FAQ How to

---

show Changed

Changed

Visitor's book Site map pmwiki-2.3.37

• login

Sql Server Data Types

Data types in SQL server

Data types

Deprecated^{[1](approve sites)} 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](approve sites) [3](approve sites)}

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](approve sites)
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](approve sites)
decimal	-10^38 +1	10^38 –1	[6](approve sites)
numeric	-10^38 +1	10^38 –1
money	-922,337,203,685,477.5808	+922,337,203,685,477.5807	[7](approve sites)
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](approve sites)
	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](approve sites)}

Type	Format	From	To	Accuracy	Ref
time2008	hh:mm:ss[.nnnnnnn]]	00:00:00.0000000	23:59:59.9999999	100 nanoseconds	[10](approve sites)
date2008	yy-mm-dd	0001-01-01	9999-12-31	1 day	[11](approve sites)
datetime	yyyy-mm-dd hh:mm:ss[.nnn]]	1753-01-01	9999-12-31	3.33 milliseconds	[12](approve sites)
smalldatetime	yyyy-mm-dd hh:mm:ss	1900-01-01	2079-06-06	1 minute	[13](approve sites)
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](approve sites)
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](approve sites)

Character Strings

Type	Length	Maximum	Description	Ref
char	Fixed	8,000	non-Unicode data	[16](approve sites)
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](approve sites)

Unicode Character Strings

Type	Length	Maximum	Description	Ref
nchar	Fixed	4,000	data	[18](approve sites)
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](approve sites)

Binary Strings

Type	Length	Maximum	Description	Ref
binary	Fixed	8,000	binary data	[20](approve sites)
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](approve sites)

Spacial ^{[22](approve sites)}

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](approve sites)
rowversion		automatically generated, unique binary numbers within a database (or timestamp)	[24](approve sites)
uniqueidentifier	16	a globally unique identifier (GUID)	[25](approve sites)
xml	2GB	XML documents and fragments	[26](approve sites)
cursor		reference to a cursor	[27](approve sites)
table		Stores a result set for later processing	[28](approve sites)
hierarchyid2008	variable	represent position in a hierarchy	[29](approve sites)
filestream2008	variable	allows unstructured data to be stored in the file system instead of inside the SQL Server database	[30](approve sites)

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](approve sites)}

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.