What do Clustered and Non clustered index actually mean?

sql-server performance indexing clustered-index non-clustered-index
P.K picture P.K · Aug 9, 2009 · Viewed 810k times · Source

I have a limited exposure to DB and have only used DB as an application programmer. I want to know about Clustered and Non clustered indexes. I googled and what I found was :

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. 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.

What I found in SO was What are the differences between a clustered and a non-clustered index?.

Can someone explain this in plain English?

Answer

Shiraz Bhaiji picture Shiraz Bhaiji · Aug 9, 2009

With a clustered index the rows are stored physically on the disk in the same order as the index. Therefore, there can be only one clustered index.

With a non clustered index there is a second list that has pointers to the physical rows. You can have many non clustered indices, although each new index will increase the time it takes to write new records.

It is generally faster to read from a clustered index if you want to get back all the columns. You do not have to go first to the index and then to the table.

Writing to a table with a clustered index can be slower, if there is a need to rearrange the data.

Related questions

Why isn't index likely to do much if it's DATETIME or DATETIME2 because they include the time portion?

Comments to question "How to decrease response time of a simple select query?" tell: "What is the data type on LaunchDate? An index isn't likely to do much if it's DATETIME or DATETIME2 because they include the time portion – OMG …

Read More
Does Foreign Key improve query performance?

Suppose I have 2 tables, Products and ProductCategories. Both tables have relationship on CategoryId. And this is the query. SELECT p.ProductId, p.Name, c.CategoryId, c.Name AS Category FROM Products p INNER JOIN ProductCategories c ON p.CategoryId = c.…

Read More
SQL performance: Is there any performance hit using NVarchar(MAX) instead of NVarChar(200)

I am wondering if there is any disadvantage on defining a column of type nvarchar(max) instead of giving it a (smaller) maximum size. I read somewhere that if the column value has more than 4?KB the remaining data will …

Read More