4th week page Limit 8060 bytes

Congratulations to you! There are only a few pages left in front of you, and then you can complete the 1th month of SQL Server Performance Tuning training . Today I'm going to talk about some of the limitations of the page, and why you like these restrictions, and you'll hate them.

As you weekly hours in the 2nd, the 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 single page. When you deal with a fixed-length type of data type such as Char,int,datetime, you find that SQL Server has a limit of the length of the record that cannot exceed 8060 bytes (containing 7 bytes of internal row overhead).

page Limits--The good side

When your table is less than 8 columns, you need to add an additional 7 bytes of internal row overhead (for each record). Add an additional 1 byte to each additional 8 columns, for example, 17 columns, and you need 9 btyes of internal row overhead (7+1+1). If you try to create a longer record size, SQL Server returns an error message to you when you execute the create TABLE statement. Take a look at the following table definition:

1 CREATE TABLETooLargeTable12 (3Column1CHAR( the),4Column2CHAR( the),5Column3CHAR( Wu)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 the table, SQL Server returns the following error message.

When you create a table of more than 8 columns, you need to count the internal cost of the 8 bytes that SQL Server needs.

1 CREATE TABLETooLargeTable22 (3Column1CHAR( +) not NULL, Column2CHAR( +) not NULL,4Column3CHAR( +) not NULL, COLUMN4CHAR( +) not NULL,5Column5CHAR( +) not NULL, Column6CHAR( +) not NULL,6Column7CHAR( +) not NULL, COLUMN8CHAR( +) not NULL,7Column9CHAR( -) not NULL8 )9 GO

So here's another illegal table definition (8000+53+8 bytes), where SQL Server returns an error message.

page limits-the bad side

In the previous link I have shown you the page limit you like, because when you create such a table, SQL Server will return you an error message. But page limits also have a nasty side to you, because SQL Server allows you to create such tables, and sometimes the insert statement succeeds and sometimes fails ... Let's see.

The problem we face here is the variable length data type like VARCHAR . When these columns cannot exist on its own page, SQL Server moves them to the line offset in another page. This is called the Line overflow page (Row-overflow page). SQL Server leaves a bytes long pointer to the row overflow page on the original page .

In some cases where there are other combinations of columns, this pointer overflows the limit of 8060 bytes. Let's look at the following table definition:

1 CREATE TABLETooLargeTable32 (3Column1CHAR( the),4Column2CHAR( the),5Column3CHAR( -),6Column4VARCHAR( the)7 )8 GO

As you can see, I used the VARCHAR data Type here. You will see SQL Server here will give you a warning message. This warning prompts you to create the table, but it may fail when you execute the insert/update statement ...

The following INSERT statements in the table will succeed:

1 INSERT  intoTooLargeTable3VALUES2 (3REPLICATE('x', the),4REPLICATE('x', the),5 REPLICATE('x', -),6REPLICATE('x', +)7 )8 GO

The record size here is 8056 bytes long (5000+3000+30+19+7 bytes). In this case, SQL Server saves the data from column 4th on the main data page. But imagine the following insert statement:

1 INSERT  intoTooLargeTable3VALUES2 (3REPLICATE('x', the),4REPLICATE('x', the),5REPLICATE('x', -),6 REPLICATE('x', the)7 )8 GO

In the INSERT statement just now, SQL Server moves the 4th column of data to the row overflow page (Row-overflow page), because this bytes cannot be dropped in the main data page. This means that SQL Server will leave a pointer to a different page and 24bytes long, 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,insert statement execution failed!

These are bad restrictions because they are hidden when the database is operating, and the good side is visible as shown above when you define the table schema. What do you think it is?

Summary

When you design your table structure, you need to think very carefully about what you are doing. When dealing with pages in SQL Server, you will encounter a number of such restrictions that appear only after you execute them. Of course, when SQL Server gives you the error message, you are not allowed to create this table, basically it is all right.

When you get a warning, basically everyone will want to ignore it without ever wanting to. This is always a bad operation, because you have seen that your insert may fail at run time, and you can expect the problem to occur. I hope you now understand that this performance tuning training is a good insider and why it is important to understand the internal structure of the data page!

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

4th week page Limit 8060 bytes