Creating Indexes with SSMS
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.
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
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