Shulou(Shulou.com)06/01 Report--

This article will explain in detail what data types Sql Server has. Xiaobian thinks it is quite practical, so share it with you for reference. I hope you can gain something after reading this article.

Character String: A data type description stores char(n) as a fixed-length string. Up to 8,000 characters. nvarchar(n) A variable length string. Up to 8,000 characters. varchar(max) A variable length string. Up to 1,073,741,824 characters. text Variable length string. Up to 2GB of character data. Unicode string: A data type description stores nchar(n) fixed-length Unicode data. Up to 4,000 characters. nvarchar(n) Variable length Unicode data. Up to 4,000 characters. nvarchar(max) Variable length Unicode data. Up to 536,870,912 characters. ntext Variable length Unicode data. Up to 2GB of character data. Binary type: Data type description Store bits that allow 0, 1, or NULLbinary(n) fixed-length binary data. Up to 8,000 bytes. varbinary(n) Binary data of variable length. Up to 8,000 bytes. varbinary(max) Binary data of variable length. Up to 2GB bytes. image Binary data of variable length. Up to 2GB. Number type: Data type description stores tinyint allows all numbers from 0 to 255. 1 byte smallint allows all numbers from-32,768 to 32,767. 2 byte int allows all numbers from-2,147,483,648 to 2,147,483,647. The 4-byte 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 scale numbers. Numbers from-10^38 +1 to 10^38 -1 are allowed.

The p parameter indicates the maximum number of digits that can be stored (left and right of the decimal point). p must be 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 byte numeric(p,s)

Fixed precision and scale numbers. Numbers from-10^38 +1 to 10^38 -1 are allowed.

The p parameter indicates the maximum number of digits that can be stored (left and right of the decimal point). p must be 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 byte smallmoney Monetary data between-214,748.3648 and 214,748.3647. 4 bytes money Monetary 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. 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-byte Date type: Data type description stores datetime from January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds. 8 bytesdatetime2 from January 1, 1753 to December 31, 9999 with an accuracy of 100 nanoseconds. 6-8 Bytessmalldatetime runs from January 1, 1900 to June 6, 2079 with an accuracy of one minute. 4 bytesdate Store only dates. From January 1, 0001 to December 31, 9999. 3 bytetime stores only time. Accuracy is 100 nanoseconds. 3-5 Bytesdatetimeoffset is the same as datetime2, plus a time zone offset. 8-10 Bytestamp stores a unique number that is updated each time a row is created or modified. Timestamp is based on an internal clock and does not correspond to real time. Each table can have only one timestamp variable. sql_variant stores up to 8,000 bytes of data of different data types, except text, ntext, and timestamp. uniqueidentifier stores a global identifier (GUID). XML stores XML formatted data. Up to 2GB. cursor stores a reference to a pointer for database operations. The table stores the result set for later processing.

Fixed or variable length

The so-called fixed length is fixed length, when the input data length does not reach the specified length will automatically fill in English spaces behind it, so that the length reaches the corresponding length; var prefix, indicating that the actual storage space is variable, such as varchar,nvarchar variable length character data will not be filled with spaces, the exception is, text storage is also variable length.

Unicode or non-Unicode

In the database, English characters only need one byte to store, but Chinese characters and many other non-English characters need two bytes to store. If English and Chinese characters exist at the same time, it is easy to cause confusion due to the different space occupied, resulting in the character string read out is garbled. Unicode character set is to solve the problem of incompatibility of character sets, all its characters are represented by two bytes, that is, English characters are also represented by two bytes. The prefix n represents Unicode characters, such as nchar,nvarchar, which use the Unicode character set.

Based on the above two points, look at the field capacity

char, varchar Up to 8000 English, 4000 Chinese characters nchar, nvarchar can store 4000 characters, whether English or Chinese

use

If the amount of data is very large, and can 100% determine the length and save only ansi characters, then char can determine the length and not necessarily ansi characters or, then use nchar;

For very large data, such as article content, use nText

Other generic nvarchar

char, varchar, nchar, nvarchar characteristics comparison

CHARCHAR is very convenient to store fixed-length data, CHAR field index efficiency level is high, such as the definition of char(10), then regardless of whether you store the data up to 10 bytes, have to take up 10 bytes of space.

VARCHAR stores variable-length data, but storage efficiency is not as high as CHAR, if a field may have values of variable length, we only know that it cannot exceed 10 characters, and defining it as VARCHAR(10) is the most cost-effective. The actual length of a VARCHAR type is the actual length of its value +1. Why "+1"? This byte is used to store how much length was actually used.

From the perspective of space, varchar is appropriate; from the perspective of efficiency, char is appropriate. The key is to find the trade-off point according to the actual situation.

TEXT

text Stores variable-length non-Unicode data up to a maximum length of 2^31-1(2,147,483,647) characters.

NCHAR, NVARCHAR and NTEXT have more "N" than the first three from the name. Compared with char and varchar, nchar and nvarchar can store up to 4000 characters, whether English or Chinese, while char and varchar can store up to 8000 English characters and 4000 Chinese characters. It can be seen that when using nchar and nvarchar data types, there is no need to worry about whether the input characters are English or Chinese characters, which is more convenient, but there is some loss in the number of English stored.

So generally speaking, if it contains Chinese characters, use nchar/nvarchar, if it is pure English and numbers, use char/varchar.

About "Sql Server what data types" this article is shared here, I hope the above content can be of some help to everyone, so that you can learn more knowledge, if you think the article is good, please share it to let more people see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.