Questions about indexing in SQL

Question

As I understand it:

A Clustered Index orders the data physically by the index, so if you use Surname as a clustered index, when you do a select * you will get the surnames in alphabetical order.

A Non-clustered index is not physically reordering your database, but is creating a kind of lookup table that's ordered by the columns you choose.

It says in my book that you can have 16 columns for a clustered index. I'd have thought you'd only be able to choose 1 column though, as it's physically reordering your database by it? Or are multiple columns for if the first column contains duplicates?

Isn't it faster to always use non-clustered indexes, as SQL doesn't have to shuffle the data around?

Answer 1

A Clustered Index orders the data physically by the index, so if you use Surname as a clustered index, when you do a select * you will get the surnames in alphabetical order.

That's not necessarily true. An order is not guaranteed unless you supply ORDER BY for your query.

It says in my book that you can have 16 columns for a clustered index. I'd have thought you'd only be able to choose 1 column though, as it's physically reordering your database by it? Or are multiple columns for if the first column contains duplicates?

It sorts lexicographically: first on the first column, then on second (in case of a tie on the first column etc).

Isn't it faster to always use non-clustered indexes, as SQL doesn't have to shuffle the data around?

Clustered indexes do have some overhead on INSERT so it's sometimes advisable to make the tables that require fast DML unclustered (like, log tables etc).

However, clustered indexes allow much faster searching on the clustered key which results in faster joins on that key.

Answer 2

My understanding of clustered indexes - at least in relation to SQL Server 2005 - is that the ordering of the rows is not actually the physical ordering on the disk. Instead a linked list is maintained, giving a 'logical' ordering to the data. So while there's probably more of an overhead in changing a clustered index than a non-clustered index, it's not going to be as problematic as you imagine.

Answer 3

Typically you want your clustered index on a unique, narrow, increasing column(s). You rarely want to cluster on anything which is changing with updates.

Clustered indexes are not really indexes, just the data is stored in a tree instead of a heap.

Non-clustered indexes are generally narrower, so fit more rows per pages and are typically faster for reads (obviously the need to be covering to be useful).