SQL Server Index advanced: Level 1, index Overview

Original article address: StairwaytoSQLServerIndexes: Level1, IntroductiontoIndexes this article is part of the advanced series of SQL Server indexes (StairwaytoSQLServerIndexes. Indexes are designed for databases.

Original article address: StairwaytoSQLServerIndexes: Level1, IntroductiontoIndexes this article is part of the advanced series of SQL Server indexes (StairwaytoSQLServerIndexes. Indexes are designed for databases.

Original article address:

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

This article is part of the SQL Server Index advanced series (Stairway to SQL Server Indexes.

Indexing is the basis of database design and shows developers the intention of using a large number of database designers. Unfortunately, most of the time the index was added after the event of performance problems.

The first level introduces the index of SQL Server: it is a database object that enables SQL Server to query or modify the requested data in the shortest time, the minimum number of system resources is used to maximize performance. A good index will allow SQL Server to achieve maximum concurrency, and a user's query has almost no impact on other users' queries. In the end, indexes provide an efficient way for database integrity. When a unique index is created, the unique key value is ensured. This level is just an introduction, including some theories and usage, and some physical details are described at a later level.

For database developers, it is important to thoroughly understand the index. When a request arrives at SQL Server from the client, SQL Server only has two possible paths to access the requested data rows:

Scan each row in the table containing data, from the first row to the last row, and check whether each row meets the request conditions.

If an index is available, you can use the index to locate the requested data.

The first method is always available for SQL Server. The second method is feasible only when you have designed available indexes for the database, however, the second method can greatly improve the performance. Next we will continue to introduce it.

Because indexes need to be maintained (they occupy physical space and must be synchronized with tables), they are not required by SQL Server. Some databases have no indexes at all. They may cause performance degradation and may cause data integrity problems, but SQL Server allows it to exist.

However, these are not what we want. We all want the database to have excellent performance and complete data while minimizing index maintenance. This level will guide you towards this goal.

Database instance

Throughout the entire advanced series, we will use instances to describe the key concepts. These examples use Microsoft's AdventureWorks sample database. We mainly use the sales order department. There are five tables: Customer, SalesPerson, Product, SalesOrderHeader, and SalesOrderDetail. To maintain attention, we use partial columns.

The AdventureWorks design is very standard. The SalesPerson's information is in three tables: SalesPerson, Employee, and Contact. In some cases, we may regard them as a table. Is the relationship between these tables.

TSQL used at this level will be provided later.

What is an index?

Starting our index learning journey with a little story is a very old but proven technology. throughout this chapter, we will introduce the basic concepts of indexes.

You leave home to handle some things. When you get back, you get some news from the softball coach who is waiting for your daughter to come back. Three girls: Tracy, Rebecca, and Amy lost their hats. Can you buy them hats? In the next game, their parents will return them to you.

You know girls and their parents. But you don't know the size of their hats. There are three families in your town, each of which has some information you need. No problem. You will call them to get the size of the hat. You open the Directory Index of the phone book with one hand.

The first contact you need is Hellen Meyer. You estimate that Meyer should be in the middle of the name, and you will jump directly to the middle part of the phone book, however, you found a page with "Kline-Koerber" in the header, and looked forward several pages, and found "Nagle-Nyeong", website space, I flipped several pages forward and found "Maldonado-Nagle ". Realize that you are about to find it. Look back. You find the "Meyer, Helen" line and find the corresponding number. Get through Meyer's home and get the information you want.

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

You have used indexes. The way you use indexes is similar to that of SQL Server. They have many similarities and differences, such as telephone and SQL Server indexes.

In fact, you just used one of the two SQL Server indexes (clustered and non-clustered), non-clustered indexes. At this level, we will introduce non-clustered indexes. At the next level, we will introduce clustered indexes and in-depth analysis of these two indexes.

Non-clustered Index

White Paper is similar to non-clustered indexes. They are not organized by the data itself, but a ing that helps you access data. Data is what we really need. The telephone company did not organize the residents of the town into a meaningful queue, and moved the house from one place to another, making it easier for girls of the same softball team to have a home, instead of using the residents' surnames as they do now. Instead, he gave you a book containing every resident. Each line contains a key, allowing you to access the residents' phone number.

Like the phone number on the White Paper, the non-clustered index of SQL Server contains two parts:

The query key, such as the last name-middle part, is called the index key in the SQL Server vocabulary.

Tag to provide the same content, that is, the phone number. In SQL Server, direct to the data row represented by the key.

In addition, a non-clustered index of SQL Server also contains some internal header information, which may contain some optional information. These contents will be introduced at the following levels. Currently, they are not important to understand non-clustered indexes.

Just like the phone book, the index of SQL Server maintains a query key. After several small jumps, you will find the entry you want to access. Given a query key, SQL Server can quickly locate the entry. Unlike the phone book, the index of SQL Server is dynamic. That is to say, SQL Server updates the index every time a row is added, deleted, or a column containing the query key is modified.

Just like two neighboring families in the phone book, the two entrances in non-clustered indexes are not the two rows in the table. 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, if you don't want to index, the entry is usually a meaningful sequence. The rows in the table are completely out of order.

When we create an index, SQL Server will generate and precisely maintain the entries for each row in the additional table. You can create more than one non-clustered index in a table.

The biggest difference is that SQL Server cannot use telephones. Only the information in the tag can be used to navigate to the rows in the corresponding table.

Create and benefit from non-clustered Indexes