Yesterday, a colleague suddenly asked me, saying that when he created the following table in the SQL 2000 database, he suddenly encountered the following warning message. The SQL script and warning information are as follows:
- IF OBJECT_ID (N 'log') IS NULL
- BEGIN
- Create table Log
- (
- [Date] DATETIME,
- [Thread] NVARCHAR (255 ),
- [Level] NVARCHAR (50 ),
- [Logger] NVARCHAR (255 ),
- [Message] NVARCHAR (4000 ),
- [Exception] NVARCHAR (4000)
- )
- END
- ELSE
- PRINT ('this table already exists, check database ');
- GO
- Warning:
- The table 'log' has been created but its maximum row size (17159) exceeds the maximum number of bytes per row (8060 ).
- INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
I have never met such a warning message before. When I executed this script under SQL server 2000, I had this warning message. Why is Maximun ROW SIZE 17159 ?; When I run this script under SQL server 2005, there is no warning message. Is there any difference between SQL server 2005 and SQL SERVER 2000 in the storage mechanism? Before figuring out these problems, let's take a look at some other problems, that is, the maximum data rows in SQL SERVER 2000/2005 are 8060 bytes (for fixed-length data ), the size available in SQL server 2000 is 8039 bytes, while that available in SQL SERVER 2005 is 8053 bytes. We can experiment with the following script (SQL SERVER 2005)
- CREATE TABLE TEST
- (
- FIELD1 CHAR(4000),
- FIELD2 CHAR(4000),
- FIELD3 CHAR(53)
- )
- CREATE TABLE TEST1
- (
- FIELD1 CHAR(4000),
- FIELD2 CHAR(4000),
- FIELD3 CHAR(54)
- )
The minimum row size is 8061 = 4000 + 4000 + 54 + 7 (internal overhead ). Next we will change the data type of the above script, as shown below, to see the situation in SQL SERVER 2005
- IF OBJECT_ID (N 'log') IS NULL
- BEGIN
- Create table Log
- (
- [Date] DATETIME,
- [Thread] CHAR (255 ),
- [Level] CHAR (50 ),
- [Logger] CHAR (255 ),
- [Message] CHAR (4000 ),
- [Exception] CHAR (4000)
- )
- END
- ELSE
- PRINT ('this table already exists, check database ');
- GO
So how does the minimum row 8575 come from? Let's take a look at the Classic data row structure (derived from Inside SQL SERVER)
In fact, it is 8 + 255 + 50 + 255 + 4000 + 4000 = 8568 + 7 = 8575 of which the seven bytes are like this.
- Status Bits A 1
- Status Bits B 1
- Length of fixed-length ........ 2
- number of columns 2
- Null bitmap 1 bit for each column (6/8)1
Because there is no variable length field in the table, the other variable length-related bytes are 0, so 7. Next, let's take a look at the question of why maximum row size (17159 ),
8 + 255*2 + 50*2 + 255*2 + 4000*2 + 4000*2 = 17128
1 + 1 + 2 + 2 + 1 + 2 + 2*5 = 19
So 17128 + 19 = 17147, but the result is 17159, which is a bit unclear. I have not found out a lot of information. How did I calculate this 17159?
Recommended by editors]