Production Database Report: Why choosing proper data type for column is important?

Microsoft SQL Server Report

Database is one of the important part of any web application. Respect the fact that database is much more important than software. However many of you don't care about what data type you are choosing for new column in database table. I've prepared analysis report from production database in Microsoft SQL Server.

Initial Report of Table
Table has 48 columns in it, out of them 16 were with "VARCHAR" data types. Out of 16 column, there were 13 with VARCHAR(MAX). Column names and its initial data types given at the end of the article.

Total Rowcount 17931710
Total Space Occupied Including Index 9319 MB


Steps taken to improve
Step 1: Find the maximum length of value stored in column. SELECT MAX(LENGTH(COLUMN_NAME)) FROM TABLE_NAME.
- Consider a scenario for a column you got Maximum length of value 10,15...20, It means you are storing some predefined status in it and you don't need VARCHAR(MAX)/NVARCHAR(MAX) for it. Discuss with seniors or team member for length of column.
- Perform above step for all column of VARCHAR/NVARCHAR data type. And redesign your table with new length for VARCHAR/NVARCHAR data type column.

Step 2: Create new table with refined data types.

Step 3: Insert data from Old Table to New Table.(Use IDENTITY_INSERT to preserve old ID value of table. Read more https://msdn.microsoft.com/en-us/library/ms188059.aspx)

Step 4: Create existing indices on new table.

Step 5: Delete old table.

Step 6: Rename new table with old/original table name.

I followed the above steps and got the unexpected result. This reports shows the importance of choosing proper data types.

Total Rowcount 17931710
Total Space Occupied without Index 4317 MB
Total Space Occupied with Index 6493 MB
Space occupied by new table 4317(Data) + 2176(Index) = 6493

Previously size of table was 9319 MB with redefined data types its 6493 MB and we've just decreased 2826 MB from table.

Column Name Length New Lenght
****_value max >> max
hash max >> 500
****_status 255 >> 30
sign max >> 500
****_type 255 >> 30
****_message max >> 150
****_value max >> max
****_key max >> 500
****_public_key max >> 500
****_status max >> 20
****_key_before max >> 500
****_key_before_**** max >> 500
****opes max >> 500
****_values max >> 500
****_info max >> 500
currency_**** 1000 >> 500


How to reverse number in Java?

How to reverse number in Java

Its one of the tricky program asked in Java interviews. Its very simple program however at that time our mind start thinking about some complex logic to crack the code. But its very simple logic that we uses in our day to day life and can be done with few lines. Lets see how you can do that so it will help you whenever you face this question.

Modulo Operator
Yeah you got it right. You can use number % 10 to get the last digit in number.

Source Code
/**
 * @author javaQuery
 * Reverse number in Java using Modulo Operator
 */
public class ReverseNumber {
    public static void main(String[] args) {
        int number = 123;
        int remainder;
        while(number > 0){
            remainder = number % 10;
            number = number / 10;
            System.out.print(remainder);
        }
    }
}

//Output
//321


Why String is immutable in Java?

String objects are cached in String Pool and shared between multiple thread so it will lead to risk of value changed by one thread will affect value of other thread.

Example
/**
 * Find "abc" in String Pool or create new Object.
 * Say its reference is "R"
 */        
String str = "abc";
/**
 * Find "abc" in String Pool or create new Object.
 * Its already available in String Pool [Reference "R"].
 * This will point the same value in String Pool
 */
String str1 = "abc";
/**
 * Find "abcxyz" in String Pool or create new Object.
 * Now str is pointing only "abcxyz" in String Pool and "str1" is pointing to "abc".
 */
str = str + "xyz";

Graphical Representation

Why String is immutable in Java
Initial State
Why String is immutable in Java
After modification of str
Now consider what happen if String is mutable. When we change value of str by str = str + "xyz". It will also change value of str1.

This is why creator[Lee Boynton, Arthur van Hoff taken from class file] of String class marked String class as final. To make sure no one can override behaviour of String class.

Why String is immutable or final in Java?
As explained in example you can understand the reason behind the immutability of String. To understand it with real world example like...

#1 String is used in simple HelloWorld program and also used in complex program of space so it has to maintain its value.
#2 String is used to pass values between method.
#3 String is used with many other programs like File(IO operation), Network Program, etc...

This is one of the popular Interview question and asked in any interview of java for fresher of experienced. If I missed something important please do share your comments.

How to check is there any common element between two List of String?

Java Collection Framework disjoint


Collections.disjoint(Collection<?> c1, Collection<?> c2)
Returns true if the two specified collections have no elements in common.

Source Code
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/**
 * @author javaQuery
 */
public class DisjointExample {

    public static void main(String[] args) {
        /* Create list of String */
        List<String> listString = new ArrayList<String>();
        /* Add elements in listString */
        listString.add("a");
        listString.add("b");
        listString.add("c");

        /* Create list of String */
        List<String> listStringSecond = new ArrayList<String>();
        /* Add elements in listString */
        listStringSecond.add("x");
        listStringSecond.add("y");
        listStringSecond.add("z");

        System.out.println("Initial List 1: " + listString);
        System.out.println("Initial List 2: " + listStringSecond);
        System.out.println("------------------------");
        System.out.println("Disjoint: " + Collections.disjoint(listString, listStringSecond));

        /* Add element available in listString */
        listStringSecond.add("a");
        System.out.println("------------------------");
        System.out.println("List 2 after add(\"a\"): " + listStringSecond);
        System.out.println("Disjoint after add(\"a\"): " + Collections.disjoint(listString, listStringSecond));
    }
}

Output
Initial List 1: [a, b, c]
Initial List 2: [x, y, z]
------------------------
Disjoint: true
------------------------
List 2 after add("a"): [x, y, z, a]
Disjoint after add("a"): false

How to reverse List in Java Collection Framework?

Collections Framework Reverse Java


List allows to add String and Custom bean elements in it. To reverse the given list we can use Collections.reverse(List<?> list).

Source Code
/**
 * @author javaQuery
 */
public class User{

    private String Firstname;
    private String Lastname;

    public String getFirstname() {
        return Firstname;
    }

    public void setFirstname(String Firstname) {
        this.Firstname = Firstname;
    }

    public String getLastname() {
        return Lastname;
    }

    public void setLastname(String Lastname) {
        this.Lastname = Lastname;
    }
}
import com.javaquery.beans.User;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/**
 * @author javaQuery
 */
public class ReverseListExample {

    public static void main(String[] args) {
        /* Create list of String */
        List<String> listString = new ArrayList<String>();
        /* Add elements in listString */
        listString.add("a");
        listString.add("b");
        listString.add("c");

        System.out.println("Initial List");
        System.out.println(listString);
        System.out.println("------------------------");

        Collections.reverse(listString);
        System.out.println("Reverse List");
        System.out.println(listString);
        System.out.println("------------------------");

        /* Create object of User */
        User user1 = new User();
        /* Set Firstname of User */
        user1.setFirstname("Vicky");
        /* Set Lastname of User */
        user1.setLastname("Thakor");

        User user2 = new User();
        user2.setFirstname("Chirag");
        user2.setLastname("Thakor");

        User user3 = new User();
        user3.setFirstname("Heer");
        user3.setLastname("Thakor");
        
        /* Add Users in List<User> */
        List<User> listUser = new ArrayList<User>();
        /* Add elements in listUser */
        listUser.add(user1);
        listUser.add(user2);
        listUser.add(user3);

        System.out.println("Initial List");
        System.out.println("------------------------");
        for (User user : listUser) {
            System.out.println(user.getFirstname() + " " + user.getLastname());
        }
        System.out.println("------------------------");
        System.out.println("Reverse List");
        System.out.println("------------------------");
        Collections.reverse(listUser);
        for (User user : listUser) {
            System.out.println(user.getFirstname() + " " + user.getLastname());
        }        
    }
}

Output
[a, b, c]
------------------------
Reverse List
[c, b, a]
------------------------
Initial List
------------------------
Vicky Thakor
Chirag Thakor
Heer Thakor
------------------------
Reverse List
------------------------
Heer Thakor
Chirag Thakor
Vicky Thakor

How to sort List of Bean in Java?

Collection Framework Sort


In my previous article How to sort a List in Java? We have seen how we can sort List<String>. This time we'll understand code of How we can sort List<Bean>.

Storing data in bean is common practice in current industry. Bean structure allows us to get specific property of object. Hope you all are aware of what is bean so lets not waste time and understand the code.

Background
We have User object that holds two property Firstname and Lastname. We will sort the List on Firstname in ascending order. There are two technique you can use to sort an object and we'll checkout one by one.

Technique One
Say you have access to your bean class and can modify the class file then by implementing Comparable interface you can perform sorting.
/**
 * @author javaQuery
 */
public class User implements Comparable<User> {

    private String Firstname;
    private String Lastname;

    public String getFirstname() {
        return Firstname;
    }

    public void setFirstname(String Firstname) {
        this.Firstname = Firstname;
    }

    public String getLastname() {
        return Lastname;
    }

    public void setLastname(String Lastname) {
        this.Lastname = Lastname;
    }

    /**
     * This `compareTo` used to compare two `User` object,
     * Which internally uses `compareTo` of String class to compare two String values.
     * @param obj
     * @return 
     */
    @Override
    public int compareTo(User obj) {
        /* Check object and Firstname are not null */
        if(obj != null && obj.Firstname != null){
            /* We will use `compareTo` of String class */
            /* For ascending order */
            return this.Firstname.compareTo(obj.Firstname);
   
            /* For descending order */
            /* return obj.Firstname.compareTo(this.Firstname) */
        }else{
            return -1;
        }
    }
 
 /* If you want to sort on `int` value then uncomment following code */
//   @Override
//    public int compareTo(User obj) {
//        /* Check object and id is not null and 0 */
//        if (obj != null && obj.getId() != 0) {
//            /* For ascending order */
//            return this.getId() - obj.getId();
//
//            /* For descending order */
//            /* return obj.getId() - this.getId(); */
//        } else {
//            return -1;
//        }
//    }
}
Source Code
import com.javaquery.beans.User;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/**
 * @author javaQuery
 */
public class SortingObjectListExample {

    public static void main(String[] args) {
        /* Create object of User */
        User user1 = new User();
        /* Set Firstname of User */
        user1.setFirstname("Vicky");
        /* Set Lastname of User */
        user1.setLastname("Thakor");

        User user2 = new User();
        user2.setFirstname("Chirag");
        user2.setLastname("Thakor");

        User user3 = new User();
        user3.setFirstname("Heer");
        user3.setLastname("Thakor");

        User user4 = new User();
        user4.setFirstname("Yogita");
        user4.setLastname("Thakor");

        User user5 = new User();
        user5.setFirstname("Riddhi");
        user5.setLastname("Thakor");

        User user6 = new User();
        user6.setFirstname("Xender");
        user6.setLastname("Thakor");

        /* Add Users in List<User> */
        List<User> listUser = new ArrayList<User>();
        listUser.add(user4);
        listUser.add(user6);
        listUser.add(user1);
        listUser.add(user5);
        listUser.add(user2);
        listUser.add(user3);

        System.out.println("Initial List");
        System.out.println("------------------------");
        for (User user : listUser) {
            System.out.println(user.getFirstname() + " " + user.getLastname());
        }
        System.out.println("------------------------");

        Collections.sort(listUser);
        System.out.println("List after Collections.sort(List<T> list)");
        System.out.println("------------------------");
        for (User user : listUser) {
            System.out.println(user.getFirstname() + " " + user.getLastname());
        }
    }
}
Output of the above code is given at the end of the article as both technique gives same output.

Technique Two
Say if you don't have access to your bean(bind in jar) and still you want to sort on specific property then by creating custom Comparator you can achieve same.
/**
 * @author javaQuery
 */
public class User{

    private String Firstname;
    private String Lastname;

    public String getFirstname() {
        return Firstname;
    }

    public void setFirstname(String Firstname) {
        this.Firstname = Firstname;
    }

    public String getLastname() {
        return Lastname;
    }

    public void setLastname(String Lastname) {
        this.Lastname = Lastname;
    }
}
Source code
import com.javaquery.beans.User;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;

/**
 * @author javaQuery
 */
public class SortingObjectListExample {

    public static void main(String[] args) {
        /* Create object of User */
        User user1 = new User();
        /* Set Firstname of User */
        user1.setFirstname("Vicky");
        /* Set Lastname of User */
        user1.setLastname("Thakor");

        User user2 = new User();
        user2.setFirstname("Chirag");
        user2.setLastname("Thakor");

        User user3 = new User();
        user3.setFirstname("Heer");
        user3.setLastname("Thakor");

        User user4 = new User();
        user4.setFirstname("Yogita");
        user4.setLastname("Thakor");

        User user5 = new User();
        user5.setFirstname("Riddhi");
        user5.setLastname("Thakor");

        User user6 = new User();
        user6.setFirstname("Xender");
        user6.setLastname("Thakor");

        /* Add Users in List<User> */
        List<User> listUser = new ArrayList<User>();
        listUser.add(user4);
        listUser.add(user6);
        listUser.add(user1);
        listUser.add(user5);
        listUser.add(user2);
        listUser.add(user3);

        System.out.println("Initial List");
        System.out.println("------------------------");
        for (User user : listUser) {
            System.out.println(user.getFirstname() + " " + user.getLastname());
        }
        System.out.println("------------------------");

        /* Create object of `SortingObjectListExample` */
        SortingObjectListExample objSortingObjectListExample = new SortingObjectListExample();
        /* Get custom Comparator */
        Comparator<User> objComparator = objSortingObjectListExample.getComparator();

        /* Sort List<User> using custom Comparator */
        Collections.sort(listUser, objComparator);
        System.out.println("List after Collections.sort(List<T> list)");
        System.out.println("------------------------");
        for (User user : listUser) {
            System.out.println(user.getFirstname() + " " + user.getLastname());
        }
    }

    /* Create custom Comparator */
    public Comparator<User> getComparator() {
        return new Comparator<User>() {

            @Override
            public int compare(User obj1, User obj2) {
                /* Check obj1 and obj2 are not null and also check Firstname of both objects are not null */
                if (obj1 != null && obj2 != null
                        && obj1.getFirstname() != null && obj2.getFirstname() != null) {
                    /* Use `compareTo` of String class to compare two String values */
                    /* For ascending order */
                    return obj1.getFirstname().compareTo(obj2.getFirstname());
   
                    /* For descending order */
                    /* return obj2.getFirstname().compareTo(obj1.getFirstname()) */
                } else {
                    return -1;
                }
            }
        };
    }
}
Output
Initial List
------------------------
Yogita Thakor
Xender Thakor
Vicky Thakor
Riddhi Thakor
Chirag Thakor
Heer Thakor
------------------------
List after Collections.sort(List<T> list)
------------------------
Chirag Thakor
Heer Thakor
Riddhi Thakor
Vicky Thakor
Xender Thakor
Yogita Thakor

How to compare two List of String in Java?

List: Collections Framework Java


The best approach to compare two List<String>

Source Code
import java.util.ArrayList;
import java.util.List;

/**
 * @author javaQuery
 */
public class CompareTwoListExample {

    public static void main(String[] args) {
        /* Create list of String */
        List<String> listString1 = new ArrayList<String>();
        /* Add element from 0th position */
        listString1.add("a");
        listString1.add("b");
        listString1.add("c");
        listString1.add("d");
        listString1.add("e");

        /* Print list */
        System.out.println("Initial List1:\n" + listString1);
        System.out.println("---------------------------------");

        /* Create list of String */
        List<String> listString2 = new ArrayList<String>();
        /* Add element from 0th position */
        listString2.add("e");
        listString2.add("d");
        listString2.add("c");
        listString2.add("a");
        listString2.add("b");
        
        /* Print list */
        System.out.println("Initial List2:\n" + listString2);
        System.out.println("---------------------------------");

        if(listString1 != null && listString2 != null && (listString1.size() == listString2.size())){
            listString1.removeAll(listString2);
            if(listString1.isEmpty()){
                System.out.println("Both list are same.");
            }else{
                System.out.println("Both list are not same");
            }
        }
    }
}

Output
Initial List1:
[a, b, c, d, e]
---------------------------------
Initial List2:
[e, d, c, a, b]
---------------------------------
Both list are same.