INDEXING CONSIDERATIONS - A Developer's Guide to Data Modeling for SQL Server - page 237

CREATE NONCLUSTERED INDEX idx_Customer_LastName_FirstName
ON Customer (LastName ASC, FirstName ASC)
WITH (FILLFACTOR = 70,
SORT_IN_TEMPDB = ON,
ONLINE = ON)
N IndexFileGroup
This statement creates our index on the Customer table, sorting by last
name in ascending alphabetical order and then by first name in ascending
alphabetical order. The fillfactor, which specifies the amount of page space
to fill with the index information, is set to 70. This means that for all the
index pages created to hold the index data, each page will be filled no more
than 70 percent. This specification allows for some growth in the pages. By
doing this, we leave room for the insertion of rows that will fall in the mid-
dle of the ranges of each page. It lowers the overhead of maintaining the
index, because we should have fewer pages being rearranged to handle the
new rows as they come in.
We then tell SQL Server to do all the sorting of the data for the new
index in the tempdb. This moves the processing of the data, which requires
temporary physical storage, to the temp database, keeping it out of the ac-
tual user database that the index is being created in. Then we tell it to leave
the underlying tables online during the index creation process. By default,
the underlying tables would be offline, preventing anyone from reading or
writing to the tables while the index was being created. Note that this op-
tion is available only in SQL Server 2008 Enterprise Edition, so don't try it
otherwise. Finally, we specify the filegroup on which to place this index.
Filegroups
Filegroups are a method of storing database data files in a separated fash-
ion. By default, every new SQL Server database has a single filegroup,
called Primary, where the first data file lives. Users can create additional
files and filegroups and create objects in those files and filegroups. We've
specified that there be a user-defined filegroup called IndexFileGroup and
that we want the index created there (and not in the same filegroup as the
underlying table).
One of the primary reasons to do something like this is to both man-
age disk space and improve performance. If we separate our indexes from
our source tables, we can add disk spindles to satisfy the query, thereby not
tying up the same set of spindles to search for the data and retrieve the