bytes in C#

Creation QR Code in C# bytes

You can retrieve this information from the sys.dm_db_index_physical_stats dynamic management function by executing the following query:

SELECT * FROM sys.dm_db_index_physical_stats( DB_ID() ,OBJECT_ID('Test.IncludedColumnsTest') ,NULL ,NULL ,'DETAILED' ) AS a;

The total size of the index is reduced by only about 4 percent because the leaf levels of both indexes contain the same data. However, the nonleaf levels of the index with included columns contain only the one column that is in the index s key (plus pointers to the next level), while, for the other index, all columns are part of the index key, making each row in the nonleaf level roughly the same size as that of the leaf level. Table 6-6 shows the layout of each level of a NoIncludedColumns index.

1 page with 4 rows pointing to the next level 4 pages with a total of 72 rows pointing to the next level 70 pages with a total of 1,668 rows pointing to the next level 1,668 pages with a total of 40,000 rows pointing to the next level 40,000 pages containing all of the 1,000,000 rows of the index

1 page with 145 rows pointing to the next level 145 pages with a total of 40,003 rows pointing to the next level 40,003 pages containing all of the 1,000,000 rows of the index

Because the rows in the nonleaf level pages of the NoIncludedColumns index are substantially larger than those of the IncludedColumns index, more pages (and therefore more levels) are needed to create the balanced tree for the index. Because the NoIncludedColumns index is two levels (that is, 40 percent) deeper than the IncludedColumns index, each search through the NoIncludedColumns index needs two more page reads to get to the bottom of the index. This might not sound like much, but if the index is used for repeated searches, such as for joins or very frequent queries, the extra levels cause performance degradation. In Table 6-8, three example queries are shown that join a table called Test.OtherTable with the Test.IncludedColumnsTest table using different indexes. Note that the index hints [WITH(INDEX)] are used only to force SQL Server to use the specified index instead of the optimal index (which would be the IncludedColumns index). A new index named NotCovered is added to show the performance of a nonclustered index that does not cover the query. The following script defines additional objects and indexes required by the example:

-- Create the NotCovered index. CREATE NONCLUSTERED INDEX NotCovered ON Test.IncludedColumnsTest (Col1);

-- Create and populate the Test.OtherTable table. CREATE TABLE Test.OtherTable ( PKCol INT IDENTITY NOT NULL PRIMARY KEY ,Col1 INT NOT NULL );

INSERT Test.OtherTable (Col1) SELECT Col1 FROM Test.IncludedColumnsTest;

SELECT o.PKCol, i.Col2 FROM Test.OtherTable AS o INNER JOIN Test.IncludedColumnsTest AS i WITH(INDEX(IncludedColumns)) ON o.Col1 = i.Col1 WHERE o.PKCol BETWEEN 1 AND 10000;

Query 1 index: IncludedColumns The execution plan is shown in Figure 6-5.

SELECT o.PKCol, i.Col2 FROM Test.OtherTable AS o INNER JOIN Test.IncludedColumnsTest AS i WITH(INDEX(NoIncludedColumns)) ON o.Col1 = i.Col1 WHERE o.PKCol BETWEEN 1 AND 10000;

Query 2 index: NoIncludedColumns The execution plan is shown in Figure 6-6.

Query 3 index: NotCovered The execution plan is shown in Figure 6-7.

SELECT o.PKCol, i.Col2 FROM Test.OtherTable AS o INNER JOIN Test.IncludedColumnsTest AS i WITH(INDEX(NotCovered)) ON o.Col1 = i.Col1 WHERE o.PKCol BETWEEN 1 AND 10000;

Query 1, with the IncludedColumns index, is the best-performing query, with 32,726 page reads. Query 2, with the NoIncludedColumns index, used 53,994 page reads. As you can see, the difference in the number of page reads between the two indexes is roughly the same as the difference in index levels (40 percent). Query 3, with the NotCovered index, is the worst-performing query with 62,900 page reads because of the extra reads necessary to fetch the data that was not found in the index from the table. (Note the extra Nested Loops Join operator in the execution plan of Query 3.)