SQL Server data types

SQL Server data type character string:

Data Type	Description	Storage
CHAR (n)	A fixed-length string. A maximum of 8,000 characters.	N
VARCHAR (n)	A variable-length string. A maximum of 8,000 characters.
varchar (max)	A variable-length string. A maximum of 1,073,741,824 characters.
Text	A variable-length string. Up to 2GB character data.

Unicode string:

Data Type	Description	Storage
NCHAR (n)	Fixed-length Unicode data. A maximum of 4,000 characters.
nvarchar (n)	Variable-length Unicode data. A maximum of 4,000 characters.
nvarchar (max)	Variable-length Unicode data. A maximum of 536,870,912 characters.
ntext	Variable-length Unicode data. Up to 2GB character data.

Binary Type:

Data Type	Description	Storage
Bit	Allow 0, 1, or NULL
Binary (n)	Fixed-length binary data. Up to 8,000 bytes.
varbinary (n)	Variable-length binary data. Up to 8,000 bytes.
varbinary (max)	Variable-length binary data. Up to 2GB bytes.
Image	Variable-length binary data. Up to 2GB.

Number type:

Data Type	Description	Storage
tinyint	All numbers from 0 to 255 are allowed.	1 bytes
smallint	All numbers from 32,768 to 32,767 are allowed.	2 bytes
Int	All numbers from 2,147,483,648 to 2,147,483,647 are allowed.	4 bytes
bigint	Allows all 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 proportional numbers. Allows a number from -10^38 +1 to 10^38-1. The P parameter indicates the maximum number of digits that can be stored (left and right of the decimal point). P must be a value between 1 and 38. The 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 between 0 and P. The default is 0.	5-17 bytes
Numeric (P,s)	Fixed-precision and proportional numbers. Allows a number from -10^38 +1 to 10^38-1. The P parameter indicates the maximum number of digits that can be stored (left and right of the decimal point). P must be a value between 1 and 38. The 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 between 0 and P. The default is 0.	5-17 bytes
SmallMoney	Currency data between -214,748.3648 and 214,748.3647.	4 bytes
Money	Currency data between -922,337,203,685,477.5808 and 922,337,203,685,477.5807.	8 bytes
Float (n)	Floating precision digital data from -1.79e + 308 to 1.79E + 308. The parameter n indicates whether the field holds 4 bytes or 8 bytes. Float (24) holds 4 bytes, while float (53) holds 8 bytes. The default value for N is 53.	4 or 8 bytes
Real	Floating precision digital data from -3.40e + 38 to 3.40E + 38.	4 bytes

Date Type:

Data Type	Description	Storage
Datetime	From January 1, 1753 to December 31, 9999, the accuracy is 3.33 milliseconds.	8 bytes
DateTime2	From January 1, 1753 to December 31, 9999, the accuracy is 100 nanoseconds.	6-8 bytes
smalldatetime	From January 1, 1900 to June 6, 2079, the accuracy is 1 minutes.	4 bytes
Date	Only dates are stored. From January 1, 01 to December 31, 9999.	3 bytes
Time	Storage time only. The accuracy is 100 nanoseconds.	3-5 bytes
DateTimeOffset	Same as datetime2, plus time zone offset.	8-10 bytes
Timestamp	Stores a unique number that is updated whenever a row is created or modified. The timestamp is based on the internal clock and does not correspond to real time. There can be only one timestamp variable per table.

Other data types:

data class Type	description
sql_ Variant	stores data of up to 8,000 bytes of different data types, except text, ntext, and timestamp.
uniqueidentifier	stores the global identifier (GUID).
xml	storage X ML format data. Up to 2GB.
cursor
table	storage The result set for later processing.

2. Fixed or variable length
   The so-called length is fixed, when the input length of the data does not reach the specified length will be automatically filled with English space after it, so that the length of the corresponding length, the var prefix, indicating that the actual storage space is longer, such as Varchar,nvarchar variable-length character data is not padded with spaces, except that the text is stored in a variable length.
4. Unicode or non-Unicode
   In a database, English characters require only one byte to store, but Chinese characters and many other non-English characters require two bytes of storage. If the English and Chinese characters exist simultaneously, because of the different occupied space, it is easy to cause confusion, resulting in the reading of the string is garbled. The Unicode character set is created to address the incompatibility of the character set, and all of its characters are represented in two bytes, meaning that the English character is also represented in two bytes. The prefix n represents Unicode characters, such as Nchar,nvarchar, which use the Unicode character set.
6. Look at the field capacity based on the two points above
   

   Char,varchar	Up to 8,000 English, 4,000 kanji
   Nchar,nvarchar	Can store 4,000 characters, whether English or Chinese

8. Use
   If the amount of data is very large and can be 100% to determine the length and save only ANSI characters, Char
   Can determine the length is not necessarily ANSI characters or, then use nchar;
   For oversized data, such as article content, use nText
   Other general-purpose nvarchar
10. Character comparison of char, varchar, nchar and nvarchar

• CHAR
  Char is convenient for storing fixed-length data, and the index on achar field is highly efficient, such as defining char(10), which takes up 10 bytes of space regardless of whether the data you store is 10 bytes.
• VARCHAR
  Store variable length data, but the storage efficiency is not high, if the value of a field may be not fixed length, we only know it cannot exceed 10 characters, it is the most advantageous to define it as VARCHAR(10). The actual length of the VARCHAR type is +1 of the actual length of its value. Why "+1"? This byte is used to hold the length that is actually used.

From the space consideration, with the varchar suitable, from the efficiency consideration, uses the char to be suitable, the key is to find the tradeoff point according to the actual situation.

• TEXT
  Text stores non-Unicode data of variable length, with a maximum length of 2^31-1 (2,147,483,647) characters.
• NCHAR, NVARCHAR, NTEXT
  These three kinds of names from the first three more than the previous "N". Compared to charandvarchar ,ncharandnvarchar store up to 4,000 characters, whether in English or Chinese characters, while Char, varchar can store up to 8,000 English, 4,000 Chinese characters. It can be seen that the use of nchar,nvarchar data types without worrying about the input characters are English or Chinese characters, more convenient, but in the storage of English number of some losses.

So generally, if it contains Chinese characters, use Nchar/nvarchar, if pure English and numbers, with Char/varchar.

SQL Server data types