General rules on selecting Data Types of SQL Server

1.Use the smallest possible columnsizes. The smaller the columnsize, the less the amount of data that SQL Server has to store and process, and the faster SQL Server will be able to read and write the data. In addition, the narrower the column, the faster a sort will be performed onthat column.

2.Use the smallest possible data type that will hold your data for a column. For example, if you are going to be storing numbers from 1 to 99 in a column, you would be better off selecting the TINYINT data type instead of the INT data type.
For numeric data, it is better to use a numeric data type such as INTEGER rather than using VARCHAR or CHAR, since numeric data types generally require less space to hold numeric values then character data types. Also, if numeric data is stored as characters, it will have to be converted to numbers to be used in calculations. Smaller columns can improve performance when the columns are searched, joined with other columns, or sorted.

3.FLOATs or REALs should not be used to define primary keys. Integer data types are most commonly used for primary keys. Avoid selecting the fixed-length columns—CHAR or NCHAR—if your columnwill have a lot of NULLs. The NULL ina CHAR or NCHAR field will take up the entire fixed length of 255 characters. This is a large waste of space and reduces SQL Server’s overall performance.

4.If you are going to be using a column for frequent sorts, consider an integer-based column rather than a character-based column. SQL Server sorts integer data faster than character data.