List of Datatypes
Published: 3rd May 2013
A datatype is exactly how it sounds... it defines the type of data. We use these all the time in our normal lives but may not even realise it.

14, for example, is a number. We know that "bored" is a word made up of 5 characters. Those are effectively datatypes in action, just that we call them slightly different names in SQL Server.

Using the above, 14 is a number, but in SQL it would be an integer, therefore of the type "int". This can be broken down further as it is also a smallint and a tinyint, but they will be clarified below.

In the same way, "bored" is a word of 5 characters, therefore we would refer to it as char(5). But a word can be any length, therefore we could use a variable length character, a varchar.

These characterisations allow us to work with data and manipulate it accordingly, for example you can put two varchars together "cow" & "girl" to get "cowgirl", yet you cannot add or multiply them. Likewise you can add or subtract two ints, but you would not normally say "1 & 1 = 11".

I have provided a list of the most common types below, but if you wish to get a full list, then please refer to Microsoft Books Online.

List of Common Datatypes


bit This can be either 0 or 1 1 Byte
tinyint Whole number between 0 and 255 1 Byte
smallint Whole number between -32,768 and 32,767 2 Bytes
int Whole number between -2,147,483,648 and 2,147,483,647 4 Bytes
bigint Whole number between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807 8 Bytes
smallmoney -214,748.3648 to 214,748.3647 4 Bytes
money -922,337,203,685,477.5808 to 922,337,203,685,477.5807 8 Bytes
numeric numeric(p, s), p is the number of decimal digits, s is the number of decimal places Variable
float -1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308 Variable
date 0001-01-01 to 9999-12-31 3 Bytes
time 00:00:00.0000000 to 23:59:59.9999999 5 Bytes
smalldatetime 1900-01-01 00:00 to 2079-06-06 23:59 4 Bytes
datetime 1753-01-01 00:00:00.000 to 9999-12-31 23:59:59.997 8 Bytes
char char(n), (non-unicode) where n is fixed length from 1 to 8000 n Bytes
varchar varchar(n), (non-unicode) where n is a fixed length from 1 to 8000. (max) = 2GB size n Bytes + 2
nchar nchar(n), (unicode) where n is a fixed length from 1 to 4000. 2*n Bytes
nvarchar nvarchar(n), (unicode) where n is a fixed length from 1 to 4000. (max) = 2GB size 2*n Bytes + 2
Comments:
NB: Comments will only appear once they have been moderated.