30-08-2017, 03:57 PM
One of the most important routes for high performance in a SQL Server database is the index. Indexes accelerate the query process by providing quick access to rows in data tables, as well as how a book's index helps you find information quickly within that book. In this article, I provide an overview of SQL Server indexes and explain how they are defined in a database and how they can make the query process faster. Most of this information applies to indexes in SQL Server 2005 and 2008; the basic structure has changed little from one version to the next. In fact, much of the information also applies to SQL Server 2000.
Index structures
Indexes are created in columns in tables or views. The index provides a quick way to look for data based on the values within those columns. For example, if you create an index in the primary key and then look for a data row based on one of the primary key values, SQL Server first finds that value in the index, and then uses the index to quickly locate the entire index. data row. Without the index, a table scan would have to be performed to locate the row, which can have a significant effect on performance.
You can create indexes in most columns of a table or a view. Exceptions are primarily those columns configured with large object (LOB) data types, such as image, text, and varchar (max). You can also create indexes in XML columns, but those indexes are slightly different from the base index and are outside the scope of this article. Instead, I'll focus on the indexes that are most commonly implemented in a SQL Server database.
An index consists of a set of pages (index nodes) that are organized in a tree structure B. This structure is of a hierarchical nature, with the root node at the top of the hierarchy and leaf nodes at the bottom , as shown in Figure 1.
When a query is issued in an indexed column, the query engine starts at the root node and navigates down through the intermediate nodes, with each layer of the intermediate level more granular than the previous one. The query engine continues down through the index nodes until it reaches the leaf node. For example, if you are looking for the value 123 in an indexed column, the query engine would first look at the root level to determine which page to refer to at the upper intermediate level. In this example, the first page indicates the values 1-100 and the second page, the values 101-200, so the query engine would go to the second page of that level. The query engine would then determine to go to the third page at the next intermediate level. From there, the query engine would navigate to the leaf node for value 123. The leaf node would contain the entire data row or a pointer to that row, depending on whether the index is clustered or not clustered.
Index structures
Indexes are created in columns in tables or views. The index provides a quick way to look for data based on the values within those columns. For example, if you create an index in the primary key and then look for a data row based on one of the primary key values, SQL Server first finds that value in the index, and then uses the index to quickly locate the entire index. data row. Without the index, a table scan would have to be performed to locate the row, which can have a significant effect on performance.
You can create indexes in most columns of a table or a view. Exceptions are primarily those columns configured with large object (LOB) data types, such as image, text, and varchar (max). You can also create indexes in XML columns, but those indexes are slightly different from the base index and are outside the scope of this article. Instead, I'll focus on the indexes that are most commonly implemented in a SQL Server database.
An index consists of a set of pages (index nodes) that are organized in a tree structure B. This structure is of a hierarchical nature, with the root node at the top of the hierarchy and leaf nodes at the bottom , as shown in Figure 1.
When a query is issued in an indexed column, the query engine starts at the root node and navigates down through the intermediate nodes, with each layer of the intermediate level more granular than the previous one. The query engine continues down through the index nodes until it reaches the leaf node. For example, if you are looking for the value 123 in an indexed column, the query engine would first look at the root level to determine which page to refer to at the upper intermediate level. In this example, the first page indicates the values 1-100 and the second page, the values 101-200, so the query engine would go to the second page of that level. The query engine would then determine to go to the third page at the next intermediate level. From there, the query engine would navigate to the leaf node for value 123. The leaf node would contain the entire data row or a pointer to that row, depending on whether the index is clustered or not clustered.