SQL Server Introduction Order: First level, Introduction to Indexes

SQL Server Introduction Order: First level, Introduction to Indexes

by David Durant, 2014/11/05 (first PUBLISHED:2011/02/17)

Original address:

Stairway to SQL Server Indexes:level 1, Introduction to Indexes

This article is part of the SQL Server cable Introduction order series (Stairway to SQL Server Indexes).

Indexes are the basis of database design, showing developers the intent to use a large number of database designers. Unfortunately, most of the time the index is in the performance problem, it was added after the event.

The first level describes the SQL Server index: A database object that allows SQL Server to query or modify the requested data in the shortest possible time, with minimal system resources for maximum performance gains. A good index would allow SQL Server to maximize concurrency, and one user's query would have little impact on other users ' queries. Ultimately, indexes provide an efficient way to database integrity, ensuring unique key values when a unique index is created. This level is just a brief introduction, including some theories and usage, and some physical details are left behind in the level described.

For database developers, a thorough understanding of the index is important when a request arrives from the client to SQL Server,sql Server with only two possible paths to access the requested data row:

1. Scans each row in the table that contains the data, from the first row to the last row, to check whether each row satisfies the requested condition.
2. If you have an available index, you can use the index to locate the requested data.

The first method is always available for SQL Server, and the second method is only feasible if you have designed an index that is available to the database, but the second one can lead to significant performance gains, which we will continue to cover next.

Because indexes need to be maintained (they take up physical space and they are bound to be synchronized with tables), they are not required by SQL Server. Some databases are completely non-indexed. They can cause performance degradation, which can lead to data integrity issues, but SQL Server allows it to exist.

But the bad things are not what we want. We all want database performance, data integrity, and keeping index maintenance to a minimum. This level will guide you towards this goal.

DB instance

Throughout the advanced series, we use examples to illustrate key concepts. These examples use the Microsoft AdventureWorks sample database. We mainly use the sales order department. Contains 5 sheets: Customer, Salesperson, Product, SalesOrderHeader, SalesOrderDetail. To keep the concentration of attention, we use a section of the column.

AdventureWorks design is very standard, the sales person's information in three sheets are: Salesperson,employee,contact. In some cases, we think of them as a table. Is the relationship between these tables.

The TSQL used at this level will be given later.

What is an index

Start our index learning journey through a small story, a very old, but proven technique that runs through this chapter, introducing the basic concepts of indexing.

You leave home to deal with some things. When you come back, get some news from your daughter's softball coach waiting for you to come back. Three girls: Tracy,rebecca, and Amy lost their hats. Can you buy them a hat and their parents will give it to you in the next game?

You know those girls, and you know their parents. But you don't know the size of their hats. There are three people in your town, and every family has some information you need. No problem, you'll call them and get the size of the hat. You open the directory index of the phone book while holding the phone.

You need to contact the first home is Hellen Meyer, you estimate Meyer should be in the middle of the name, you jump directly to the center of the phone book, but you found the page to the head of the "Kline-koerber" page, a few pages forward, and found the "Nagle-nyeong" , and then turned a few pages, found the "Maldonado-nagle". Realize that you are about to find, looking backwards, you find the "Meyer,helen" line, find the corresponding number. Get through the Meyer's home, got the information you want.

Repeat the process above and find the other two families and get the size of the other two hats.

You use an index that is similar to how SQL Server uses indexes. They have a lot of similarities, there are some differences between the phone book and the SQL Server index.

In fact, you just used one of the SQL Server two indexes (clustered and nonclustered), a nonclustered index. At this level we introduce a nonclustered index, the next level describes the clustered index, and an in-depth analysis of both indexes.

Nonclustered indexes

White Paper is similar to nonclustered indexes, and they are not organized in the data itself, just a mapping to help you access the data. The data itself is what we really need. The telephone company did not organize the inhabitants of the town into a meaningful queue, moving the house from one place to another, and facilitating the home of the girls of the same softball team, rather than using the surname of the inhabitants as they are now. Instead, he gives you a book that contains every inhabitant. Each row contains a key that allows you to access the phone to the resident.

Just like a phone on a white paper, a nonclustered index of SQL Server contains two parts of the content:

Query keys, such as last name-name-middle section, are called index keys in the vocabulary of SQL Server.

Label, which provides the same content, that is the phone number, and SQL Server directly points to the data row represented by the key.

In addition, a SQL Server nonclustered index contains some internal header information that may contain optional information. These are described at a later level and are not yet understood as the focus of the nonclustered index.

Just like a phone book, the SQL Server index maintains a query key, and a few small jumps will find the entry you want to access. Given a query key, SQL Server can quickly locate the portal. Unlike phone books, the index of SQL Server is dynamic. That is, each time you add a row, delete the same, or a column that contains a query key is modified, SQL Server updates the index.

Just like in the phone book two next to the family, not geographically adjacent to the same, in the nonclustered index next to the two entrance, is not the table next to the two rows of data. The first entry may be the last row in the table, and the second entry may be the first row in the table. In fact, an entry that does not want to be indexed is usually a meaningful sequence. The rows in the table are completely unordered.

When we create an index, SQL Server generates and accurately maintains the entry for each row in the extra table. You can create more than one nonclustered index in a single table.

The biggest difference is that SQL Server cannot use the phone. He will only use the information in the tag to navigate to the row in the corresponding table.

Create and benefit from nonclustered indexes

We end this level with a query of two sample databases to make sure that the sample database you are using is SQL Server 2005 's AdventureWorks database, or that it can be in the case of 2008. Each time we execute the same query, but the first time is before the index is created, the second time after the index is created. Every time, SQL Server tells us how much work is needed to get the data. We will query Helen Meyer line in the Contact table (probably in the middle part of the table). Initially, there is no index in the FirstName or LastName column, and to ensure proper execution, you can delete the index by using the following code.

1. IF EXISTS (SELECT * from sys.indexes
2. WHERE object_id = object_id (' person.contact ')
3. and name = ' FullName ')
4. DROP INDEX Person.Contact.FullName;

Turn on Io and time statistics

1. SET STATISTICS io on
2. SET STATISTICS time on
3. GO

Execute Query

1. SELECT *
2. From Person.Contact
3. WHERE FirstName = ' Helen '
4. and LastName = ' Meyer ';
5. GO

We will see the results of the execution, that is, Helen's message.

In the Message tab, we'll see

1. Table ' Contact '. Scan count 1, logical read 561 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
3. SQL Server Execution Time:
4. CPU time = 0 milliseconds, elapsed time = 9 milliseconds.

The information shows that this query generates logic IO 561 times and the processor takes 9 milliseconds. The content you display will differ from the processor.

To build a nonclustered index

1. CREATE nonclustered INDEX FullName
2. On Person.Contact
3. (LastName, FirstName);
4. GO

Execute Query again

1. SELECT *
2. From Person.Contact
3. WHERE FirstName = ' Helen '
4. and LastName = ' Meyer ';
5. GO

The information we saw this time became

1. Table ' Contact '. Scan count 1, logical read 4 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
3. SQL Server Execution Time:
4. CPU time = 0 milliseconds, elapsed time = 1 milliseconds.

Only 4 logical IO is required after indexing and less processor time.

Conclusion

Creating an appropriate index can greatly improve database performance. At the next level, we will understand the physical structure of the index. We will know why nonclustered indexes are good for queries. Other types of indexes, other benefits of indexes, index-related costs, monitoring and maintenance indexes, and some best practices are also described. The goal is to tell you the knowledge necessary to build the index.

Code download

• Level 1-introtoindexes_durant_code.sql
• Level 1-millionrowcontacttable.sql

SQL Server Introduction Order: First level, Introduction to Indexes