SQL Server Tour (1): The Women Lebron Hyperdunk 2013 trip brought us welfare system view that the SQL Server (2): Understanding SQL Server table scan evil Tour (3): doubts those years back people clustered index SQL Server Tours (4): You must know the non-clustered index scan SQL Server's Journey (5): indeed have to say DBCC command of SQL Server trip (6): Use winHex weapon brigade deepen understanding of SQL Server data pages (7 ): Why say state and less of the field can not be indexed weekend finally moved into rental housing, installed broadband. ʱ?? ʱ?? ʱ?? Only to find no net day. ʱ?? ʱ?? It is a kind of isolated way. ʱ?? ʱ?? I can not stand that kind of day. ʱ?? ʱ?? ʱ?? Well, since network security go up, I have to continue this series. Index and locks, these two themes of our development engineers, it is very important. ʱ?? ʱ?? Only by Nike Air Max understanding these two themes, we can write high-quality sql statements in the previous blog, I mentioned index is a separate index. ʱ?? ʱ?? Of course, the database can not only recognize a separate index, as well as I that this composite index, when it comes to the composite index, may be familiar with when it comes to people will include index, and that these two indexes in the end what difference does it make, 2015 Nike Free 5.0 of course, I was a novice pieces. ʱ?? ʱ?? So the following is my personal opinion. ʱ?? ʱ?? One: From the perspective of the problem data page 1. do two tables, insert two data, do the composite index on test1, do include an index on test2, as Air Jordan Outlet follows: - insert two records in the CREATE TABLE test1 test1 table (ID int, Name CHAR (5), Email CHAR (10)) INSERT INTO test1 VALUES (1, 'aaaaa', '111@qq.com') INSERT INTO test1 VALUES (2, 'bbbbb', '222 @ qq .com ') CREATE INDEX idx_test1 ON dbo.test1 (Name, Email) - insert in test2 table two records CREATE TABLE test2 (ID int, Name CHAR (5), Email CHAR (10)) INSERT INTO test2 VALUES ( 1, 'aaaaa', '111@qq.com') INSERT INTO test2 VALUES (2, 'bbbbb', '222@qq.com') CREATE INDEX idx_test2 ON dbo.test2 (Name) INCLUDE (Email) 2. Then View by DBCC command data page recording \u0026 lt; 1 \u0026 gt; take a look at the information table test1 each slot DBCC SALOMON TRACEON (2588,3604) DBCC IND (Ctrip, New Nike Free 5.0 V4 Shoes Black Red test1, -1) DBCC PAGE (Ctrip, 1,194,1) Slot 0, Offset 0x60, Length 27, DumpStyle BYTERecord Type = INDEX_RECORD Record Attributes = NULL_BITMAP Record Size = 27Memory Dump @ 0x000000000FB0A0600000000000000000: 16616161 61613131 31407171 2e636f6d +.aaaaa111@qq.com 0000000000000010: c0000000 01000000 030000 +++++++++ New Nike Free Run 3 Shoes Women Grey Purple Womens ++++ ........... Slot 1, Offset 0x7b, Length 27, DumpStyle BYTERecord Type = INDEX_RECORD Record Attributes = NULL_BITMAP Record Size = 27Memory Dump @ 0x000000000FB0A07B0000000000000000: 16626262 62623232 New Nike Tr Fit Running Shoes Black 32407171 2e636f6d +.bbbbb222@qq .com 0000000000000010: c0000000 01000100 030000 +++++++++++++ ........... OFFSET TABLE: Row - Offset 1 (0x1) - 123 (0x7b) 0 (0x0) - 96 (0x60) \u0026 lt; 2 \u0026 gt; look at the test2 table each slot information DBCC TRACEON (2588,3604) DBCC IND (Ctrip, Air Max 2012 Green Orange Black test2, -1) DBCC PAGE (Ctrip, 1,207,1) Slot 0, Offset 0x60 , Length 27, DumpStyle BYTERecord Type = INDEX_RECORD Record Attributes = NULL_BITMAP Record Size = 27Memory Dump @ 0x000000000DFCA0600000000000000000: 16616161 6161c400 00000100 00003131 + .aaaaa ........ 11 0000000000000010: 31407171 2e636f6d 030000 ++++++++ +++++ 1@qq.com ... Slot 1, Offset 0x7b, Length 27, DumpStyle BYTERecord Type = INDEX_RECORD Record Attributes = NULL_BITMAP Record Size = 27Memory Dump @ 0x000000000DFCA07B0000000000000000: 16626262 6262c400 00000100 01003232 + .bbbbb .... .... 22 0000000000000010: 32407171 2e636f6d 030000 +++++++++++++ 2@qq.com ... OFFSET TABLE: Row - Offset 1 (0x1) - 123 (0x7b) 0 (0x0) - 96 (0x60) \u0026 lt; 3 \u0026 gt; data from test1 and test2 page view, are two slot slots, then we test1 and test2 out to compare the slot0 slot next, is not to know about both What is the difference. test1 の slot00000000000000000: 16616161 61613131 31407171 2e636f6d +.aaaaa111@qq.com 0000000000000010: c0000000 01000000 030000 +++++++++++++ New Nike Tr Fit Shoes Black Purple ........... test2 の slot00000000000000000: 16616161 6161c400 00000100 00003131 + .aaaaa ........ 11 0000000000000010: 31407171 2e636f6d 030000 +++++++++++++ 1@qq.com ... Here I carefully dissected two table slot Content: 16 6161616161 3131314071712e636f6d c0000000 0100 0000 0300 0016: This is a system header data index record. 6161616161: 9797979797 Air Max 2012 Blue Silver White is converted to decimal, which is the character of aaaaa. 3131314071712e636f6d: I think you understand this, which is 111@qq.com. c000000010000000: Because we are a heap table, so this is RowID table, converted to decimal is: 192: 1: 0.0300: This indicates the number of records in the table, that is Nike Air Max 2011 three records. If you explain New Nike Free 5.0 V4 Shoes Black Blue to understand the above, we continue to look at the test2 の slot0, if you look carefully, you will see test2 in, 111qq.com is finally recorded. ʱ?? ʱ?? This shows that the problem? ʱ?? ʱ?? If you are familiar with the case record, you know, in fact, the value of variable-length fields in the record are usually placed at the end record. ʱ?? ʱ?? Advantage is New Nike Free 5.0 V4 Shoes Black Red that you can do it 'line overflow.' That is, the index may exceed 900 length limit. ʱ?? ʱ?? The composite index was unable to do so. ʱ?? Air Max 2012 Blue Grey White ʱ?? If you do not believe I can be an example, the length of the name and email set length 500. And include an index but can go through. ʱ?? ʱ?? ʱ?? ʱ?? These days Shanghai particularly cold shiver begin typing are playing. ʱ?? ʱ?? Not prepared to continue to say. ʱ?? ʱ?? Next to continue to tear down the composite index in the end what benefits can bring. TheSQL Server trip (8): Composite index and include indexes in the end there is much difference?