List of Datatypes
Published: Sep 09, 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.
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 |