Basic points to be considered when starting with SQL Server – – Part 2
Posted by unwrapdotnet
Hello friends, In the previous post, we discussed when to use which datatype for string values.
Today, I want to draw your attention to some more data type selection. I mean we have several data types in SQL like:-
|Fixed Number Type||INT, BIGINT, TINYINT, NUMERIC|
|Regular Character Types||CHAR, VARCHAR|
|Unicode Character types||NCHAR,NVARCHAR|
|Approximate Number Type||Float|
|Date Type||DATE, DATETIME, DATETIME2, SHORTDATETIME|
Now we have several data types that looks similar and we get confused when and why to use which one. We already discussed character types Vs. Unicode character types. So let’s focus on rest today.
Before I move forward let me tell you why data type selection is critical. Basically the data type is the only thing that represents your data. That allows you to manipulate your data without extra efforts.
If you save date in a type INT or VARCHAR then it will not allow you to take benefit of SQL validations. I mean INT column will allow you to insert 999999 and VARCHAR will allow you to insert “ABC”. But these are not valid dates. So if you have chosen the data type “DATE” instead of INT/VARCHAR, it will give you error when you attempt to save invalid date.
Also, different SQL operators behave differently with different data types.
Operator plus(+) works as addition for numeric but works as concatenation for strings.
So selection of data type is very important in order to have a successful application.
Now, as we understand why data type is important let’s move forward and try to answer the question that arises to our mind.
If we need to use numeric types for data, then which one “INT, BIGINT, SMALLINT, TINYINT”?
Answer to this is very simple. It totally depends on your need.
If you want to store a range of integers from 0 to 255 only use TINYINT.
If you want to store a more than TINYINT but less than normal range of integers use SMALLINT (-215 to -215 -1)
If you want to store a normal range (-231 to -231 -1) of integers user INT
If you want to store more than above mentioned ranges then use BIGINT (-263 to -263 -1)
SQL will not stop us, if we use INT to save a range of 1 to 100. But it will degrade the performance since INT uses 4 bytes and TINYINT will use only 1 byte. So more the I/O is used less the performance we will get. Although, it will not have impact if the number of rows returned is very less.
Similarly, if we only want Date then data type DATE is the best one instead of DATETIME.
It is good to have optimal data type for optimal performance.
Thanks for giving your time to read this. Keep in touch I will post more soon…..