Several common thresholds in SQL Server databases

Original: Several common thresholds in SQL Server database

The source of this article: http://www.cnblogs.com/wy123/p/6709520.html

What is the maximum length of the 1,sql statement or stored procedure (SQL string capacity)?

Often people ask, my SQL statements are pieced together, it may be very long, if too long, is not SQL Server support is not moving?
The maximum length of a SQL statement or stored procedure is 65,536 * network packet size, so what is this network packet size?
Refer to the following, is the size of the network packet, the default is 4,096 bytes, that is, 4KB,
So by default, the maximum length of the SQL statement is 65,536 * 4kb = 255.996093MB, close to 256MB,
So under normal circumstances, you can not worry about "My SQL statement is too long, is not going to explode SQL Server," but this does not mean that I support the form of a string to piece together a very long SQL statement practice.

　　

The above configuration can also be queried from the configuration table

　　

The maximum number of bytes for a string or variable that the 2,print keyword can print is 8000

When the Print keyword is in the print string, the maximum length of the string that can be printed is 8,000 bytes, and if the string or variable is longer than 8,000 bytes, it is truncated.
Here 8000 is the number of bytes, not the number of characters, if the string is a Unicode character, then the number of printed characters will be 4000, including the naked eye can not see the carriage return line.
Because someone is debugging the stored procedure, it is not strange to find out what print is inconsistent with the expected.
For example, each line of the original string is the same length, and the print is truncated because it is here

3, themaximum character length displayed in the SSMs table when the SELECT statement is queried

The default setting for this length is 65535, meaning that if it is varchar (max) or nvarchar (max), the result is more than 65,535 bytes, the result is truncated,
However, this does not prove that the query result is wrong, just that the results are not fully displayed in the SSMs table.
If there is a long field, be careful when copying the results of the query, direct replication is likely to replicate a defective result.
The test, however, is a bit different from the 65535 here.

　　

4,varchar (max) and nvarchar (max) support for maximum storage capacity

The maximum storage capacity for varchar (max) and nvarchar (max) is 2^31-1 and bytes, that is, one byte is 2GB long,
A plain text txt ebook is only large, so in most cases it is not necessary to say that the string I have stored is very long and that varchar (max) will not work.
However, nvarchar (max) stores a maximum number of characters less than varchar (max), because nvarchar (max) occupies two bytes. To figure out the encoding type of the stored object.
Test to verify the maximum storage capacity problem

　　

5,CTE maximum number of recursion supported during recursion

The maximum recursion count is 100 by default and can be forced recursively to 32,767 times
By default, such as the following SQL recursion 99 times, execution is not a problem

　　

An error occurs after the SQL statement is executed, exceeding the maximum number of default recursion

　　

The following is the way to force recursion to the maximum of 32,767 times by option (Maxrecursion 32767)

Scope of the Time Type field in the 6,sql server database

Similar to the type of numeric type, the time type is the same, each type has a certain range, not any one type of variable can be arbitrarily assigned value.
Operation time type data, accidentally error, because different time types have different range of values, the scope of the wrong time will also out-of-range error

　　

The range of values for the various time types and the space occupied are as follows

As for the same type, the space occupied is different because the precision of the expression is not the same when the space occupied is not the same

　　

Think of it and add it.

Several common thresholds in SQL Server databases