March 19, 2007 at 7:19 pm
So are you saying that if you have a clustered index - all other indexes store their 'pointers'? in terms of the clustered index?
How is the info stored without a clustered index that makes it slower. I didn't think it would do a full column search or it would obviate the point of the index.
We have many indexes on our tables on columns that get searched intensively and speed it up heaps - just no clustered.
Cheers,
michael.
March 20, 2007 at 10:04 am
It won't always do a full column search, but the odds are a lot likelier that it will decide that a full column scan is faster than using the index, especially if selectivity is low. If you build a table with quite a bit of data in it with a nonclustered index on a non-key column, it may use the index. If you then add a clustered index on a key column, you will see that often SQL Server will use the clustered index over the nonclustered index.
If no clustered index exists, the nonclustered index stores a pointer to a value called a Row Identifier which is a combination of the file identifier, page number in the specified file, and row number on the specified page.
If it has a clustered index, the nonclustered index stores a pointer to the clustered value.
When looking for records that point to a clustered index, SQL Server performs a "GetNext" to get all records. So if the query had 3 rows returned with clustered values of 23, 875, and 10973, It will seek the record with the clustered value of 23, then it will seek forward (GetNext) to value 875 and then seek forward to value 10973.
Without a clustered index, it will find the record based on the Row Identifier (RID) using a "Nested Loop Join". So to find the same three rows, it will find the specified file then the specified page then the specified row of the first record. Then it will repeat the find process for the other two records.
For a recordset of three rows, the difference won't likely be noticeable. The larger the data set and the result set, the bigger the difference will be.
One way to think of it would be imagine a 10,000 page book with an index showing which pages every word in the book appears on. You can think of a clustered index as the book having each page numbered sequentially 1 to 10,000 and a heap (no clustered index) as the page numbers start over at 1 in each chapter.
Which is going to be faster for you to find pages that a specific word exists on?
If you have 3 page numbers for the pages numbered 1 to 10,000, you can just flip through to find the three pages.
If you have 3 pairings of chapter and page number, not in order, you'll have to first find each chapter and then find each page number.
I think that example explains well why the clustered index makes it faster. But you should also consider that the example "index" is only two layered (chapter & page) whereas a real index would be 3 layered (file, page, & row) making the nonclustered search more complex.
March 26, 2007 at 12:01 am
Robert,
Thanks for that. That was a good process level explanation - plain english and not abstracted. You should write the MS help files.
Cheers,
Mike.
March 26, 2007 at 10:23 am
LOL Thanks!! That would probably cure my insomnia!!
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply