Understanding of common SQL Server data types

Roughly divided into characters, 2 binary, numeric, date, other types

Character

Char is fixed-length. such as char (8), the character you enter is less than 8 o'clock, and it will be followed by a null value. When you enter a character that is larger than the specified number, it intercepts the characters that are out of the bounds.

varchar (n) a variable-length, non-Unicode character data that contains n bytes. The value of n (1-8,000), which stores the actual length of bytes of input data, rather than n bytes . The input data character length can be zero.
nvarchar (n) contains a variable-length Unicode character data of n characters. The value of n (1-4,000), the byte storage size is twice times the number of input characters.       The input data character length can be zero.

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.

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

2 binary
bit allows 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.
Numeric Type
tinyint allows all numbers from 0 to 255. 1 bytes
smallint allows all numbers from 32,768 to 32,767. 2 bytes
int allows all numbers from 2,147,483,648 to 2,147,483,647. 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 number. 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 1to a value between 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 The currency data between -214,748.3648 and 214,748.3647. 4 bytes
Money 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
datetime is from January 1, 1753 to December 31, 9999 with a precision of 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 stores only dates.   From January 1, 01 to December 31, 9999. 3 bytes
time is stored only.   The accuracy is 100 nanoseconds. 3-5 bytes
The DateTimeOffset is the same as datetime2, plus the 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.

   

Understanding of common SQL Server data types