SQL Server Data types

SQL Server Data types

SQL Server Numeric Types

Data Type Description Storage
bigint bigint is used when integer value exceeds range of int data type and range is -2^63 tp 2^63-1 8 Bytes
Used when numbers(whole) range is between -2^31(-2,147,483,648) to 2^31-1(2,147,483,647) 4 Bytes
smallint Used when numbers(whole) range is between -2^15 (-32,768) to 2^15-1 (32,767) 2 Bytes
tinyint Used when whole numbers range between 0 to 255 1 Byte
decimal(p,s) Allowed numbers range is – 10^38 +1 through 10^38 – 1.
P is maximum number of digits allowed to store. Value range is from 1 to 18(Default value).
S is the number of decimal digits allowed to store right of the decimal point.
5 to 17Bytes
numeric(p,s) Allowed numbers range is – 10^38 +1 through 10^38 – 1.
P is maximum number of digits allowed to store. Value range is from 1 to 18(Default value).
S is the number of decimal digits allowed to store right of the decimal point.
5 to 17 Bytes
money This used when range is between -922,337,203,685,477.5808 to 922,337,203,685,477.5807 8 Bytes
smallmoney This used when range is between – 214,748.3648 to 214,748.3647
float Used when range is between – 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308 4 or 8 Bytes
real Used when range is between – 3.40E + 38 to -1.18E – 38, 0 and 1.18E – 38 to 3.40E + 38 4 Bytes

SQL Server String Data Types

Data Type Description Storage
char(n) Fixed legth character string.Range is 1 to 8000 n characters
varchar(n) Variable legth string data. Range is 1 to 8000 2 bytes+number of characters
text Variable length data with maximum legth of 2^30 – 1 (2,147,483,647)
ntext Variable length data with maximum legth of 2^30 – 1(1,073,741,823)
image Variable length binary data from 0 through 2^31-1 (2,147,483,647) bytes.
nchar(n) Fixed lenght string data. Range from 1 to 4000
nvarchar(n) Variable lenght Unicode string data. Range is from 1 to 4000.
binary(n) Fixed length binary data. Range is from 1 to 8000
varbinary(n) variable length binary data. Range is from 1 to 8000.
bit Takes 0, 1, or null

SQL Server Date Types

Data Type Description
date Stores date. Range 0001-01-01 through 9999-12-31
datetime Used to define a date that is combined with a time of day with fractional seconds that is based on 24-hour.
Time Range: 00:00:00 through 23:59:59.997
Date range: January 1, 1753, through December 31, 9999
datetime2 This is like datetime. date2 considered as an exxtension of the existing datetime type that a larger range.
datetimeoffset This is same as datetime2 type with time zone offset
smalldatetime This defines a date is combined with time of day.
time This defines a time of a day. Format is hh:mm:ss[.nnnnnn]

SQL Server Other Data Types

Data Type Description
xml Used to store xml data.
cursor Stores refernces to a cursor. This cannot be used in a CREATE TABLE statement
table Used to store a result set for processing later
uniqueidentifier Used to store globally unique identifier(GUID)
sql_variant Used to store data types except varchar(max),nvarchar(max), text,xml, varbinary(max),ntext,image,timestamp,sql_variant,geometry,datetimeoffset,geography and user defined types.
Powered by k2schools