Browse DevX
Sign up for e-mail newsletters from DevX


Create and Modify SQL Server Indexes Properly : Page 2

Learn how to determine the state of your indexes and understand whether that state should be changed. See how evaluating the construction and location of your system's indexes and deciding whether to adjust their fill factors and padding can improve the system's performance.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

How Indexes Are Made
Among the resources that shape indexes, the most elemental ones at your disposal are the statements that create the index and the two system stored procedures, sp_helpindex and sp_help @tablename. As an example, I asked the Object Browser in Query Analyzer to create a script for a table as a create. I then invoked sp_helpindex to get the details of the indexes on this table, Company. Figure 1 shows the outputs of the create table statement and sp_helpindex.

Figure 1: The Outputs of the Create Table Statement and of sp_helpindex

From the output, you can see that the system answered the lions share of the index-creation questions from the Introduction:

  • The index named PK_Company was created as the result of a constraint creating a primary key.
  • PK_Company is a clustered index, meaning that the table is in the order specified by the index key, in this case, the identity column CompanyID.
  • The index created has only one column, and since it was a primary key constraint, it also is unique and not null.
  • Because I didnt specify the direction of the index, it was created in the default of ascending order.
  • Since the clustered index was created on the Primary file group, the table and its index were created there. Clustered indexes are always created on the same file group as the data, because the leaf level or lowest level of a clustered index is the data itself. (In fact, you can move a table to a different file group by creating a clustered index for the table on a different file group. Both structures will move. Additionally, clustered indexes are effective for range searches because of their proximity to the data and because they sort the table in index order.)

The Unintended Consequences of Clustered Indexes
Be sure to analyze the retrieval requests for your system and design your indexes to accommodate most of those requests. You'll have to accept tradeoffs between the most frequently executed queries and the less frequently executed but more demanding queries.

The optimizer can chose to use an existing index based on its statistics. However, if the statistics indicate that reading the whole table would be cheaper, the optimizer will perform a table scan. The database can maintain index statistics with the Auto_Create_Statistics option set, or you can update them manually. Depending on how busy your server is, you may elect to perform updates at less busy times. As a side benefit, you also can keep statistics on non-indexed columns, which could help query execution.

Clustered and non-clustered indexes are interrelated by virtue of the clustering index key. You can promote efficiency in index maintenance by specifying clustered indexes first, because the non-clustered indexes use the clustered indexs pointers. If you specify a non-clustered index before a clustered index and then drop the clustered one, you are forcing the server to do double the work. When you specify a clustered index, you create the clustering key that succeeding secondary or non-clustered indexes use. If the clustered index comes after the non-clustered index, the server will drop and recreate the non-clustered indexes so it can inform them about where to find pages with the clustering key. To avoid this extra work, use the Drop Existing clause of the Create Index statement.

Another consequence of clustered indexes is you can influence the physical location of the data file by using the ON FILEGROUP clause. If you designate a different file group for the index, you can move the table to the named file group in the clustered index order. Because the clustered index in effect is the data, one less intermediate leaf level lies between the data and the index. Youll have to invoke the Alter Database command to create the new file group, then relocate your table to the new file group with a Create Index statements On Filegroup option.

If you elect this Drop Existing option after the fact (i.e., your table already exists and you have already created the new file group), you can use the create index command with the DROP EXISTING clause to prevent the double build and rebuild of the non-clustered indexes.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date