SQL Server DBA common concepts, operational analysis

1. How do I use char and varchar when designing a table? Please write down your understanding of varchar (max).

1. How do I use char and varchar when designing a table? Please write down your understanding of varchar (max).

Char: is fixed-length, that is, when you enter a character that is less than the number you specify, it will be followed by a null value. For example, a char (10) is defined, and the character you enter is less than 10 o'clock, and it will be followed by a null value. When you enter the word sizes at 10 o'clock, it intercepts the characters that are out of the bounds, which in short will account for 10 bytes of space. Char is convenient for storing fixed-length data, and indexes on char fields are extremely efficient because they are fixed lengths. An English character takes up one byte, and a Chinese character is two bytes. (because char is fixed-length, it can be much faster than varchar!) but the program is a bit more cumbersome to deal with, you should use trim and other functions to remove the space on both sides!)

nvarchar (n): A variable-length Unicode character data that contains n characters. The value of n must be between 1 and 4,000. You can store up to 4,000 characters, both English and Chinese, in 2 characters.

varchar: If the possible value of a field is not fixed length, we only know it cannot exceed 10 characters, it is the most advantageous to define it as varchar (10). Stores variable-length data, so it does not fill with spaces, but storage efficiency is no higher than char. 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. Up to 8,000 English, 4,000 Chinese characters.

VarChar generally applies to English and numerals, and nvarchar is suitable for Chinese and other characters.

The maximum varchar length in SQL Server is 8000, but you can use varchar (max) to reach 2G. (Explanation: A database file consists of a district, and the zone consists of eight physically contiguous pages, which are the basic units of data stored in SQL, and data pages are recorded.) A page size is 8K (8192 bytes), and the disk space allocated by the data file (. mdf or. ndf) in the database can be logically divided into pages (from 0 to n consecutively numbered). Disk I/O operations are performed at the page level, that is, the minimum data units that SQL Server reads or writes to data each time is a data page. A data page consists of a page header (96 bytes), a data row, a row offset (a minimum of 36 bytes), so the maximum length of the data row is 8x1024-96-36-7=8060,7, which means that SQL Server also needs to attach a minimum of 7 bytes of system data to the data for each row. ）

varchar (max):

In MSSQL2005 and above, a large value data type (varchar (max), nvarchar (max), varbinary (max)) is added. A large value data type can store up to 2^30-1 bytes of data.
These data types behave in the same way as the smaller data types varchar, nvarchar, and varbinary.
Microsoft's argument is that this data type is used instead of the previous text, ntext, and image data types, and the correspondence between them is:
varchar (max)-------text;
nvarchar (max)-----ntext;
varbinary (max)----image.

varchar (max):string is truncated

Replicate (' str ', n): auxiliary n str string

DECLARE @test varchar (max) set @test =replicate (' 1 ', 9000) print len (@test) workaround (1) Use the cast set @test =replicate (CONVERT ( varchar (max), ' 1 '), 9000)--cast to varchar (max). Print Len (@test) (2) splits 9,000 characters into 2 parts, each part less than 8000set @test =replicate (CONVERT (varchar (max), ' 1 '), 4000) +replicate ( CONVERT (varchar (max), ' 1 '),) print len (@test) uses the Select query result in SQL Server SSMS, the default 1 line for non-XML formats is limited to 65,535 characters, and the print A line can only output 8,000 characters. So to output the view content, you can save the result content as a CSV file. You can also convert the result data into XML format, with the following code:
SELECT CAST (' <a><![ cdata[' + CAST (@test as nvarchar (max)) + ']]></a> ' as XML reference: http://blog.csdn.net/z10843087/article/details/ 77584905 http://www.bubuko.com/infodetail-1883338.html Summary:

1, two not more than 8000 length of the "string itself" addition, the default total length is not more than 8000, if the sum after the length of more than 8000, more than 8000 of the portion will be truncated

Principle: ( string default is char type, char type maximum is 8000 characters, so want to add more than 8000 will be truncated))

2, the added two string has a length of more than 8000, the addition of two string itself after the result is not truncated.

Principle: (because more than 8000 of the string is temporarily stored by the varchar (max)/text, the maximum can be saved 2^31-1, so can accommodate more than 8000)

3, regardless of the length of the string, after assigning a string to the varchar (max) variable, add the "Add variable" to the third varchar (max) variable, and the result is not truncated.

So: What is the length of the two strings that cannot be expected to be added, and what is the length after the addition?

Do not add (+) directly to the two string itself, and to ensure that it is foolproof, assign the string to the varchar (max) variable and add it with the variable, because the way in which the variables are added is always fine. (or force the string data type to varchar (max) or text Class)

? Comparison:

1. Fixed length or variable length

The so-called length is fixed, when the length of the data to be saved will be automatically filled with English space after it, so that the length of the corresponding length, with the Var prefix, that is, the actual storage space is dynamic change, such as Varchar,nvarchar variable length character data will not be filled with spaces.

2.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.

3. Maximum capacity for storage of several data types

Char,varchar up to 8,000 English, 4,000 kanji

Nchar,nvarchar can store up to 4,000 characters, whether in English or kanji

SQL Server DBA common concepts, operational analysis