This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Prepared by Bihag Thaker
Choosing correct data type while designing tables is an important part of application designing.
Incorrect choice of data types in tables can cause database and application to perform inefficiently.
Following are some of the guidelines to choose a proper data type for a column:
Always try to choose the correct data type depending on the type and characteristics of the values that are to be stored in a particular column.
Use Unicode data types like NCHAR, NVARCHAR and NTEXT only if other complex language characters other than simple English like characters need to be stored which can’t be stored in CHAR, VARCHAR or TEXT data types.
For Unicode, data type it requires 2 bytes to store a single character which can lead the inefficient storage of data and query execution.
Always use variable length data types like VARCHAR and NVARCHAR rather than using fixed length data types like CHAR and NCHAR as variable length data types make use of storage space efficiently.
Use fixed length data types CHAR and NCHAR only when the number of characters in data is known and determined.
Wherever possible, use VARCHAR (MAX) and NVARCHAR (MAX) data types instead of using TEXT and NTEXT data types.
Always use correct choice in numeric and integer data types. Always choose the small data type which requires less storage size over the other big data types as far as it can accommodate all the possible values in a domain and serves the purpose of the application.
For example, if a CountryID needs to be stored associated with each country name and as far as we know that the total number countries are not greater than 255, then the correct choice of data type will be TINYINT and not SMALLINT or INT.
Also, columns which are expected to store the values that fall in the range of SMALLINT; define them only as SMALLINT and not INT.
Similarly, use only BIGINT data type only when it is expected from an application to have a value which falls outside of the range if INT data type.
Use DATETIME data types wisely. If you don’t expect a date value to be much small or much large which falls in the range of SMALLDATETIME data type and accuracy of more than a minute is not required then use SMALLDATETIME and not DATETIME as it requires less storage space than DATETIME data type.
Use DATETIME data type to store date values along with the time component and when time accuracy is required in milliseconds. If time component is not relevant, you should use DATE data type which stores only the date part.
Avoid choosing data types which require explicit typecasting at the time of performing DML operations.
For example, storing date value or integer value in a string data type (VARCHAR or NVARCHAR) and then converting them back to DATETIME or INT while querying data is considered a bad practice and should be avoided.
