What is Clustured and non clustured indexing? How to index a table using sql server 2000 Enterprise manager? thanks in advance for your kind reply..
+2
A:
A clustered index means that the rows are physically ordered by the values in that index. A non-clustered index means that an index table is kept up to date that allows for quick seeking and sorting based upon value, but does not physically order the rows.
Only one clustered index can exist for a table, and if a primary key exists then that is the clustered index (in SQL Server).
Adam Robinson
2009-04-01 18:58:24
+2
A:
A clustered index defines how the actual table is stored. The rows are stored in a way to make searches on the fields in the clustered index fast. (They're not physically stored in the sort order of the index fields, but in a binary tree or something similiar.)
You can have only one clustered index per table. The clustered index contains all fields in the table, for example:
indexfield1 - indexfield2 - field2 - field3 - ....
A non-clustered index is like a separate table. It contains the fields in the index, and a reference to the fields in the table. For example:
secondindexfield1 - secondindexfield2 - reference to table row
When searching a non-clustered index, SQL server will find the value in the index, do a "bookmark lookup" to the table, and retrieve the other row fields from there. This is why non-clustered indexes perform slightly less wel then clustered indexes.
To add an index in SQL Server Management Studio, expand the table node in object view. Right click on "Indexes" and select "New Index".
Andomar
2009-04-01 19:04:09
+3
A:
In a clustered index on ID, the table rows are ordered by ID.
In a non-clustered index on ID, the references to table rows are ordered by ID.
We can compare a database to a CSV file:
ID,Value
-------
1,ReallyReallyLongValue1
3,ReallyReallyLongValue2
In a clustered table, when we insert a new row, we need to squeeze it between the existing rows:
ID,Value
-------
1,ReallyReallyLongValue1
2,ReallyReallyLongValue2
3,ReallyReallyLongValue3
, which is slow on insert but fast on retrieve.
In a non-clustered table, we keep a separate file index file which orders our rows:
Id,RowNumber
------------
1, 1
3, 2
When we insert the new row, we just append it to our main file and update the short index file:
ID,Value
-------
1,ReallyReallyLongValue1
3,ReallyReallyLongValue3
2,ReallyReallyLongValue2
Id,RowNumber
------------
1, 1
2, 3
3, 2
, which is fast on insert but less efficient on retrieve.
In real databases indexes use more efficient binary trees, but the principle remains the same.
Clustered indexes are faster on SELECT, non-clustered indexes are faster on INSERT / UPDATE / DELETE
Quassnoi
2009-04-01 19:05:24
I'm guessing SQL Server stores tables in a sparse B-Tree format. So insert on a clustered index might be more efficient, since you only have to update one place (instead of both the base table and the index)
Andomar
2009-04-01 19:17:46
Sure, but when it comes to balancing the tree, non-clustered indexes are much more efficient, especially if you have lots of data in your table.
Quassnoi
2009-04-01 19:19:40
Balancing the tree happens during a reindex, not insert/update/delete? Agree that reindex is faster for a non-clustered index
Andomar
2009-04-01 19:31:22
Balancing also occurs when the branches hit the tree range limits.
Quassnoi
2009-04-01 20:00:56
We've had big performance gains after reindexing an old table. Suggesting the tree just extends some branches indefinitely until you reindex. Not sure tho :)
Andomar
2009-04-01 21:59:20
A:
Clustered Index: Only one clustered index per table is allowed. If an index is clustered, it means that the table on which the clustered index is based is physically sorted according to that index. Think of the page numbers in an encyclopedia.
Non-clustered Index: Can have many non-clustered indexes per table. Think of the keyword index at the back of the book.