SQL SERVER - Different Types of Data types

Today we will see different types of Data types that are available in SQL Server 2005. In general, A data type represents the type of data that we usually store with in a column of a table.

Different types of Data types :

Exact & Approximate Numerics :

Data type	Description	No. Of bytes
TinyInt	Integer data from 0 through 255	1
SmallInt	Integer data from  -2^15 to 2^15 - 1	2
Int	Integer data from   -2^31 to 2^31 - 1	4
BigInt	Integer data from   -2^63 through 2^63-1	8
Numeric	Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1	5-17
Decimal	Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1	5-17
Float	Floating precision number data from   - 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308	8
Real	Floating precision number data from -3.40E + 38 through 3.40E + 38	4
Bit	Integer data type that can take a value of 1, 0, or NULL. Occupies 1 byte for every 8 bit columns	1

Character & Unicode Character Strings :

Data type	Description	No. Of bytes
Char[(n)]	Fixed-length, Non-Unicode character data with a maximum of 8,000 characters	0-8000
Varchar[(n)]	Variable-length, Non-Unicode  data with a maximum of 8,000 characters	0-8000
Varchar(max)	Variable-length, Non-Unicode  data with a maximum of 2GB characters   and preferred over text data type	0-2 GB
text	Variable-length, non-Unicode data with a maximum length of 2^31 - 1 characters	0-2 GB
nchar	Fixed-length Unicode data with a maximum length of 4,000 characters	0-8000
nVarchar	Variable-length Unicode data with a maximum length of 4,000 characters	0-8000
nvarchar(max)	Variable-length, Non-Unicode  data with a maximum of 2GB characters  and preferred over ntext data type	0-2 GB
ntext	Variable-length Unicode data with a maximum length of 2^30 - 1 characters	0-2 GB

Binary Strings :

Data type	Description	No. Of bytes
binary[(n)]	Fixed-length binary data with a maximum length of 8,000 bytes	0-8000
varbinary[(n)]	Variable-length binary data with a maximum length of 8,000 bytes	0-8000
varbinary(max)	Variable-length binary data with a maximum of 2 GB bytes	0-2 GB
image	Variable-length binary data with a maximum length of 2^31 - 1 bytes	0-2 GB

Date and time :

Data type	Description	No. Of bytes
smallDatetime	Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute	4
Datetime	Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of 3.33 milliseconds	8

Monetary :

Data type	Description	No. Of bytes
smallmoney	Monetary data values from  -214,748.3648 through +214,748.3647	4
money	Monetary data values from  -2^63 through 2^63 - 1	8

Other Data Types :

Data type	Description	No. Of bytes
cursor	A reference to a cursor. A data type for variables or stored procedure OUTPUT parameters that contain a reference to a cursor. Any variables created with the cursor data type are nullable.	0-8
uniqueidentifier	A globally unique identifier	16
timestamp	A database-wide unique number that gets updated every time a row gets updated	8
sysname	Used for table columns, variables, and stored procedure parameters that store object names.	256
table	A special data type used to store a result set for later processing
sql_variant	A data type that stores values of various data types, except text, ntext, timestamp, and sql_variant	0-8016
rowversion	Same as that of timestamp
xml	16 bytes pointer	0-2GB