Search content within the blog

Wednesday, April 22, 2009

Indexes in SqlServer

Relational databases like SQL Server use indexes to find data quickly when a query is processed.The SQL Server engine uses an index in much the same way a reader uses a book index.

Microsoft SQL Server supports two types of indexes:
Clustered indexes define the physical sorting of a database table’s rows in the storage media. For this reason, each database table may have only one clustered index. If a PRIMARY KEY constraint is created for a database table and no clustered index currently exists for that table, SQL Server automatically creates a clustered index on the primary key.

Non-clustered indexes are created outside of the database table and contain a sorted list of references to the table itself. SQL Server 2000 supports a maximum of 249 non-clustered indexes per table. However, it’s important to keep in mind that non-clustered indexes slow down the data modification and insertion process, so indexes should be kept to a minimum

You should consider creating non-clustered indexes on any columns that are frequently referenced in the WHERE clauses of SQL statements. Other good candidates are columns referenced by JOIN and GROUP BY operations.

You may wish to also consider creating non-clustered indexes that cover all of the columns used by certain frequently issued queries. These queries are referred to as “covered queries” and experience excellent performance gains.

1 clustered index per table (usually primary key)
249 non- clustered indexes per table are possible.

Also refer the following links :Link1

Link2

No comments:

Post a Comment