add Resources page
|
Sql Server Data Types
Data types in SQL server
Data types
Deprecated[1] data types are marked by strikethrough.
Best practices
- use variable length data types
- avoid tiny and small data types
- use exact numerics unless otherwise required
Exact numerics [2] [3]
Type | From | To | Ref |
bigint | -2^63 -9,223,372,036,854,775,808 | 2^63-1 9,223,372,036,854,775,807 | [4] |
int | -2^31 -2,147,483,648 | 2^31-1 2,147,483,647 |
smallint | -2^15 -32,768 | 2^15-1 32,767 |
tinyint | 0 | 255 |
bit | 0 | 1 | [5] |
decimal | -10^38 +1 | 10^38 –1 | [6] |
numeric | -10^38 +1 | 10^38 –1 |
money | -922,337,203,685,477.5808 | +922,337,203,685,477.5807 | [7] |
smallmoney | -214,748.3648 | +214,748.3647 |
- numeric and decimal are Fixed precision and scale data types and are functionally equivalent.
- money and smallmoney data types are accurate to a ten-thousandth of the monetary units that they represent.
Approximate numerics
Type | From | To | Ref |
float | -1.79E+308 | -2.23E-308 | [8] |
| 0 | 0 |
| 2.23E-308 | 1.79E+308 |
real | -3.40E+38 | -1.18E-38 |
| 0 | 0 |
| 1.18E-38 | 3.40E + 38 |
Date and time [9]
Type | Format | From | To | Accuracy | Ref |
time2008 | hh:mm:ss[.nnnnnnn]] | 00:00:00.0000000 | 23:59:59.9999999 | 100 nanoseconds | [10] |
date2008 | yy-mm-dd | 0001-01-01 | 9999-12-31 | 1 day | [11] |
datetime | yyyy-mm-dd hh:mm:ss[.nnn]] | 1753-01-01 | 9999-12-31 | 3.33 milliseconds | [12] |
smalldatetime | yyyy-mm-dd hh:mm:ss | 1900-01-01 | 2079-06-06 | 1 minute | [13] |
datetime22008 | yyyy-mm-dd hh:mm:ss[.nnnnnnn]] | 0001-01-01 00:00:00.000000000 | 9999-12-31 23:59:59.9999999 | 100 nanoseconds | [14] |
datetimeoffset2008 | yyyy-mm-dd hh:mm:ss[.nnnnnnn]] [+|-]hh:mm | 0001-01-01 00:00:00.000000000 | 9999-12-31 23:59:59.9999999 | 100 nanoseconds | [15] |
Character Strings
Type | Length | Maximum | Description | Ref |
char | Fixed | 8,000 | non-Unicode data | [16] |
varchar | Variable | 8,000 | non-Unicode data |
varchar(max) | Variable | 2^31-1 | non-Unicode data |
text | Variable | 2^31-1 | non-Unicode data, use varchar(max) | [17] |
Unicode Character Strings
Type | Length | Maximum | Description | Ref |
nchar | Fixed | 4,000 | data | [18] |
nvarchar | Variable | 4,000 | Unicode data |
nvarchar(max) | Variable | 2^31-1 | Unicode data |
ntext | Variable | 2^30 - 1 | Unicode data, use nvarchar(max) | [19] |
Binary Strings
Type | Length | Maximum | Description | Ref |
binary | Fixed | 8,000 | binary data | [20] |
varbinary | Variable | 8,000 | binary data |
varbinary(max) | Variable | 2^31-1 | binary data |
image | Variable | 2^31-1 | binary data, use varbinary(max) | [21] |
Type | Description |
Geometry2008 | used to store planar (flat-earth) data, generally as XY coordinates that represent points, lines, and polygons in a two-dimensional space |
Geography2008 | used to store ellipsoidal (round-earth) data, that is latitude and longitude coordinates that represent points, lines, and polygons on the earth’s surface |
Other Data Types
Type | Length | Description | Ref |
sql_variant | 8016 | values of various SQL Server-supported data types, except varchar(max), varbinary(max), nvarchar(max), xml, text, ntext, image, timestamp, sql_variant, geography, hierarchyid, geometry, User-defined types | [23] |
rowversion | | automatically generated, unique binary numbers within a database (or timestamp) | [24] |
uniqueidentifier | 16 | a globally unique identifier (GUID) | [25] |
xml | 2GB | XML documents and fragments | [26] |
cursor | | reference to a cursor | [27] |
table | | Stores a result set for later processing | [28] |
hierarchyid2008 | variable | represent position in a hierarchy | [29] |
filestream2008 | variable | allows unstructured data to be stored in the file system instead of inside the SQL Server database | [30] |
Alias Data Types
Alias types are based on the system data types in SQL Server.
Alias types can be used when several tables must store the same type of data in a column and you have to make sure that these columns have identical data type, length, and nullability.
For example, an alias type called postal_code could be created based on the char data type.
When an alias data type is created, supply the following parameters:
- Name
- System data type upon which the new data type is based
- Nullability (whether the data type allows null values). When nullability is not explicitly defined, it will be assigned based on the ANSI null default setting for the database or connection.
Data type synonyms [31]
Included in SQL Server for ISO compatibility.
Synonym | SQL Server system data type |
Binary varying | varbinary |
char varying | varchar |
character | char |
character | char(1) |
character( n ) | char(n) |
character varying( n ) | varchar(n) |
Dec | decimal |
Double precision | float |
float[(n)] for n = 1-7 | real |
float[(n)] for n = 8-15 | float |
integer | int |
national character( n ) | nchar(n) |
national char( n ) | nchar(n) |
national character varying( n ) | nvarchar(n) |
national char varying( n ) | nvarchar(n) |
national text | ntext |
timestamp | rowversion |
Data type synonyms can be used instead of the corresponding base data type name in data definition language (DDL) statements, such as CREATE TABLE, CREATE PROCEDURE, or DECLARE @variable.
|