SQL Server 2008 R2 : Creating and Managing Indexes - Creating Indexes (part 2) - Creating Indexes with SSMS

Creating Indexes with SSMS

SQL Server 2008 has many options for creating indexes within SSMS. You can create indexes within SSMS via the Database Engine Tuning Advisor, database diagrams, the Table Designer, and several places within the Object Explorer. The means available from the Object Explorer are the simplest to use and are the focus of this section.

Index creation in the Object Explorer is facilitated by the New Index screen. You can launch this screen from SMSS by expanding the database tree in the Object Explorer and navigating to the Indexes node of the table that you want to add the index to. Then you right-click the Indexes node and select New Index. A screen like the one shown in Figure 1 is displayed.

Figure 1. Using Object Explorer to create indexes.

The name and options that are populated in Figure 1 are based on the person index created in the previous T-SQL section. The LastName, FirstName, and MiddleName columns were selected and added as part of this new index by clicking the Add button, which displays a screen with all the columns in the table that are available for the index. You simply select the column(s) you want to include on the index. This populates the Index Key Columns grid on the default General page.

You can select other options for an index by changing the Select a Page options available on the top-left side of the New Index screen. The Options, Included Columns, Storage, Spatial, and Filter pages each provide a series of options that relate to the corresponding category and are utilized when creating the index.

Of particular interest is the Included Columns page. This page allows you to select columns that you want to include in the leaf-level pages of the index but don’t need as part of the index key. For example, you could consider using included columns if you have a critical query that often selects last name, first name, and address from a table but uses only the last name and first name as search arguments in the WHERE clause. This may be a situation in which you would want to consider the use of a covering index that places all the referenced columns from the query into a nonclustered index. In the case of our critical query, the address column can be added to the index as an included column. It is not included in the index key, but it is available in the leaf-level pages of the index so that the additional overhead of going to the data pages to retrieve the address is not needed.

The Spatial and Filter option pages are new to SQL Server 2008. The Spatial page can be used to create spatial indexes on a column that is defined as a spatial data type; that is either type geometry or geography. If your table contains a column of this data type, you can use the Index Type drop-down to change the index type to Spatial. After this is done, you can add a column that is defined as a spatial data type to the index. Finally, you can select the Spatial option page, as shown in Figure 2, that allows you to fully define a spatial index. 

Figure 2. Spatial Index options page.

The Filter option page allows you to define a filtering criterion to limit the rows that are included in the index. The page, shown in Figure 3, is relatively simple with a single input area that contains your filtering criterion. This criterion is basically the contents of a WHERE clause that is similar to what you would use in a query window to filter the rows in your result. The filter expression shown in Figure 3 was defined for an index on the PersonType column, which is found in the Person.Person table of the AdventureWorks2008 sample database. Many of the rows in this table have a PersonType value equal to 'IN' so a filtered index that does not include rows with this value will dramatically reduce the size of the index and make searches on values other than 'IN' relatively fast.

Figure 3. Filter Index options page.

After selecting all the options you want for your index via the New Index screen, you have several options for actually creating the index. You can script the index, schedule the index creation for a later time, or simply click OK to allow the New Index screen to add the index immediately. As mentioned earlier, it is a good idea to use this New Index screen to specify the index options, and then you can click the Script button to generate all the T-SQL statements needed to create the index. You can then save this script to a file to be used for generating a database build script or for maintaining a record of the indexes defined in a database.