Explain SQL Server s index structure. in .NET framework

Creation PDF-417 2d barcode in .NET framework Explain SQL Server s index structure.

Explain SQL Server s index structure.
Make PDF-417 2d Barcode In Visual Studio .NET
Using Barcode maker for VS .NET Control to generate, create PDF-417 2d barcode image in .NET framework applications.
www.OnBarcode.com
PDF 417 Recognizer In .NET Framework
Using Barcode recognizer for Visual Studio .NET Control to read, scan read, scan image in .NET applications.
www.OnBarcode.com
Estimated lesson time: 20 minutes
Barcode Generator In .NET
Using Barcode creation for Visual Studio .NET Control to generate, create barcode image in Visual Studio .NET applications.
www.OnBarcode.com
Barcode Reader In VS .NET
Using Barcode decoder for .NET framework Control to read, scan read, scan image in .NET applications.
www.OnBarcode.com
Exploring B-Trees
Encode PDF-417 2d Barcode In C#.NET
Using Barcode maker for Visual Studio .NET Control to generate, create PDF417 image in VS .NET applications.
www.OnBarcode.com
Create PDF 417 In .NET Framework
Using Barcode encoder for ASP.NET Control to generate, create PDF417 image in ASP.NET applications.
www.OnBarcode.com
The structure that SQL Server uses to build and maintain indexes is called a Balanced tree, or B-tree. The illustration in Figure 4-1 shows an example of a B-tree.
Print PDF417 In Visual Basic .NET
Using Barcode creator for VS .NET Control to generate, create PDF 417 image in VS .NET applications.
www.OnBarcode.com
Encoding Code 128 In VS .NET
Using Barcode printer for VS .NET Control to generate, create Code 128 Code Set C image in VS .NET applications.
www.OnBarcode.com
Root
Draw Code39 In .NET Framework
Using Barcode maker for VS .NET Control to generate, create Code 3/9 image in VS .NET applications.
www.OnBarcode.com
DataMatrix Maker In Visual Studio .NET
Using Barcode creation for .NET Control to generate, create ECC200 image in .NET applications.
www.OnBarcode.com
Intermediate
Encoding UCC-128 In .NET Framework
Using Barcode encoder for .NET Control to generate, create GS1 128 image in Visual Studio .NET applications.
www.OnBarcode.com
Generating UPC-E Supplement 5 In Visual Studio .NET
Using Barcode maker for Visual Studio .NET Control to generate, create GTIN - 12 image in .NET framework applications.
www.OnBarcode.com
Leaf
Painting Data Matrix In .NET
Using Barcode generation for Reporting Service Control to generate, create Data Matrix ECC200 image in Reporting Service applications.
www.OnBarcode.com
Barcode Decoder In Java
Using Barcode scanner for Java Control to read, scan read, scan image in Java applications.
www.OnBarcode.com
Figure 4-1
Code 3/9 Reader In Visual Basic .NET
Using Barcode recognizer for .NET Control to read, scan read, scan image in VS .NET applications.
www.OnBarcode.com
UCC.EAN - 128 Creation In None
Using Barcode drawer for Font Control to generate, create GTIN - 128 image in Font applications.
www.OnBarcode.com
General index architecture
EAN-13 Printer In Java
Using Barcode creation for Eclipse BIRT Control to generate, create GTIN - 13 image in BIRT reports applications.
www.OnBarcode.com
Print ANSI/AIM Code 39 In VS .NET
Using Barcode generator for ASP.NET Control to generate, create Code 3/9 image in ASP.NET applications.
www.OnBarcode.com
A B-tree consists of a root node that contains a single page of data, zero or more intermediate levels containing additional pages, and a leaf level.
Draw 2D Barcode In Visual Studio .NET
Using Barcode printer for ASP.NET Control to generate, create Matrix Barcode image in ASP.NET applications.
www.OnBarcode.com
Generating PDF 417 In C#
Using Barcode creator for VS .NET Control to generate, create PDF 417 image in .NET applications.
www.OnBarcode.com
4
Creating Indexes
The leaf-level pages contain entries in sorted order that correspond to the data being indexed. The number of index rows on a page is determined by the storage space required by the columns defined in the index. For example, an index defined on a 4-byte integer column will have five times as many values per page as an index defined on a char(60) column that requires 60 bytes of storage per page. SQL Server creates the intermediate levels by taking the first entry on each leaf-level page and storing the entries in a page with a pointer to the leaf-level page. The root page is constructed in the same manner.
MORE INFO
Index internals
For a detailed explanation of the entries on an index page as well as how an index is constructed, see Inside Microsoft SQL Server 2005: The Storage Engine by Kalen Delaney (Microsoft Press, 2006) and Inside Microsoft SQL Server 2005: T-SQL Querying by Itzik Ben-Gan (Microsoft Press, 2006).
By constructing an index in this manner, SQL Server can search tables that have billions of rows of data just as quickly it can tables that have a few hundred rows of data. Let s look at the B-tree in Figure 4-2 to see how a query uses an index to quickly find data.
A, H O, T
Root
Intermediate
Leaf
A, B, C
D, E, F, G
H, I, J, K
L, M, N
O, P, Q, R
U, V, W, X, Y, Z
Figure 4-2
Building an index
If you were looking for the term SQL Server, the query would scan the root page. It would find the value O as well as the value T. Because S comes before T, the query knows that it needs to look on page O to find the data it needs. The query would then move to the intermediate-level page that entry O points to. Note that this single operation has immediately eliminated three-fourths of the possible pages by scanning a very small subset of values. The query would scan the intermediate-level page and
Lesson 1: Understanding Index Structure
find the value S. It would then jump to the page that this entry points to. At this point, the query has scanned exactly two pages in the index to find the data that was requested. Notice that no matter which letter you choose, locating the page that contains the words that start with that letter requires scanning exactly two pages. This behavior is why the index structure is called a B-tree. Every search performed always transits the same number of levels in the index and the same number of pages in the index to locate the piece of data you are interested in.
Inside Index Levels
The number of levels in an index, as well as the number of pages within each level of an index, is determined by simple mathematics. As previous chapters explained, a data page in SQL Server is 8,192 bytes in size and can store up to 8,060 bytes of actual user data. If you built an index on a char(60) column, each row in the table would require 60 bytes of storage. That also means 60 bytes of storage for each row within the index. If there are only 100 rows of data in the table, you would need 6,000 bytes of storage. Because all the entries would fit on a single page of data, the index would have a single page that would be the root page as well as the leaf page. In fact, you could store 134 rows in the table and still allocate only a single page to the index. As soon as you add the 135th row, all the entries can no longer fit on a single page, so SQL Server creates two additional pages. This operation creates an index with a root page and two leaf-level pages. The first leaf-level page contains the first half of the entries, the second leaf-level page contains the second half of the entries, and the root page contains two rows of data. This index does not need an intermediate level because the root page can contain all the values at the beginning of the leaf-level pages. At this point, a query needs to scan exactly two pages in the index to locate any row in the table. You can continue to add rows to the table without affecting the number of levels in the index until you reach 17,957 rows. At 17,956 rows, you have 134 leaf-level pages containing 134 entries each. The root page has 134 entries corresponding to the first row on each of the leaf-level pages. When you add the 17,957th row of data to the table, SQL Server needs to allocate another page to the index at the leaf level, but the root page cannot hold 135 entries because this would exceed the 8,060 bytes allowed per page. So SQL Server adds an intermediate level that contains two pages. The first page contains the initial entry for the first half of the leaf-level pages, and the second page
Copyright © OnBarcode.com . All rights reserved.