How to Use the Microsoft SQL Index

Microsoft SQL Server 2008, as well as previous versions, use indexes to increase the speed of finding information in tables. Multiple types of indexes are available depending on your needs. Clustered indexes are based on the primary key value in the table and are ordered logically based on this data. Each table can have only one clustered index. Unique indexes prevent duplicate values from occurring in indexed columns. A non-clustered index logically orders the data in the table, ignoring the physical order of the data. Creating an index is fairly simple. Indexes are important to the proper functioning of a database.

Instructions

1. Open Microsoft SQL Server Management Studio or your desired query application.

2. Connect to the database using credentials with permissions to create an index.

3. Click "New Query" and switch to the correct database by typing and hitting "Enter:"

   USE [database name]

   GO

4. Determine the index type you are creating and the columns it will contain.

5. Type the query to create an index and hit "Enter:"

   CREATE [index type] INDEX [index name] ON [column list] [sort order]

   The index type can be unique, clustered or non-clustered. Name the index in such a manner that you will be able to identify the index easily in the future. The column list should include all columns you want in your index along with their sort order. For multiple columns, separate them with a comma.

6. Add any additional optional parameters in the index create statement.

7. Execute the query, and verify that the index is created.

References

• MSDN: CREATE INDEX (Transact-SQL)

Resources

• Simple Talk: SQL Server Index Basics