Clustered and non-clustered indexes in SQL Server

refer this link...
http://www.devtoolshed.com/content/clustered-index-vs-non-clustered-index-sql-server

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

When queries are run against a db, an index on that db basically helps in the way the data is sorted to process the query for faster and data retrievals are much faster when we have an index. Using indexes we can search as well as sort the record quickly.
Basically indexes in tables are concepts like page no in a book. It looks like B-Tree structures.
There is a single root page at the top of the tree, branching out into N number of pages at each intermediate level until it reaches the bottom, or leaf level, of the index.
The index tree is traversed by following pointers from the upper-level pages down through the lower-level pages.
In addition, each index level is a separate page chain. There may be many intermediate levels in an index.

There are 2 types of indexes in sql server. They are
1) Clustered index
2) Non Clustered index

1) Clustered index:
a) Clustered index is unique for any given table
b) Clustered index are stored in sequence order. Basically in a table there is only one clustered index.
c) Using this cluster index we can easily search the record.
d) The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index


Non Clustered Index:
a) Non Clustered index are stored in heap. Basically in a table we can have one or more than one clustered index.
b) The leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.

siva

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Refer below link
http://www.codeproject.com/Articles/173275/Clustered-and-Non-Clustered-Index-in-SQL-2005
http://www.allinterview.com/showanswers/8164.html

Regards,
Ravi

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Clustered index
A clustered index sorts and stores the data rows of the table or view in
order based on the clustered index key. The clustered index is
implemented as a B-tree index structure that supports fast retrieval of
the rows, based on their clustered index key values.

CREATE CLUSTERED INDEX MyIndex1 ON MyTable(Column1);

Nonclustered index
A nonclustered index can be defined on a table or view with a clustered
index or on a heap. Each index row in the nonclustered index contains the nonclustered key value and a row locator. This locator points to the data row in the clustered index or heap having the key value. The rows in the index are stored in the order of the index key values, but the data rows are not guaranteed to be in any particular order unless a clustered index is created on the table.

CREATE NONCLUSTERED INDEX MyIndex ON MyTable(Column1,Column2);

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users can see the index name but cannot see the indices themselves; they are just used to speed up queries. Effective indices are one of the best ways to improve performance of a database application.

An Index can give you improved query performance because a seek action occurs for retrieving records from your table in a query. A seek means you were able to locate record(s) without having to examine every row to locate those record(s).

A table scan occurs when there is no index available or when a poorly created index exists on the table for a query running against that table. In a table scan, SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a significant impact on performance.

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator