SQL Server Index Introduction: SQL Server index Level 1

author David Durant,2014/11/05(First edition:2011/02/17)

Original link:

http://www.sqlservercentral.com/articles/Stairway+Series/72284/

The series

This article is part of the "Stairway Series:Steps forSQL Server Indexing"

indexes are the basis of database design and tell developers to use the database with regard to the designer's intentions. Unfortunately, when performance problems arise, indexes are often added as an afterthought. The end here is a simple series of articles that should enable them to quickly make any database professional "Fast"

First Level introduction SQL Server Index: A Database object that enables SQL Server to locate and / or modify the requested data in the shortest time possible , using minimal system resources for maximum performance. A good index will also allow SQL Server to achieve maximum concurrency so that queries run by one user have little impact on queries run by others. Finally, indexes provide an effective way to enforce data integrity by guaranteeing the uniqueness of key values when creating a unique index. This level is an introduction ; It covers concepts and usages, but leaves physical details at a later level.

for database developers, an in-depth understanding of indexes is important: For some reason, database developers are most important: when when a SQL Server request arrives from a client,SQL Server has only two possible ways to access the requested row:

L. It can scan each row in a table that contains data, start with the first row and continue to the last row, checking each row to see if it meets the request criteria.

L. Or, if a useful index is available, you can use the index to find the requested data.

The first option is always available for SQL Server. The second option is available only when you instruct SQL Server to Create a useful index, but it can significantly improve performance, which we will explain in a later level.

because indexes have the overhead associated with them (they occupy space and must be kept synchronized with the table), SQL Server does not require them. You can have a database that has no indexes. It may be poorly executed and there will certainly be a data integrity issue, but SQL Server will allow it.

But that's not what we want. We all want the database to perform well, with data integrity, while minimizing the index overhead. This level will enable us to move towards this goal.

Sample Database

in the entire stairway microsoft AdventureWorks Sample database. We specialize in sales order functionality. Five tables will give us a good combination of trading and non-transactional data salesperson Span style= "font-family: Arial", product salesorderheader salesorderdetail

AdventureWorks has been normalized, so the salesperson information is divided into three tables. Salesperson, employee and contact information. For some examples, we'll use them as a table. Figure 1.1 shows the complete set of tables we will use and the relationships between them.

Figure 1.1: The AdventureWorks table to be used in this ladder

Attention:

The stair level displays all the TSQL code can be downloaded with the article (see the link at the bottom of this article)

What is an index?

We begin our index study with a short story, which uses a stale but proven technique that we will refer to in this article when we introduce the basic concepts of indexing.

you leave your house to run a few errands. When you come back, you will find that your daughter's softball coach is waiting for your message. Tracy, Rebecca and Aimi's three girls have lost the team's hat. You can ask the sports product store to swing and buy a hat for the girl. Their parents will reimburse you in the next game. You know girls, you know their parents. But you don't know the size of their hats. Somewhere in your town there are three homes, each with the information you need. No problem, just call your parents and get the hat size. You come into contact with your phone and then reach the index-the white page of the phone book. the first residence you need is Helen.• Meyer (Helen Meyer). Estimated "Meyer" will be located near the middle of the population, you jump to the middle of the white page, just find you in the title "Kline-koerber"on the page. You jump one step forward and arrive "Nagle-nyeong"page. A smaller jump that lets you in "Maldonado- Brasserie Nagel "page. Realize that you are now on the right page and you scan the page until you reach the "Meyer, Helen" line and get the phone number. Using your phone number, you can reachMeyerHome and get the information you need. you repeat this process two times, to the other two residences, and then get two hat sizes.

you have just used an index and have SQL Server uses the index in the same way that it is used ; because white pages and SQL Server There are a lot of similarities and differences between indexes.

In fact, the index you just used represents One of two SQL Server indexes supported by SQL Server : Clustered and non-clustered. White Pages best represent the concept of nonclustered indexes. Therefore, at this level, we introduce a nonclustered index. The subsequent levels introduce a clustered index and drill down into both types.

Nonclustered indexes

White pages are similar to nonclustered indexes because they are not the organization of the data itself ; It's a mechanism or map to help you access that data. The data itself is the actual person we need to contact. The telephone company did not arrange the town's dwelling in a meaningful order, moving the house from one place to another, so that all the girls of the same softball team lived together and the house had no surname of the inhabitants. Instead, it will give you a book, and each residence has an entrance. These entries are sorted by the search key of the white page ; Last name, first name, middle initials and street address. Each entry contains search keywords and data that allow you to enter the residence ; phone number.

like the entries in the whitelist, each entry in the SQL Server nonclustered index consists of two parts:

L. Search keywords, such as last name - first names - middle initials. In SQL Server terminology, this is the index key.

L. bookmarks have the same purpose as phone numbers, allowing SQL Server to navigate directly to the rows in the table corresponding to this index entry.

in addition, SQL Server nonclustered index entries have header information that is intended for internal use only, and may contain optional information. Both of these will be discussed at a later level. At this point, the understanding of non-clustered indexes is not important either.

like a white page, maintain a single in the Search keyword sequence SQL Server Index so that any particular entry can be accessed through a small set of "jumps". Given a search key,SQL Server can quickly reach the index entry for that key. Unlike white pages,SQL Server indexes are dynamic. That is, SQL Server updates the index every time you add, delete, or modify a search key column value .

The order of entries in a nonclustered index differs from the row order in the table, as the order of entries in the white page differs from the geography order of the dwelling in the town. The first entry in the index may be the index of the last row in the table, and the second entry in the index may be the second entry in the first row in the table. If the fact is different from the index, its index is always meaningful ; the rows of a table can be completely indeterminate.

When you create an index, SQL Server generates and maintains an entry in the index for each row in the underlying table (an exception to this general rule is encountered at a later level when we overwrite the filtered index). You can create multiple nonclustered indexes on a table, but you cannot have indexes that contain data from multiple tables.

and the biggest difference is: SQL Server cannot use the phone. It must use the information in the Bookmarks section of the index entry to navigate to the corresponding row of the table. this is necessary when SQL Server requires any information in the data row but not in the corresponding index entry, such as the size of the softball cap for Tracy Meyer. Therefore, for a better analogy, a white page entry contains a set of GPS coordinates instead of a phone number. Then, you use GPS coordinates to navigate to the house represented by the white page entry.

Create and benefit from nonclustered indexes

We end this level by querying our sample database two times. Make sure to use SQL Server 2005 dedicated version, sql Server 2008 You can use this version. adventureworks2008 But the first execution occurs before we create the index, and the second execution occurs after the index is created. Each time, sql Server Will tell us how much work has been done to retrieve the requested information. We will find " contact style= font-family " helen Meyer

IF EXISTS (SELECT * from Sys.indexeswhere object_id = object_id (' person.contact ')

and name = ' FullName ') DROP INDEX Person.Contact.FullName;

Listing 1.1-ensuring The index does not exist

Our task would require four SQL command batches.

The first command batch:

SET STATISTICS io on

SET STATISTICS Time Ongo

Listing 1.2- opening statistics

The above batch notification SQL Server We want our queries to return performance information as part of the output.

Second batch of commands:

SELECT *

From Person.Contact

WHERE FirstName = ' Helen '

and LastName = ' Meyer '; GO

Checklist 1.3- retrieving some data

This second batch retrieves the "Helenmaye" line:

584 Helen Meyer [email protected] 0-519-555-0112

Additionally, the following performance information is available:

Table ' Contact '. Scan count 1, logical reads 569. SQL Server Execution Times:cpu time = 3 Ms.

This output tells us that our request executes 569 logical IOandtakes about 3 milliseconds of processor time to complete. The value of the processor time may be different.

Third batch of commands:

CREATE nonclustered INDEX FullName

On Person.Contact

(LastName, FirstName); GO

listing 1.4- Creating a nonclustered index

This batch creates a nonclustered index on the first and last name columns of the Contact table. A composite index is an index that has multiple columns that determine the index row sequence.

Fourth batch of commands:

SELECT *

From Person.Contact

WHERE FirstName = ' Helen '

and LastName = ' Meyer '; GO

List 1.3(again)

This last batch is our original the re-execution of the SELECT statement. We get the same return row as before ; but the performance statistics are different this time .

Table ' Contact '. Scan count 1, logical reads 4. SQL Server execution Times:cpu time = 0 Ms.

This output tells us that our request requires only 4 logical IO; and a very small amount of processor time is required to retrieve the "Helenmaye" line.

Conclusion

creating a well-chosen index can greatly improve database performance. At the next level, we will begin to examine the physical structure of the index. We will look at why this nonclustered index is so advantageous to this query, and why this may not always be the case. Future levels will cover other types of indices, index additional benefits, index-related costs, monitoring and maintenance of your index, and best practices ; all of the goals are to provide you with the knowledge necessary to create the best indexing scheme for the tables in your own database.

Downloadable code

L Level1-introtoindexes_durant_code.sql

L Levellevel 1-millionrowcontacttable.sql

Resources:

Level 1-introtoindexes_durant_code.sql | level 1 -Millionrowcontacttable.sql

This article is part of the SQL Server index Ladder

SQL Server Index Introduction: SQL Server index Level 1