Basic points to be considered when starting with SQL Server – – Part 2

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:-

Type

DataTypes

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.
For example:-
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.
For example:-
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.
For example:-
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…..

<<Previous Post                                                                                                Next Post>>

Written By: Ankit Arora, Sr. Software Engineer, Mindfire Solutions

Advertisements

Posted on January 20, 2014, in SQL and tagged , , , , , , , , , , . Bookmark the permalink. 4 Comments.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: