Data Types in SQL Server 2008

I Read a great article on database journal lately about data types in 2008
, i am sharing some basic info.

Numeric data types 

An Integer is a counting number with no decimal point or fractional piece. All negative numbers, positive numbers, and zero are integers. SQL Server breaks integers into four sizes:

BigInt: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

Int: -2,147,483,648 to 2,147,483,648

SmallInt: -32,768 to 32,767

TinyInt: 0 to 255

Here as a part of software design you need to clear what to use in which cases, it may be a simple choice to pick the largest range, but looking deep you need to understand first Bigint uses double the space as int, and the other being the application consuming the database gets what it expects and there are not overflow errors.

for decimal value numbers, there are  Decimal, Numeric, Money, and SmallMoney. The types Decimal and Numeric are functionally the same thing. Meaning, they will work, calculate and behave identically, the only difference is in the mathematical definition and not in the way SQL Server utilizes them.

Money and SmallMoney are really Decimals with a fixed amount of four decimal places to the right. SmallMoney can be valued from - 214,748.3648 to 214,748.3647 while the range of Money is from -922,337,203,685,477.5808 to 922,337,203,685,477.5807. One of the reasons for using Money rather than a Decimal includes the display option of dollar signs and commas after three digits.

Strings  


Here we will discuss Char, VarChar, and Text. As most of you might me aware of difference between char and varchar, where when a char(50) and varchar(50) columns are defined, char takes up 50 Bytes in respective of size of content saved in the column where varchar is flexible and takes up bytes required to save the length of the content. 


Char must be used in case of where it is know or understood that length of the text would be fixed like a char(1) column would make much more sense in case of a Sex column where only M/F is expected to saved instead of varchar, where as something like name should be varchar. 


Varchar has a max limit of 8000, though a new introduction of Varchar(max) has made it more flexible as the size can be unlimited in this case, and the main benefit of this is that as in case of Text column string operations cannot be done, while here all the string operations can be handled easily. Also the thing to note here is that Varchar(max) is all set to replace text in the future versions of SQL server as Text is supported in 2008 only as a part of backward compatibility. 


I will adding more to this, hope you like it.... please Digg, Kick or share this if you like this.