SQL Server Tour--eighth station composite index and include index how much difference?

Weekend finally moved out of the rental, installed broadband .... Only to find the days without the net ... What a kind of isolation it is ... Can't stand that kind of day anymore .... Well, since the net

Ann's up, and I have to go on with this series.

Indexes and locks, these two topics are very important to our development engineers ... Only by understanding these two topics can we write high-quality SQL statements, in the previous blog, I said

Indexes are single-column indexes ... Of course the database can not only recognize the single-column index, and my composite index, when it comes to composite index, may be familiar with the include index, the two indexes in the end

What is the difference, of course, I am also a rookie ... So the following is also my personal opinion ...

One: Look at the problem from the data page perspective

1. Do two tables, insert two data, make a composite index on test1, make an include index on test2, such as:

1 --Insert 2 records in the Test1 table2 CREATE TABLETest1 (IDint, NameCHAR(5), EmailCHAR(Ten))3 INSERT  intoTest1VALUES(1,'AAAAA','[email protected]')4 INSERT  intoTest1VALUES(2,'bbbbb','[Email protected].com')5 CREATE INDEXIdx_test1 onDbo.test1 (Name,email)6 7 --Insert 2 records in the Test2 table8 CREATE TABLETest2 (IDint, NameCHAR(5), EmailCHAR(Ten))9 INSERT  intoTest2VALUES(1,'AAAAA','[email protected]')Ten INSERT  intoTest2VALUES(2,'bbbbb','[email protected]') One CREATE INDEXIdx_test2 onDbo.test2 (Name) INCLUDE (Email)

2. Then view the data page record with the DBCC command

<1> take a look at the information for each slot in the Test1 table

1 DBCC TRACEON (2588,3604)2DBCC IND (ctrip,test1,-1)  3DBCC PAGE (Ctrip,1,194,1

1Slots0, Offset0x60, Length -, Dumpstyle BYTE2 3Record Type=Index_record RECORD Attributes=Null_bitmap Record Size=  -4 5MemoryDump @0x000000000fb0a0606 7 0000000000000000:16616161 61613131 314071712e636f6d†.aaaaa111@qq. com8 0000000000000010: c000000001000000 030000††††††††††††† ......9 TenSlots1, Offset0x7b, Length -, Dumpstyle BYTE One  ARecord Type=Index_record RECORD Attributes=Null_bitmap Record Size=  - -  -MemoryDump @0x000000000fb0a07b the  - 0000000000000000:16626262 62623232 324071712e636f6d†.bbbbb222@qq. com - 0000000000000010: c000000001000100 030000††††††††††††† ...... -  +OFFSETTABLE: -  +Row-Offset A 1(0x1)- 123(0x7b)                  at 0(0x0)-  the(0x60)

<2> take a look at each slot information in the Test2 table

1 DBCC TRACEON (2588,3604)2DBCC IND (ctrip,test2,-1)  3DBCC PAGE (Ctrip,1,207,1)

1Slots0, Offset0x60, Length -, Dumpstyle BYTE2 3Record Type=Index_record RECORD Attributes=Null_bitmap Record Size=  -4 5MemoryDump @0x000000000dfca0606 7 0000000000000000:166161616161c40000000100 00003131†.aaaaa ..... One 8 0000000000000010:314071712e636f6d030000†††††††††††††1@qq. com ... 9 TenSlots1, Offset0x7b, Length -, Dumpstyle BYTE One  ARecord Type=Index_record RECORD Attributes=Null_bitmap Record Size=  - -  -MemoryDump @0x000000000dfca07b the  - 0000000000000000:166262626262c40000000100 01003232†.bbbbb ..... A  - 0000000000000010:324071712e636f6d030000†††††††††††††2@qq. com ...  -  +OFFSETTABLE: -  +Row-Offset A 1(0x1)- 123(0x7b)                  at 0(0x0)-  the(0x60)

<3> from the Test1 and test2 data pages, there are two slot slots, and then we take test1 and test2 slot0 slots out of the comparison, is not aware of the difference between the two probably.

Test1のslot0

1 0000000000000000:   166161616161313131407171 2e636f6d†.aaaaa111@qq 2 0000000000000010: 01000000 030000 †††††††††††††     ...........    

Test2のslot0

1 0000000000000000:   166161610000010000003131 †.aaaaa .....  One 2 0000000000000010:   31407171030000†††††††††††††1@qq. Com...     

Let me dissect the slots in the next two tables:

6161616161 3131314071712e636f6d c0000000 0100 0000 0300 00

16: This is the system header data for the index record.

6161616161: Converted to decimal is 9797979797, that is, the character of the AAAAA.

3131314071712E636F6D: I think you know that, that is [email protected].

c000000010000000: Because we are heap tables, so this is the table of rowID, converted to decimal is: 192:1:0.

0300: This represents the number of records in the table, which is 3 records.

If you understand the above explanation, then we continue to look at test2のslot0, if you are careful, you will see in Test2, 111qq.com is at the end of the record ... So what does this show?

? What's the problem??? If you are familiar with the records, you know, in fact, the record of the variable length field values are generally placed in the tail of the record ... The advantage is that "line overflow" can be done. Also

You can exceed the 900 length limit of the index ... But the composite index can not do ... If you don't believe me, I can do an example of setting the length of name and email to 500.

The include index can pass smoothly .....

Shanghai is very cold these days, typing all shook hands ... Not ready to go on saying ... The next article continues to rip down the benefits of a composite index.

SQL Server Tour--eighth station composite index and include index how much difference?