sql data type is an attribute that specifies the type of data of any object. each column, variable and expression has a related data type in sql. you can use these data types while creating your tables. you can choose a data type for a table column based on your requirement.
sql server offers six categories of data types for your use which are listed below −
exact numeric data types
| data type | from | to |
|---|---|---|
| bigint | -9,223,372,036,854,775,808 | 9,223,372,036,854,775,807 |
| int | -2,147,483,648 | 2,147,483,647 |
| smallint | -32,768 | 32,767 |
| tinyint | 0 | 255 |
| bit | 0 | 1 |
| decimal | -10^38 +1 | 10^38 -1 |
| numeric | -10^38 +1 | 10^38 -1 |
| money | -922,337,203,685,477.5808 | +922,337,203,685,477.5807 |
| smallmoney | -214,748.3648 | +214,748.3647 |
approximate numeric data types
| data type | from | to |
|---|---|---|
| float | -1.79e + 308 | 1.79e + 308 |
| real | -3.40e + 38 | 3.40e + 38 |
date and time data types
| data type | from | to |
|---|---|---|
| datetime | jan 1, 1753 | dec 31, 9999 |
| smalldatetime | jan 1, 1900 | jun 6, 2079 |
| date | stores a date like june 30, 1991 | |
| time | stores a time of day like 12:30 p.m. | |
note − here, datetime has 3.33 milliseconds accuracy where as smalldatetime has 1 minute accuracy.
character strings data types
| sr.no. | data type & description |
|---|---|
| 1 |
char maximum length of 8,000 characters.( fixed length non-unicode characters) |
| 2 |
varchar maximum of 8,000 characters.(variable-length non-unicode data). |
| 3 |
varchar(max) maximum length of 2e + 31 characters, variable-length non-unicode data (sql server 2005 only). |
| 4 |
text variable-length non-unicode data with a maximum length of 2,147,483,647 characters. |
unicode character strings data types
| sr.no. | data type & description |
|---|---|
| 1 |
nchar maximum length of 4,000 characters.( fixed length unicode) |
| 2 |
nvarchar maximum length of 4,000 characters.(variable length unicode) |
| 3 |
nvarchar(max) maximum length of 2e + 31 characters (sql server 2005 only).( variable length unicode) |
| 4 |
ntext maximum length of 1,073,741,823 characters. ( variable length unicode ) |
binary data types
| sr.no. | data type & description |
|---|---|
| 1 |
binary maximum length of 8,000 bytes(fixed-length binary data ) |
| 2 |
varbinary maximum length of 8,000 bytes.(variable length binary data) |
| 3 |
varbinary(max) maximum length of 2e + 31 bytes (sql server 2005 only). ( variable length binary data) |
| 4 |
image maximum length of 2,147,483,647 bytes. ( variable length binary data) |
misc data types
| sr.no. | data type & description |
|---|---|
| 1 |
sql_variant stores values of various sql server-supported data types, except text, ntext, and timestamp. |
| 2 |
timestamp stores a database-wide unique number that gets updated every time a row gets updated |
| 3 |
uniqueidentifier stores a globally unique identifier (guid) |
| 4 |
xml stores xml data. you can store xml instances in a column or a variable (sql server 2005 only). |
| 5 |
cursor reference to a cursor object |
| 6 |
table stores a result set for later processing |