Week 4 Page limit 8060 bytes_html/css_WEB-ITnose

Congratulations! There are only a few pages left in front of you, and then you can complete the first month of SQL Server Performance Tuning Training. Today I'm going to talk about some of the limitations on the next page and why you'll love them and why you'll hate them.

As you learned in Week 2, a data page is always 8kb in size, and you can only store 8060 bytes on it. Your record size indicates how many records you can store on a page. When you deal with fixed-length data types such as CHAR, INT, DATETIME, etc., you will find that SQL Server has a limit that the record length cannot exceed 8060 bytes (including 7 bytes of internal row overhead).

Page limit? The good side

When your table has fewer than 8 columns, you need to add an additional 7 bytes of internal row overhead (for each record). For every 8 additional columns you add an extra 1 byte, for example, for 17 columns you need 9 btyes of internal row overhead (7 1 1). If you try to create a longer record size, SQL Server will return an error message to you when you execute the CREATE TABLE statement. Let’s take a look at the following table definition:

1 CREATE TABLE TooLargeTable12 (3    Column1 CHAR(5000),4    Column2 CHAR(3000),5    Column3 CHAR(54)6 )7 GO

As you can see, each record requires 8061 bytes (5000 3000 54 7 bytes). So in this case, when you try to create this table, SQL Server will return the following error message.

When you create a table with more than 8 columns, you need to include the 8 bytes of row internal overhead required by SQL Server.

1 CREATE TABLE TooLargeTable22 (3    Column1 CHAR(1000) NOT NULL,   Column2 CHAR(1000) NOT NULL,4    Column3 CHAR(1000) NOT NULL,   Column4 CHAR(1000) NOT NULL,5    Column5 CHAR(1000) NOT NULL,   Column6 CHAR(1000) NOT NULL,6    Column7 CHAR(1000) NOT NULL,   Column8 CHAR(1000) NOT NULL,7    Column9 CHAR(53) NOT NULL8 )9 GO

So here is another illegal table definition (8000 53 8 bytes), and SQL Server will return an error message.

Page limits?? The bad side

In the previous link I have shown you the side of page limits that you like, because when you create such a table, SQL Server will return You got an error message. But page limits also have a nasty side, because SQL Server will allow you to create such a table, and sometimes the INSERT statement will succeed and sometimes it will fail... Let's take a look.

The problem we face here is with variable length data types like VARCHAR. When these columns cannot exist on its own page, SQL Server moves them to row offsets on another page. This is called a Row-Overflow page . SQL Server will leave a 24 bytes long pointer to the row overflow page in the original page.

In some cases when other columns are combined, this pointer will exceed the 8060 bytes limit. Let’s take a look at the following table definition:

1 CREATE TABLE TooLargeTable32 (3    Column1 CHAR(5000),4    Column2 CHAR(3000),5    Column3 CHAR(30),6    Column4 VARCHAR(3000)7 )8 GO

As you can see, here I used the data type of VARCHAR (3000) . You will see that SQL Server will give you a warning message here. This warning indicates that you can create this table, but it may fail when executing the INSERT/UPDATE statement...

The following insert statement in the table will succeed:

1 INSERT INTO TooLargeTable3 VALUES2 (3    REPLICATE('x', 5000),4    REPLICATE('x', 3000),5    REPLICATE('x', 30),6    REPLICATE('x', 19)7 )8 GO

The record size here is 8056 bytes long (5000 3000 30 19 7 bytes). In this case, SQL Server will save the data in column 4 in the main data page. But imagine the following INSERT statement:

1 INSERT INTO TooLargeTable3 VALUES2 (3    REPLICATE('x', 5000),4    REPLICATE('x', 3000),5    REPLICATE('x', 30),6    REPLICATE('x', 3000)7 )8 GO 

In the INSERT statement just now, SQL Server will move the 4th column data to the row-overflow page (row-overflow page), because These 3000 bytes cannot be placed in the main data page. This means that SQL Server will leave a 24bytes long pointer to a different page where the data can be found. But our record size is now 8061 bytes long (5000 3000 30 24 7 bytes).

Duang, your record length exceeds 8060 bytes, and the INSERT statement failed to execute!

These are bad restrictions because they are hidden during database operations. The good thing is that when you define The table structure is visible as shown above. Think about it, what is it?

Summary

When you design your table structure, you have to think about your operations very carefully. When dealing with pages in SQL Server you will encounter many such limitations that only appear after execution. Of course, when SQL Server gives you an error message, you are not allowed to create this table, and the world is basically at peace.

When you receive a warning, almost everyone ignores it without even thinking about it. This is always a bad move, because as you've seen, your INSERT may fail at runtime, and you can expect the problem to occur. I hope you now understand that this performance tuning training is a good low-down and why it is so important to understand the internal structure of data pages!

In the next training I will explain more details about heap tables in SQL Server, and why they are sometimes good (design) and sometimes bad (design). Please stay tuned and enjoy the remaining 7 days!