Transact-SQL Datatypes

Exact Numerics

bit

integer

Storage Size 1 bit
Data Range 0 or 1

b 

0
1
tinyint

integer

Storage Size 1 byte
Data Range 0 to 255

n 

0
69
0xEf
smallint

integer

Storage Size 2 bytes
Data Range -2^15 to (2^15)-1

n 

0
69
-999
0x12Ef
int

integer

Storage Size 4 bytes
Data Range -2^31 to (2^31)-1

n 

0
69
-999
0x12Ef
bigint

integer

Storage Size 8 bytes
Data Range -2^63 to (2^63)-1

n 

0
69
-999
0x12Ef
decimal ( p , s )

numeric of precision p (1-38) and scale s (0-p)

Storage Size 5-17 bytes
Data Range (-10^38)+1 to (10^38)-1 (at max precision)

n 

3.14159
1.5E2    -- 150
5.0E-1   -- 0.5
numeric ( p , s )

numeric of precision p (1-38) and scale s (0-p)

Storage Size 5-17 bytes
Data Range (-10^38)+1 to (10^38)-1 (at max precision)

n 

3.14159
1.5E2    -- 150
5.0E-1   -- 0.5
smallmoney

currency

Storage Size 4 bytes
Data Range -214,748.3648 to 214,748.3647

n 

0
69.99
-123.1234
money

currency

Storage Size 8 bytes
Data Range -922,337,203,685,477.5808 to 922,337,203,685,477.5807

n 

0
69.99
-123.1234

Approximate Numerics

float

floating point - 15 digit precision (identical to float(53))

Storage Size 8 bytes
Data Range -1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308

x 

3.14159
1.5E2    -- 150
5.0E-1   -- 0.5
float ( n )

floating point, of mantissa n

Storage Size 4-8 bytes
Data Range -1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308

x 

3.14159
1.5E2    -- 150
5.0E-1   -- 0.5
real

floating point (identical to float(24))

Storage Size 4 bytes
Data Range -3.40E+38 to -1.18E-38, 0 and 1.18E-38 to 3.40E+38

x 

3.14159
1.5E2    -- 150
5.0E-1   -- 0.5

Date & Time

date

date

Storage Size 3 bytes
Data Range 1753-01-01 to 9999-12-31

d 

'2012-02-04'
time

time

Storage Size 5 bytes
Data Range 00:00:00 to 23:59:59.9999999

d 

'14:35:29.1234567'
smalldatetime

date and time

Storage Size 4 bytes
Data Range 1900-01-01 to 2079-06-06, 00:00:00 to 23:59:59

d 

'2012-02-04 14:35:29'
datetime

date and time

Storage Size 8 bytes
Data Range 1753-01-01 to 9999-12-31, 00:00:00 to 23:59:59.997

d 

'2012-02-04 14:35:29.123'
datetime2

date and time

Storage Size 6-8 bytes
Data Range 0001-01-01 to 9999-12-31, 00:00:00 to 23:59:59.9999999

d 

'2012-02-04 14:35:29.1234567'
datetimeoffset

date and time with offset

Storage Size 10 bytes
Data Range 0001-01-01 to 9999-12-31, 00:00:00 to 23:59:59.9999999, -14:00 to +14:00

d 

'2012-02-04 14:35:29.1234567 +12:15'

Strings

char ( n )

fixed-length string

Storage Size bytes
Data Range 0 to (<=8000) chars

s 

'single'
'isn''t'
"double"
text

variable-length string

Data Range (2^31)-1 max chars

s 

'single'
'isn''t'
"double"
varchar ( { n | max } )

variable-length string

Storage Size +2 bytes
Data Range 0 to (<=8000) chars or (2^31)-1 for max

s 

'single'
'isn''t'
"double"
nchar ( n )

fixed-length unicode string

Storage Size *2 bytes
Data Range 0 to (<=4000) chars

s 

'single'
'isn''t'
"double"
ntext

variable-length unicode string

Data Range (2^30)-1 max chars

s 

'single'
'isn''t'
"double"
nvarchar ( { n | max } )

variable-length unicode string

Storage Size *2 + 2 bytes
Data Range 0 to (<=4000) chars or (2^30)-1 for max

s 

'single'
'isn''t'
"double"

Binary

binary ( n )

fixed-length binary data

Storage Size bytes
Data Range 0 to (<=8000) bytes

z 

0x12Ef23Fa342bcE
image

variable-length binary data

Data Range (2^31)-1 max bytes

z 

0x12Ef23Fa342bcE
varbinary ( { n | max } )

variable-length binary data

Storage Size +2 bytes
Data Range 0 to (<=8000) bytes or (2^31)-1 for max

z 

0x12Ef23Fa342bcE

Miscellaneous

cursor

cursor reference

z 

@cursor = CURSOR FOR SELECT 'Foobar'
hierarchyid

hierarchy data type

z 

/
/1/
/0.3.-7/
/1/3/
/0.1/0.2/
table

table (temp)

z 

@table = TABLE (col1 int, col2 varchar(10))
uniqueidentifier

GUID

Storage Size 16 bytes

z 

newid()
'6F9619FF-8B86-D011-B42D-00C04FC964FF'
xml

xml data

Data Range upto 2Gb

z 

12