what is cluster index? and non cluster index?

cluster index create bydefault when you create a primary key on a table, rest are non-cluster index.....

Follow below link for more imfo.

http://www.sql-server-performance.com/2004/index-data-structures/

Thanks,
Rajni Shekhar

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

Hi,

Cluster Index:-
-----------------
a.A clustered index is a special type of index that reorders the way records in the table are physically stored.
Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

b.Clustered indexes sort and store the data rows in the table based on their key values, a table can have 1 clustered index.

Non-Cluster Index
------------------
a. A nonclustered index is a special type of index in which the logical order of the index does not match the physical
stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages.
Instead, the leaf nodes contain index rows which in turn point to the data.

b. We can create 249 non-cluster index on a table, we can create the non-cluster index on the columns which is being frequently queried by applications.

In general indexes are being used as the effective search technique.
http://social.msdn.microsoft.com/Forums/en-US/sqlsearch/thread/470aff5c-d989-4d38-a13f-f97368530e78/

Thanks
Chintan

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

You can create non-clustered index on the fields which are generally used in where clause to enhance performance.

Thanks,
Sanjay

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

you can go through this link also for more idea about it

http://stackoverflow.com/questions/1251636/what-do-clustered-and-non-clustered-index-actually-mean

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

Clustered
Clustered indexes sort and store the data rows in the table based on their key values. Because the data rows are stored in sorted order on the clustered index key, clustered indexes are efficient for finding rows. There can only be one clustered index per table, because the data rows themselves can only be sorted in one order. The data rows themselves form the lowest level of the clustered index.

The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. If a table has no clustered index, its data rows are stored in a heap.

Nonclustered
Nonclustered indexes have a structure completely separate from the data rows. The lowest rows of a nonclustered index contain the nonclustered index key values and each key value entry has pointers to the data rows containing the key value. The data rows are not stored in order based on the nonclustered key.

The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or are clustered. For a heap, a row locator is a pointer to the row. For a table with a clustered index, the row locator is the clustered index key.

The only time the rows in a table are stored in any specific sequence is when a clustered index is created on the table. The rows are then stored in sequence on the clustered index key. If a table only has nonclustered indexes, its data rows are stored in a unordered heap.

Indexes can be unique, which means no two rows can have the same value for the index key. Otherwise, the index is not unique and multiple rows can share the same key value.

Life is a Race
Thanks & Regards
By
Sabari Mahesh P M

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

Indexes are a common way to enhance database performance. An index allows the database server to find and retrieve specific rows much faster than it could do without an index. But indexes also add overhead to the database system as a whole, so they should be used sensibly.
2 types of Indexes
1.Clustered index
2.Nonclustured index

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. Clustered index is set on Primary key.Clusterindex physically arranges all the records.

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. Non-clustered index is generally set on the fields which are used in where clause.It logically arranges record.

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

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

Please refer this link too..
http://www.sql-server-performance.com/2004/index-data-structures/
http://www.codeproject.com/Articles/173275/Clustered-and-Non-Clustered-Index-in-SQL-2005
http://msdn.microsoft.com/en-us/library/ms175049%28v=sql.105%29.aspx

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

Hope you understand it.
Mark as Answer if its helpful to you...

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

Hi..

go through this link

http://www.codeproject.com/Articles/173275/Clustered-and-Non-Clustered-Index-in-SQL-2005

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