Description and differences of some common data types in MSSQL
1, bigint
Occupies 8 bytes of storage space, with a value range of 19-bit integers between -2^63 (-9,223,372,036,854,775,808) and 2^63-1 (9,223,372,036,854,775,807), corresponding to the Int64/long in C #
2, int
Occupies 4 bytes of storage space, with a value range of 10-bit integers between -2^31 (-2,147,483,648) and 2^31-1 (2,147,483,647), corresponding to the int32/int in C #
3, smallint
Occupies 2 bytes of storage space, with a value range of 5-bit integers between -2^15 (-32768) and 2^15-1 (32767), corresponding to the Int16/short in C #
4, tinyint
Takes up 1 bytes of storage space and takes an integer value range of 0-255, corresponding to Byte/byte in C #
5, Bit
Up to 1 bytes of storage space, value 0/1 (false/true), corresponding to Boolean in C #
6, char (n)
Fixed-length, non-Unicode string data with a storage length of 1-8000 bytes, typically used to store data items with a length or length difference. n is the custom maximum length that can be stored.
For example, defining a data column of type char (5), when the number of stores is 123, ABCD, 12345, they occupy a storage space of 5 bytes
7. VARCHAR (N|MAX)
Non-fixed-length, non-Unicode string data with a storage length of 1-8000 bytes, typically used to store data items with a large difference in length. n is the custom maximum length that can be stored.
For example, the type of data column is varchar (5), and when the number of stores is 123, ABCD, 12345, they occupy a storage space of 3, 4, 5 bytes (Actual storage space + 2 bytes).
When N is max, the maximum storage length is 2^31-1 bytes
Recommendations from MSDN:
- If the column data items are of the same size, charis used.
- If the size of the column data items varies considerably, varcharis used.
- Use varchar (max)if the column data item size varies greatly and the size may exceed 8,000 bytes.
8, Nchar/nvarchar
Different from Char/varchar,nchar and nvarchar can store Unicode characters, such as Kanji
English characters occupy 1 bytes when stored in the database, Chinese and some other non-English characters occupy two bytes, if stored in char or varchar, the Chinese characters are garbled in the operating system. , in order to solve this problem, nchar and nvarchar two kinds of data types are produced.
In nchar and nvarchar, both Chinese and English characters occupy two bytes, so you can store up to 4,000 characters
9. Text/ntext/image Type
For storing large non-Unicode characters, Unicode characters, and binary data with fixed-length data types and variable-length data types, Microsoft says that the three types will be removed in future MSSQL releases, and it is recommended to use varchar (max)/nvarchar (max) and varbinary (max) instead
Other back to write, if there is inappropriate, please make bricks!
Description and differences of some common data types in MSSQL