Usage of Common Data Types

Data Type	Size	Description
Bigint	8 bytes	- Range of value: -9,223,372,036,854,775,808 to   9,223,372,036,854,775,807 - Identity Specification: Yes
Uniqueidentifier	16 bytes	- Format: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx - Default Value or Binding: NEWID()
Bit	1 byte (1-8 bit) 2 bytes (9-16 bit)	- TRUE will converted to 1 - FALSE will converted to 0
Smallint	2 bytes	- Range of value: -32,768 to 32,767
Int	4 bytes	- Range of value: -2,147,483,648 to 2,147,483,647
Tinyint	1 byte	- Range of value: 0 to 255
Float	4 bytes (1-24) 8 bytes (25-53)	- Avoid using in WHERE clause, especially = and <> - It only used in > or < - Default value: float(53)
Real	4 bytes	- Avoid using in WHERE clause, especially = and <> - It only can use in > or < - It equals to float(24)
Decimal(p, s)	5 bytes (1-9p) 9 bytes (10-19p) 13 bytes (20-28p) 17 bytes (29-38p)	- It will not round up the value - precision(p): Number of digit - scale(s): Number of decimal point
Money	8 bytes	- Range of value: -922,337,203,685,477.5807 to   922,337,203,685,477.5807 - It will round up the value
Smallmoney	4 bytes	- Range of value: -214,748.3647 to 214,748.3647 - It will round up the value
Smalldatetime	4 bytes	- Range of value: Jan 1, 1900 to Jun 6, 2079 - accuracy of 1 minute
DateTime	8 bytes	- Range of value: Jan 1, 1753 to Dec 31, 9999 - accuracy of 3.33 milliseconds - Default Value or Binding: getdate()
DateTime2	6-8 bytes	- Range of value: Jan 1, 0001 to Dec 31, 9999 - accuracy of 100 nanoseconds
DateTimeOffset	8-10 bytes	- It same as datetime2 with the addition of a time zone offset
Date	3 bytes	- Range of value: Jan 1, 0001 to Dec 31, 9999
Time	3-5 bytes	- accuracy of 100 nanoseconds
Timestamp	8 bytes	- Any update made will changes the timestamp value - It cannot be used as primary key
Char(n)		- Maximum length: 8,000 characters - Non-Unicode - Fixed-length: use all the space that defines
NChar(n)		- Maximum length: 4,000 characters - Accept unicode (e.g. Chinese) - Fixed-length: use all the space that defines
VarChar(n)		- Maximum length: 8,000 characters - Non-Unicode - Variable-length
NVarChar(n)		- Maximum length: 4,000 characters - Accept Unicode (e.g. Chinese) - Variable-length
Text		- Maximum length: 2,147,483,647 characters - Accept ASCII code - Variable-length
NText		- Maximum length: 1,073,741,823 characters - Unicode (e.g. Chinese) - Variable-length - It do not allow for sorting, group by, and distinct
Binary(n)		- Maximum length: 8,000 bytes - Fixed-length: use all the space that defines
Varbinary(n)		- Maximum length: 8,000 bytes - Variable-length
Image		- Maximum length: 2,147,483,647 bytes - Variable-length - BMP, TIFF, GIF, or JPEG format