Can a Table Really Have a Clustered Index?

Question

Can a Table Really Have a Clustered Index?

hakim.ali

SSCarpal Tunnel

Points: 4239
More actions
December 23, 2012 at 9:33 pm

#267936

Comments posted to this topic are about the item Can a Table Really Have a Clustered Index?
Hakim Ali
www.sqlzen.com

Viewing 15 posts - 1 through 15 (of 53 total)

You must be logged in to reply to this topic. Login to reply

Robert Davis One Orange Chip Points: 28027 More actions · Answer 1

Great definition and clarification of terms for beginners! You explained it very well.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

hakkie42 SSC Enthusiast Points: 151 More actions · Answer 2

Ok, so in other words you're saying a clustered index means that the physical data is stored in index order, so e.g. binary searches are possible.

A nonclustered index is closer to what you'd expect of the meaning of "index": a separate list with a pointer to the physical record.

That does clear up a lot...

Neha05 Default port Points: 1494 More actions · Answer 3

Neha05

Default port

Points: 1494

December 24, 2012 at 4:48 am

#1571254

Good explanation!

roger.plowman SSChampion Points: 10265 More actions · Answer 4

I always liked the library analogy.

The clustered index is how the books are shelved (usually by Dewey Decimal number), the non-clustered indexes are the index cards in the card catalogs.

Of course this probably tells you I'm old... (laughing)

Extending this, covering indexes are having the information you're looking for in the card catalog so you don't have to go looking on the shelf. 😀

patrickmcginnis59 SSCertifiable Points: 6483 More actions · Answer 5

Let's assume there is no clustered index on this table. This makes it a heap, meaning the rows are not physically written to disk in any particular order. When you search for a record in such a table via a select statement, the database must look at each record in the table. This is called a table scan. Not a very good way of doing things, because it is bad for performance.

If you have a nonclustered index on the table and the select statement had criteria as such that it could use the nonclustered index, does SQL still have to do a table scan? Seems to me it would do a lookup on the index then bookmark lookup for the matching rows in the heap. I do realise that bookmark lookups are more expensive than using a clustered index.

Mr. hoek Old Hand Points: 378 More actions · Answer 6

It would be nice that MSSQL tables would 'be' clustered or non clustered, but in fact they 'are' heaps with an optional 'clustered index' added to then.

Removing the clustered index, does not remove the data 😉

roger.plowman SSChampion Points: 10265 More actions · Answer 7

Mr. hoek (12/24/2012)
It would be nice that MSSQL tables would 'be' clustered or non clustered, but in fact they 'are' heaps with an optional 'clustered index' added to then.
Removing the clustered index, does not remove the data 😉

Actually, non-heaps *are* clustered indexes. Creating a clustered index physically rearranges the table into order by the index, and keeps it in that physical order as records are added and deleted. Removing a clustered index merely stops SQL server from keeping the table in order, but doesn't bother to rearrange the table randomly again. 🙂

dfortier SSC-Addicted Points: 411 More actions · Answer 8

Finally, an explanation that does not require mental gymnastics and reading between the lines. Thanks for taking the time.

Phil Parkin SSC Guru Points: 246956 More actions · Answer 9

Nice article, but rather than fields and records, I think you really mean columns and rows - there is a difference.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

annjunk Old Hand Points: 335 More actions · Answer 10

patrickmcginnis59 (12/24/2012)
Let's assume there is no clustered index on this table. This makes it a heap, meaning the rows are not physically written to disk in any particular order. When you search for a record in such a table via a select statement, the database must look at each record in the table. This is called a table scan. Not a very good way of doing things, because it is bad for performance.
If you have a nonclustered index on the table and the select statement had criteria as such that it could use the nonclustered index, does SQL still have to do a table scan? Seems to me it would do a lookup on the index then bookmark lookup for the matching rows in the heap. I do realise that bookmark lookups are more expensive than using a clustered index.

If a non-clustered index covers a query (meaning everything in your select and where are included somehow in the nc index) SQL will not do a table scan if it decides the index is the optimal way to look at the query.

For example, if I have a nc index built on the area code field and I have included the first name, last name, and phone number columns in the index. I then run the following query, there is a very good chance that SQL will choose to use my nc index and never touch the table on a heap.

SELECT Firstname, Lastname, phone FROM phonebook WHERE areacode = '412';

Mr. hoek Old Hand Points: 378 More actions · Answer 11

But why does the table allocation 'double' when adding a clustered index?

The only explanation I can think of, is that SQL server has the information 'twice' in the database:

- ones as pure 'table' data (all records/fields)

- ones as sorted 'index' data (all records/fields)

annjunk Old Hand Points: 335 More actions · Answer 12

I wonder if this over simplification will end up confusing people more in the end. Both clustered and non-clustered indexes have what are called non-leaf-level pages that can help the server in searches.

Like the person's library analogy earlier, when you walk into the library and know the ID on your book, you can start walking and reading the numbers on the ends of the shelves and only look for your book on the correct shelf. These labels on the ends of the book cases are like the non-leaf level pages of a clustered index.

This is also why a clustered index is about 105% of the size of your table. There is something more added to the table, not simply the reorganizing of the data.

hakim.ali SSCarpal Tunnel Points: 4239 More actions · Answer 13

Robert Davis (12/24/2012)
Great definition and clarification of terms for beginners! You explained it very well.

Thanks much.

Hakim Ali
www.sqlzen.com

hakim.ali SSCarpal Tunnel Points: 4239 More actions · Answer 14

hakkie42 (12/24/2012)
Ok, so in other words you're saying a clustered index means that the physical data is stored in index order, so e.g. binary searches are possible.
A nonclustered index is closer to what you'd expect of the meaning of "index": a separate list with a pointer to the physical record.
That does clear up a lot...

Yup... not very familiar with binary searches, but the rest sounds about right.

Hakim Ali
www.sqlzen.com