Tuesday, 3 May 2011

SQL Data Types

Character strings:


Data type
Description
char(n)
Fixed-length character string. Maximum 8,000 characters
varchar(n)
Variable-length character string. Maximum 8,000 characters
varchar(max)
Variable-length character string. Maximum 1,073,741,824 characters
text
Variable-length character string. Maximum 2GB of text data

Unicode strings:


Data type
Description
nchar(n)
Fixed-length Unicode data. Maximum 4,000 characters
nvarchar(n)
Variable-length Unicode data. Maximum 4,000 characters
nvarchar(max)
Variable-length Unicode data. Maximum 536,870,912 characters
ntext
Variable-length Unicode data. Maximum 2GB of text data
Binary types:


Data type
Description
bit
Allows 0, 1, or NULL
binary(n)
Fixed-length binary data. Maximum 8,000 bytes
varbinary(n)
Variable-length binary data. Maximum 8,000 bytes
varbinary(max)
Variable-length binary data. Maximum 2GB
image
Variable-length binary data. Maximum 2GB
Number types:
Data typeDescriptionStorage
realMonetary data from -214,748.3648 to 214,748.3647
4 bytes
tinyintAllows whole numbers from 0 to 255
1 byte
smallintAllows whole numbers between -32,768 and 32,767
2 bytes
intAllows whole numbers between -2,147,483,648 and 2,147,483,647
4 bytes
bigintAllows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807
8 bytes
decimal(p,s)
Fixed precision and scale numbers.
Allows numbers from -10^38 +1 to 10^38 –1.
The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18.
The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0
5-17 bytes
numeric(p,s)
Fixed precision and scale numbers.
Allows numbers from -10^38 +1 to 10^38 –1.
The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18.
The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0
5-17 bytes
smallmoney
Monetary data from -214,748.3648 to 214,748.3647
4 bytes
Money
Monetary data from -922,337,203,685,477.5808 to 922,337,203,685,477.5807
8 bytes
float(n)
Floating precision number data from -1.79E + 308 to 1.79E + 308.
The n parameter indicates whether the field should hold 4 or 8 bytes. float(24) holds a 4-byte field and float(53) holds an 8-byte field. Default value of n is 53.
4 or 8 bytes


Date types:
Data typeDescriptionStorage
datetimeFrom January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds8 bytes
datetime2From January 1, 0001 to December 31, 9999with an accuracy of 100 nanoseconds
6-8 bytes
smalldatetimeFrom January 1, 1900 to June 6, 2079 with an accuracy of 1 minute
4 bytes
dateStore a date only. From January 1, 0001 to December 31, 9999
3 bytes
timeStore a time only to an accuracy of 100 nanoseconds
3-5 bytes
datetimeoffset
The same as datetime2 with the addition of a time zone offset
8-10 bytes
timestamp
Stores a unique number that gets updated every time a row gets created or modified. The timestamp value is based upon an internal clock and does not correspond to real time. Each table may have only one timestamp variable


Other Data Types:
Data type
Description
sql_variant
Stores up to 8,000 bytes of data of various data types, except text, ntext, and timestamp
uniqueidentifier
Stores a globally unique identifier (GUID)
xml
Stores XML formatted data. Maximum 2GB
cursor
Stores a reference to a cursor used for database operations
table
Stores a result-set for later processing

 Share This Post


Friendly Learn Translate to Tamil

Smilely

Services

© 2014