Drill-down nonclustered indexes: Stairs SQL Server Level two index

through David Durante , 2017/10/18(first published: 2014/11/26)

the series

This article is part of the stair series: Ladder index for SQL Server

The basis of the index database design tells the developer to use the intent of the Database Designer. Unfortunately, the index is often followed by a performance problem that arises. Finally here is a simple series of articles that should allow any database professional to quickly "accelerate"

The first-level index of SQL Server stairs describes SQL Server indexes, generally, and nonclustered indexes. As our first case study, we demonstrated the potential benefits of indexing when a single row was retrieved from a table. At this level, we continue our investigation of the nonclustered index; Check their contributions in case of good query performance, go beyond retrieving a single row from a table.

Will at most of our level, we introduce a few theories, study some of the indices internally to help explain this theory, and then execute some queries. These query indexes and performance report statistics are executed or not, so that we can see the impact of the metrics.

we will use a subset from the AdventureWorks database table, which we used for primary, centralized Contact form at this level. We're going to use an index FullName We're using for the first-level index to illustrate our point of view. To make sure we control the index on the contact table, we will make two tables of copies of the dbo pattern, only one of the build FullName indices. This will give us control of the environment: two tables: one with a single nonclustered index, one without any indexes.

IFEXISTS (SELECT*FromSys.tables WHEREobject_id=object_id(' dbo. Contacts_index '))DROP TABLEDbo. Contacts_index;GOIFEXISTS (SELECT*FromSys.tables WHEREobject_id = OBJECT_ID  (DROP TABLE dbo. Contacts_noindex; Goselect * into dbo. Contacts_index     from Person.Contactselect * into dbo. Contacts_noindex     from Person.Contact              

Listing 2.1: Copying people. Contact Form

a fragment of Contact Person shown in the table

ContactID FirstName middlename LastName emailaddress
                                    .
.
1288 Laura F Norman [email protected]
651 Michael Patten [email protected]
1652 Isabella R James [email protected]
1015 David R Campbell [email protected]
1379 Balagane Swaminath [email protected]
742 Steve Schmidt [email protected]
1743 Shannon C Guo [email protected]
1106 John Y Chen [email protected]
1470 Blaine dockter [email protected]
833 Clarence R. Tatman [email protected]
1834 Heather M Wu [email protected]
1197 Denise H Smith [email protected]
560 Jennifer J. Maxham [email protected]
1561 Ido Ben-sacha [email protected]
924 Becky R. Waters [email protected]

Nonclustered index entries

The following statement creates our Contacts_index FullName nonclustered index table.

FullName  contacts_index LastName);  

Create INDEX FullName in contacts_index( surname ,FirstName);      

Listing 2.2-Creating a nonclustered index

Remember that a nonclustered index stores the index key, and a bookmark is used to access the actual data in the table itself. You can think of bookmarks as a pointer. The future level will describe the bookmark, its form and use, in detail.

a fragment of FullName The index shows that the composition of the Surname and the FirstName as a key column, add a bookmark:

:---Search Key columns:bookmark

.

Russell Zachary =
Ruth Andy =
Ruth Andy =
Ryan David =
Ryan Justin =
Sabella Deanna =
Sackstede Lane =
Sackstede Lane =
Saddow Peter =
Sai Cindy =
Sai Kaitlin =
Sai Manuel =
Salah Tamer =
Salanki Ajay =
Salavaria Sharon =

Each entry contains the index key column and the collection value. Additionally, SQL Server nonclustered index entries have internal-use-only header information and may contain some optional data values. These will be at a later level; Nor is it important at this time for the basic understanding of nonclustered indexes.

Now, what we need to know is that the key value allows SQL Server to find the appropriate index entry (age); And the collection value of the entry allows SQL Server to access the rows in the corresponding data table.

Benefits of index entries in the sequence

The index key value (s) of the index entry sort, then SQL Server can quickly traverse the sequential entry. The scan sequence entry can start from the beginning of the exponent, exponent, exponent, or from any entry.

Therefore, if a request requires all contacts to start with the letter "S" (LastName like ' s% '), SQL Server can quickly navigate to the first "s" Entry ("Sabella, Deanna"), and then traverse the index, using the bookmark to access the row until the first "T" entry is reached; At this point it knows it retrieves all the "S" entries.

The above request executes faster if all of the selected column indexes. So, if we publish:

FirstNameLastName contact ' s% ';   

SQL Server can quickly navigate to the first "S" entry, then traverse the index entry, ignoring the bookmark and retrieving the data value directly from the index entry until it reaches the first "T" entry. In relational database terminology, the index "overrides" the query.

The benefits of any SQL operator from the sequencing data can be benefited from the index. This includes order by, groups, different alliances (alliances), and joins ....

For example, if you request the last name of a contact that requires a count, SQL Server can start counting at the first entry and index it. Each time the value of the surname changes, the SQL Server outputs the current count and starts a new count. With the previous request, this is an overlay query; SQL Server accesses the index, completely ignoring the table.

Note the key column for the importance of the order from left to right. Our index is very useful if a request requires everyone's last name "Ashton", but little or no help if the request is for everyone whose name is "Ashton".

test some sample queries

If you want to perform a test query, make sure that you run the script to create two versions of the new Contact table, dbo. Contacts_index and dbo. Contacts_noindex; Create and run a script First name, last name Index dbo. Contacts_index.

To verify the assertion in the previous subsections, we will be using the same performance statistics as we use Level 1 and run some queries; Have and no index.

set STATISTICS io ONSET STATISTICS time on      
 
 
 Sqlselect FirstName, LastName 
 from dbo. contacts --Execute with both Contacts_noindex and 
--Contacts_index 
 WHERE LastName like ' s% ' without index (21 Row (s) affected) 
 Table ' Contacts_noindex '. Scan count 1, logical reads 568.With Index (2130 row (s) affected) 
 Table ' Contacts_index '. Scan count 1, logical reads 14.Index Impactio reduced from 568 reads to reads. Commentsan index that covers the query was a good thing to has. Without an index, the entire table was scanned to find the rows. 
 the "2130 rows" statistic indicates that "S" are a popular initial letter for last names, occurring in ten percent of a LL contacts. 
 
 

test a non-covered query

Next, we modify our query request to the same row as before, but do not include the column index. The query execution information is given in table 2.2.

Drill-down nonclustered indexes: Stairs SQL Server Level two index