A tangle of the smallest rows in the SQL Server table

Source: Internet
Author: User

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:

 
 
  1. IF OBJECT_ID (N 'log') IS NULL
  2. BEGIN
  3. Create table Log
  4. (
  5. [Date] DATETIME,
  6. [Thread] NVARCHAR (255 ),
  7. [Level] NVARCHAR (50 ),
  8. [Logger] NVARCHAR (255 ),
  9. [Message] NVARCHAR (4000 ),
  10. [Exception] NVARCHAR (4000)
  11. )
  12. END
  13. ELSE
  14. PRINT ('this table already exists, check database ');
  15. GO
  16. Warning:
  17. The table 'log' has been created but its maximum row size (17159) exceeds the maximum number of bytes per row (8060 ).
  18. 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)

 
 
  1. CREATE TABLE TEST  
  2. (  
  3. FIELD1 CHAR(4000),  
  4. FIELD2 CHAR(4000),  
  5. FIELD3 CHAR(53)  
  6. )  
  7. CREATE TABLE TEST1  
  8. (  
  9. FIELD1 CHAR(4000),  
  10. FIELD2 CHAR(4000),  
  11. 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

 
 
  1. IF OBJECT_ID (N 'log') IS NULL
  2. BEGIN
  3. Create table Log
  4. (
  5. [Date] DATETIME,
  6. [Thread] CHAR (255 ),
  7. [Level] CHAR (50 ),
  8. [Logger] CHAR (255 ),
  9. [Message] CHAR (4000 ),
  10. [Exception] CHAR (4000)
  11. )
  12. END
  13. ELSE
  14. PRINT ('this table already exists, check database ');
  15. 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.

 
 
  1. Status Bits A 1  
  2. Status Bits B 1  
  3. Length of fixed-length ........ 2  
  4. number of columns 2  
  5. 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]

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.