vote buttons
1
2
beginner
0
intermediate
0
advanced
Briefly explain all the different types of indexes in sql server.
11-Oct-2014 05:15 UTC
Andy
20

1 Answers

vote buttons
0

Composite index

An index that contains more than one column. In both SQL Server 2005 and 2008, you can include up to 16 columns in an index, as long as the index doesn’t exceed the 900-byte limit. Both clustered and nonclustered indexes can be composite indexes.


Unique Index

An index that ensures the uniqueness of each value in the indexed column.

A unique index is automatically created when you define a primary key or unique constraint:

Primary key: When you define a primary key constraint on one or more columns, SQL Server automatically creates a unique, clustered index if a clustered index does not already exist on the table or view. However, you can override the default behavior and define a unique, nonclustered index on the primary key.

Unique: When you define a unique constraint, SQL Server automatically creates a unique, nonclustered index. You can specify that a unique clustered index be created if a clustered index does not already exist on the table.


Covering index

A type of index that includes all the columns that are needed to process a particular query. For example, your query might retrieve the FirstName and LastName columns from a table, based on a value in the ContactID column. You can create a covering index that includes all three columns.



11-Oct-2014 05:20 UTC
Super Human
1164