Create and Modify SQL Server Indexes Properly

Indexes are critical to smooth data retrieval and manipulation in SQL Server. They are the way foreign keys maintain relationships among themselves and the way DB pros enforce order on data. The system decides how and where an index is built by following the database practitioners directions to address the following questions:

• Is the index the result of a constraint?
• Is the index clustered or not?
• If it is a non clustered index, should it be on a different file group?
• If it is clustered, what fill factor and padding should be used?
• Is the index unique or not?
• Is the index in ascending or descending order?
• Is the index built on a single column, or more than one column?

A look at the column and table constraint language from the create table statement reveals placeholders that implement these decisions:

< column_constraint > ::= [ CONSTRAINT constraint_name ] 
    { [ NULL | NOT NULL ] 
        | [ { PRIMARY KEY | UNIQUE } 
            [ CLUSTERED | NONCLUSTERED ] 
            [ WITH FILLFACTOR = fillfactor ] 
            [ON {filegroup | DEFAULT} ] ]

< table_constraint > ::= [ CONSTRAINT constraint_name ] 
    { [ { PRIMARY KEY | UNIQUE } 
        [ CLUSTERED | NONCLUSTERED ] 
        { ( column [ ASC | DESC ] [ ,...n ] ) } 
        [ WITH FILLFACTOR = fillfactor ] 
        [ ON { filegroup | DEFAULT } ] 

The difference between the first and second forms of the command pertain to the number of columns in the index. The first form is for a single column, while the table level constraint allows you to include multiple columns.

In this 10-Minute Solution, you will learn how to determine the state of your indexes and understand if it should be changed. You also will learn how to vary the construction and location of your system's indexes and how these choices influence maintenance and performance. Properly applied, your choices can lead to quicker response times and more efficient space management for your data and its indexes.



How do I determine the state of my indexes? How do I know if they need my intervention? What methods can I apply to modify them, and how do I measure the effect of my changes?



Learn how to determine the state of your indexes and understand whether that state should be changed. Start by identifying them using sp_help @tablename and sp_helpindex, and then evaluate the construction and location of your system's indexes and decide if its necessary to adjust their fill factors and padding. These modifications will impact your system.