Đang chuẩn bị liên kết để tải về tài liệu:
Microsoft SQL Server 2008 R2 Unleashed- P86

Microsoft SQL Server 2008 R2 Unleashed- P86:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major releases.Microsoft SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 794 CHAPTER 25 Creating and Managing Indexes A nonclustered index is also structured as a B-tree. Figure 25.2 shows a simplified diagram of a nonclustered index defined on a first name column. Intermediate Page Root Page Amy George Sam Zelda Amy Bob Dave Elizabeth George Sam Leaf Page Amy Amy Anabelle Data Page Albert John . Alexis Amy . Amundsen Fred . Baker Joe . Best Elizabeth . Elizabeth Emma V Mason Emma . . Masonelli Irving . . Narin Anabelle . Naselle Amy . . 1 Neat Juanita Zelda I FIGURE 25.2 A simplified diagram of a nonclustered index. As with a clustered index in a nonclustered index all index key values are stored in the nonclustered index levels in sorted order based on the index key s . This sort order is typically different from the sort order of the table itself. The main difference between a nonclustered index and clustered index is that the leaf row of a nonclustered index is independent of the data rows in the table. The leaf level of a nonclustered index contains a row for every data row in the table along with a pointer to locate the data row. This pointer is either the clustered index key for the data row if the table has a clustered index on it or the data page ID and row ID of the data row if the table is stored as a heap structure that is if the table has no clustered index defined on it . To locate a data row via a nonclustered index SQL Server starts at the root node and navigates through the appropriate index pages in the intermediate levels of the index until it reaches the leaf page which should contain the index key for the desired data row. It then scans the keys on the leaf page until it locates the desired index key value. SQL Server then uses the pointer to the data row stored with the index key to retrieve the corresponding data row. Download from www.wowebook.com Creating Indexes 795 NOTE For a more detailed discussion of clustered tables versus heap tables that is tables with no clustered indexes and more detailed descriptions