Microsoft SQL Server 2005 Developer’s Guide- P3:This book is the successor to the SQL Server 2000 Developer’s Guide, which was extremely successful thanks to all of the supportive SQL Server developers who bought that edition of the book. Our first thanks go to all of the people who encouraged us to write another book about Microsoft’s incredible new relational database server: SQL Server 2005. | Chapter 2 Developing with T-SQL 39 NONCLUSTERED Indexes A nonclustered index on a table or view is an index where the order of the index does not depend on the physical order of the data rows. In other words the columns do not have to be next to each other to make up the index. You can create up to 249 nonclustered indexes for each table in your database. NONCLUSTERED is the default mode when no keyword is specified in the CREATE INDEX statement. Included Columns in Indexes In some cases you may find that you are frequently querying a column in a table that is not a key column. In previous versions of SQL Server you would generally create an indexed view to handle this situation. However one of the restrictions to using an indexed view is that the index must be unique. SQL Server 2005 resolves this by allowing the inclusion of nonkey columns in a nonclustered index. This allows the query optimizer to locate all the required information from an index scan the table or clustered index need not be accessed. SQL Server 2005 allows up to 1023 columns to be included as nonkey columns. The following shows an example of creating a nonclustered index including nonkey columns CREATE NONCLUSTERED INDEX IdxTerritoryID_Date ON TerritoryID INCLUDE ModifiedDate CLUSTERED Indexes A clustered index has the index order the same as the physical order of the rows and the table data is stored with the index. If you regularly access rows in your table in a particular order a clustered index can significantly improve the speed of your queries. SQL Server allows you to create only one clustered index on each table. The following code shows creating a clustered index on the Sales .SalesPerson table CREATE CLUSTERED INDEX IdxPersonTerr ON SalesPersonID TerritoryID UNIQUE Indexes You can create a unique index on a column to guarantee that the data in the column will not be duplicated on an Insert operation. The database engine checks for duplicate values .
