SQL Server Data Types
In SQL Server, data type specifies the type of data that can be stored in a column of a table such as integer data, string data, date & time, binary strings, etc.
Assigning an appropriate data type to columns in a table is crucial while designing a database. It affects the performance and efficiency of the database and the application using the database.
SQL Server provides built-in data types for all kinds of data that can be used within SQL Server. Additionally, you can also define your own data type in T-SQL.
Data Type Categories:
| Category | Data Types |
|---|---|
| Exact numerics | bit, tinyint, smallint, int, bigint, decimal, numeric, money, smallmoney |
| Approximate numerics | Real, Float |
| Date & Time | date, smalldatetime, datetime, datetime2, datetimeoffset, time |
| Character strings | char, varchar, text |
| Unicode Character strings | nchar, nvarchar, ntext |
| Other data types | cursor, hierarchyid, sql_variant, spatial Geometry types, spatial Geography types, rowversion, uniqueidentifier, xml, table |
Exact Numerics
| Data Type | Value | Description |
|---|---|---|
| bit | 0,1 or NULL | Smallest data type of 1 byte storage size |
| tinyint | 0 to 255 | 1 bytes storage size |
| smallint | -32,768 to 32,767 | 2 bytes storage size |
| int | −2,147, 483,648 | 4 bytes storage |
| bigint | −9,223,372, 036,854,775,808 | 8 bytes storage |
| decimal | −10^38+1 to 10^38−1 | Numeric data type that have a fixed precision and scale. |
| 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 |
Approximate Numerics
| Data Type | Value | Description |
|---|---|---|
| float(n) | - 1.79E+308 to -2.23E-308, 0 | Storage depends on the value of n. |
| real | - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38 | 4 bytes |
Date and Time
| Data Type | Value | Description |
|---|---|---|
| date | 0001-01-01 through 9999-12-31 | 3 bytes |
| datetime | Date: 1753-01-01 through 9999-12-31 | 8 bytes |
| datetime2 | Date: 0001-01-01 through 9999-12-31 | Precision < 3 : 6 bytes |
| smalldatetime | 1900-01-01 through 2079-06-06 | 4 bytes fixed |
| datetimeoffset | Date: 0001-01-01 through | 10 bytes fixed |
| time | 00:00:00.0000000 through 23:59:59.9999999 | 5 bytes fixed |
Character Strings
| Data Type | Value | Description |
|---|---|---|
| char[(n)] | 1 to 8000 characters | n bytes |
| varchar[(n)] | 1 to 8000 | n bytes + 2 bytes |
| varchar(max) | 1 to 2^31-1 | n bytes + 4 bytes |
| text | 0 to 2,147,483,647 | n bytes + 4 bytes |
Unicode Character Strings
| Data Type | Value | Description |
|---|---|---|
| nchar[(n)] | 1 to 4000 characters | 2 times n bytes |
| nvarchar[(n|max)] | 1 to 4000 | 2 times n bytes |
| ntext | 0 to 1,073,741,823 | 2 times the string length |
Binary Strings
| Data Type | Value | Description |
|---|---|---|
| binary[(n)] | 1 to 8000 bytes | n bytes |
| varbinary[(n|max)] | 1 to 8000 | Actual length of string + 2 bytes |
| Image | 0 to 2,147,483,647 | Variable length binary data |
Other Data Types
| Data type | Description |
|---|---|
| cursor | A data type for variables or stored procedures OUTPUT parameters that contain a reference to a cursor. |
| rowversion | It returns automatically generated unique binary numbers within a database. |
| hierarchyid | It is a variable length system data type. |
| uniqueidentifier | Is a 16 byte GUID |
| sql_variant | A data type that stores values of various SQL Server supported data types. |
| xml | It stores xml data. |
| Spatial Geometry type | Used to represent data in a flat coordinate system (Euclidean). |
| table | It is a special data type used to store a result-set temporarily for processing at a later time. |