Well I had assumed that since a “cluster” is two or more things squished together, that a “clustered index” was an index that was composed of two or more fields.
Did you see that word? “Assumed”, as my fellow DBA Eric says “makes an ASS out if U and ME”…ass-u-me assume get it…haha…yeah okay. *cough* SO ANYWAY…
I was wrong to assume, imagine that. But a clustered index actually orders the physical data, stored in “pages”. My best guess at the reason WHY the word “clustered” is used is that it takes all the pages that the data is stored on, reorders them, and puts them together for faster read access. But that’s just my guess.
Here is an example:
create table MyTestTable
(
HouseNumber int,
StreetName varchar(8000)
)insert into MyTestTable values (125, ‘Skyline’)
GO
insert into MyTestTable values (234, ‘Skyline’)
GO
insert into MyTestTable values (90, ‘Skyline’)
GO
insert into MyTestTable values (75, ‘Skyline’)
GO
insert into MyTestTable values (127, ‘Skyline’)
GO–BEFORE CLUSTERED INDEX
select * from MyTestTable
125 Skyline
234 Skyline
90 Skyline
75 Skyline
127 Skyline
Now let’s add a clustered index to this table:
CREATE CLUSTERED INDEX MyTestTable_HouseNumber
ON MyTestTable (HouseNumber)
GO–AFTER CLUSTERED INDEX
select * from MyTestTable
75 Skyline
90 Skyline
125 Skyline
127 Skyline
234 Skyline
Clustered indexes are better for queries using ranges of data. If a human being were told to get all books off the shelf whos author’s last names start with Ri-Ro if the books are in order you can grab them and come back. If they are out of order your going to have to go an index, look up where the books are located, visit each shelf and then grab the book requested. Or worse, if there is no index, you need to scan each book in the library taking the book you need down as you go. (in SQL they call this a table scan)
One Comment, Comment or Ping
Correct, a clustered index will physically order your data table.
If you have a “quiet” table, that does not receive a lot of updates on the main index, these are great for producing lightning fast results on that primary search.
If you are only appending data, and not inserting it, these are still good.
If you have inserts and deletes occurring on a regular basis, avoid clustered indexes.
November 29th, 2008
Reply to “What is a clustered index anyway?”