Clustered index: Three-step SQL Server Index

through David Durante , 2013/01/25(first published: 2011/06/22)

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 front level in this staircase outlines the indexes of nonclustered indexes in general and special. Conclusion use the following key concepts for SQL Server index. When a request arrives in your database, a SELECT statement, or an INSERT, update, or DELETE statement, SQL Server has only three possible methods to access the claims referenced in the data table:

• Access to nonclustered indexes, avoiding access to tables. This can only be queried for all the data contained in the index, which is required by this table.
• Use the search key (s) to access the index, and then use the selected bookmark (s) to access a single row of the table.
• Table rows for index and search requests are ignored.

This level first focuses on the third selection above the list; The search table. In turn, this will lead to our discussion of cluster indexing; A topic mentioned, but not covered, at level 2

Sample Database

Although we have discussed the first-level sample database, it is worth repeating. Throughout the staircase, we will use the example to illustrate the concept. These examples are based on Microsoft'sAdventureWorkssample database. We specialize in sales orders. The five tables will give us a good transactional and non-transactional data;Customer,Sales Staff,Product,SalesOrderHeader,SalesOrderDetail. To stay focused, we use a subset of the columns. BecauseAdventureWorksNormalization, sales personnel information is decomposed into three tables:Sales Staff,Employeesand theContact.

the whole staircase. We use the following two terms, which refer to one line of order swaps: "Line items" and "Order Details". The former is a more common business term; The latter's name appears in a AdventureWorks table.

Clustered Index

Let's ask the following question: How much work does it take to find a row in a table (s) If you don't use a nonclustered index? Does the search for the requested row table mean that each row in an unordered table is scanned? Permanently or the rows of the SQL Server sequence table so that it can quickly access their search keys, like quick access to the entry for a nonclustered index search key? The answer depends on whether you instruct a clustered index table to be created on SQL Server.

The nonclustered index is a separate object that occupies its own space, and the clustered index is the same as the table. By creating a clustered index, you instruct SQL Server to sort the row index key sequence of the table, preserving the modification of the sequence data in the future. The coming level will look at the generated internal data structures to accomplish this. But now, think of a clustered index sort table. Given the continuous index key value, SQL Server can quickly access this line; And can be done in the row order of the table.

for demonstration purposes, we create two copies of our sample tables, SalesOrderDetail ; one without index, one clustered index. About the key columns of the index, our designers make the same choice AdventureWorks database:SalesOrderID/ Salesorderdetailid. A copy of the code in Listing 1 SalesOrderDetail the table. We can rerun this code at any time, and we want to start with a "white paper".

Understanding the basic clustered index

the clustered index key can be any column that you select, and it does not need to be based on a primary key. In our case, most importantly, the key to the leftmost column is a foreign key,SalesOrderID value. Therefore, the SalesOrderDetail table appears consecutively in all line items of the sales order .

Remember these additional points to the SQL Server cluster index:

• Because of the row of the Entry table of the clustered index, there is no collection value in the cluster index entry. When SQL Server is already on a line, it does not need a message to tell it where to find this line.
• The clustered index always overwrites the query. The index of each column of the table from the index and the same table.
• There is a clustered index on the table that does not affect you choose to create a nonclustered index table.
• Select Clustered index key column (s)

• You can have up to one clustered index per table. A table row can be in only one sequence. You need to decide what sequence, if any, is best for each table; If possible, creating a clustered index table becomes full of previous data. When making this decision, keep in mind that sequencing not only means ordering, it also means grouping; The items are grouped by sales order lines.

  This is why the designer AdventureWorks Database selection salesorderdetailid SalesOrderID sequence SalesOrderDetail table; The natural order of the line items.
  For example, If a user requests a line item for an order, they will typically require line items for all orders. Look at the form of a typical sales order tells us that the printed copy of the order always includes all line items. The essence of it is the sales order business cluster by the sales order line item. There may be occasional requests from the warehouse to see the product rather than the sales order line item; But most of the requests; Calculate the total value of each order, such as from sales person or customer, or program, print invoice, or query; All line items that require any sales order.

  User requirements, however, are not sure what is the best clustered index. The internal index that will be overwritten by the later levels of this series; Because some internal aspects of the index of a clustered index column can also affect your selection.

• Heap

  If there is no clustered index table, the table is called a heap. Each table is a heap or a clustered index. So, although each index of our regular state is divided into two types, clustered or nonclustered; It is also important to note that each table is divided into two types; It is a clustered index or a heap. Developers often say that a table "has" or "does not" have a clustered index, but it is more meaningful to say that the table is "yes" or "not" a clustered index.

  There is only one way for SQL Server to search for a heap in the lookup row (without the use of nonclustered indexes), which is started in the first row in the table and is made through the table until all the rows have been read. There is no sequence, no search key, no way to quickly navigate to a particular line.

  compare a clustered index and a bunch of

  performance evaluation of clustered indexes and a bunch of two copies of listing 1 SalesOrderDetail table. One is a heap version, on the other hand, we create a clustered index (salesorderid,salesorderdetailid) on the original table. The table does not have any nonclustered indexes.

  We will run the same three queries for each version of the table; One retrieves a row, the other retrieves a single order, all rows, and retrieves all the rows for a single product. We now each execute the SQL and the results table as shown in.

• Conclusion

  A clustered index is a sequence of sorted tables that is specified by SQL Server and maintained when you create an index. Any row in the table has quick access to the given key value. Any set of rows, in the index key sequence, also quickly accesses the given range key.

  There can be only one clustered index per table. The decision should be that the column index key column of the clustered index is the most important decision you will make to any table.

  At our level four we will take our focus from logic to physics, introduce pages and sections, and check the physical structure of the indicator.

Clustered index: Three-step SQL Server Index