The Cause and solution of SQL Server exceeding the maximum number of bytes per line (8060)

From: http://blog.163.com/wex660@126/blog/static/241530652008111411389746/

Today, my friend has encountered this problem. It seems that I have said it many times, so I will release it to avoid talking about it every time.
I. symptom
Generally, this phenomenon is applicable when SQL files are created in the query analyzer. Generally, the following prompts are displayed:

"Warning: The table 'xxxx has been created, but its maximum row size (89960) exceeds the maximum number of bytes (8060) for each row ). If the length of the result row exceeds 8060 bytes, insert or update of the row in this table will fail. "

The table 'xxxx' has been created, but its maximum row size (10438) exceeds the maximum number of bytes per line (8060 ). If the length of the result row exceeds 8060 bytes, insert or update of the row in this table will fail. XXXX is the name of the table you created, and 10438 is the total length of variable-length columns (such as nvarchar or varbinary) in your table creation statement, 8060 is the maximum limit on the row length of SQL Server.
Ii. Cause
In fact, the above three concepts should be clarified, and the cause of the warning should be clear, this is because the total length of variable-length columns in your table creation statements exceeds the limit of SQL Server to the maximum length of rows by 8060. If the total length of data in each row cannot exceed 8060 bytes, you can still Insert rows into the table. However, if the data exceeds 8060 bytes, the system prompts you that the insertion or update operation will fail.
Error message:
Server: Information 511, level 16, status 1, 5th rows
You cannot create a row with the size of 8060.
The statement has been terminated. For example, if I have 10 yuan in total, it may take 1-5 to buy a thing, 2-3 to buy B, and 3-6 to buy C, when I make a budget, I should remind myself that if ABC has to spend a maximum amount of money on three things, then my money will not be enough, because 5 + 3 + 6 = 14> 10, although I may only spend 1 + 2 + 3 = 6 yuan to buy ABC.
Iii. Solution
Knowing the cause of the problem is easy to solve!
1. Modify the data type or length of the corresponding columns in the table creation statement (for example, change the nvarchar format to text), so that the addition of variable length columns is less than 8060. This can completely avoid the occurrence of the above errors, of course, the above errors are not inevitable.
2. In most cases, when the length of each column exceeds the row limit (this should be determined based on the stored data), you can ignore this prompt, this does not necessarily affect your normal operations.
Iv. SQL server data details

The BIT data type is an integer, and its value can only be 0, 1, or null. This data type is used to store data with only two possible values, such as yes, no, true, false, on, or

Int data type can store integers from. This type can be used for almost all numeric data stored in the database. This data type occupies 4 bytes in the database.

Smallint data type can store integers from. This type of data is useful for storing numeric data that is often limited to a specific range. This data type occupies 2 bytes of space in the database.

The tinyint data type can store integers ranging from 0 to 255. It is useful when you only plan to store a limited number of values. This data type occupies 1 byte in the database

The decimal data type can be used to store numeric data with a fixed precision and range. When using this data type, you must specify the range and precision. The range is the total number of digits that can be stored around the decimal point. Precision is the number of digits stored on the right of the decimal point.

The money data type is used to indicate money and currency values. This data type can store data from-922 billion to 922 billion, accurate to one thousandth of the currency unit

The smallmoney data type is used to indicate money and currency values. This data type can store data from-214748.3648 to 214748.3647, accurate to one thousandth of the currency unit

Float data is an approximate value type for floating point numbers. Floating Point Numbers are similar because not all numbers in the range can be accurately expressed. Floating Point number can be any number from-1.79e + 308 to 1.79e + 308

The real data type is similar to a floating point number and is an approximate value type. It indicates the floating point number between-3.40e + 38 and 3.40e + 38.

The datetime data type is used to represent the date and time. This data type stores all the date and time data from the day of January 1, December 3, 9999, accurate to 1/300 seconds or 3.33 milliseconds

The smalldatetime data type is used to indicate the date and time between and accurate to one minute.

The cursor data type is a special data type that contains a reference to the cursor. This type of data is used in the stored procedure and cannot be used when creating a table.

The timestamp data type is a special data type used to create a unique digital within the database range. A table can have only one timestamp column. The value of the timestamp column changes each time a row is inserted or modified. Although its name contains "time", the timestamp column is not a date that people can recognize. In a database, the timestamp value is unique.

The uniqueidentifier data type is used to store a globally unique identifier (guid. GUID is indeed globally unique. This number has almost no chance of being rebuilt in another system. You can use the newid function or convert a string to a unique identifier to initialize a column with a unique identifier.

The Char data type is used to store non-uniform encoded data with a fixed length. When defining a column of this type, you must specify the column length. This data type is useful when you always know the length of the data to be stored. For example, when you store data in zip code and 4 character format, you know that 108000 characters are always used.

Varchar data type, which is different from charchar type, is variable-length. When defining a column as the data type, you must specify the maximum length of the column. The biggest difference between it and char data types is that the storage length is not the column length, but the data length.

The text data type is used to store a large amount of non-uniform encoding character data. This data type can contain up to 2 billion characters

The nchar data type is used to store fixed-length, unified encoding data. Unified encoding uses a dual-byte structure to store each character, rather than a single byte (in common text ). It allows a large number of extended characters. This data type can store 4000 characters, doubling the byte space used

The nvarchar data type is used as a variable-length uniform encoding type data. This data type can store 4000 characters, and the byte space is doubled.

The ntext data type is used to store a large amount of uniform encoding data. This type of data can be stored for nearly 1 billion characters, and the byte space is doubled.

The binary data type is used to store binary data that can be up to 8000 bytes in length. When the content of the input table is close to the same length, you should use this data type.

The varbinary data type is used to store up to 8000 bytes of long binary data. This type of data should be used when the content size of the input table is variable.

The image data type is used to store variable-length binary data up to or about 2 billion bytes.

 
5. Maximum values of various objects referenced by SQL Server
The following table describes the maximum value (quantity or size) of objects defined in the Microsoft SQL Server database or referenced in a Transact-SQL statement ). The following table does not contain Microsoft SQL Server 2000 Windows CE.
The number of locks per SQL server instance is 2,147,483,647 (or available memory) 2,147,483,647 (or available memory) 1. The network data packet size is the size of the data packet in table format (TDS). This data packet is used for applications.ProgramCommunication with the relational database engine. The default packet size is 4 kb, which is controlled by the network packet size configuration option.
2. in SQL Server 2000, the maximum number of bytes of any key cannot exceed 900. You can use a variable-length column to define the key. As long as no rows with more than 900 bytes of data are inserted in such columns, the maximum size of the rows can be greater than 900. For more information, see the maximum value of the index key.
3. When SQL Server 2000 Desktop Engine or Microsoft Data Engine (MSDE) 1.0 is used, the database size cannot exceed 2 GB.
4. database objects include all tables, views, stored procedures, extended stored procedures, triggers, rules, default values, and constraints. The total number of objects in a database cannot exceed 2,147,483,647.
Vi. Others
You can search for other restrictions on the processor and memory.