Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
java148
Yak Posting Veteran
63 Posts |
 Posted - 2012-01-12 : 10:03:33
|
I read these statements about nonclustered index . my question is, the pointer is pointing to data page , why not the row location ? if the value is 'Page1Row2', this way will be faster, isn't it ? If only page location, then it will continue to scan all rows in that page ?ThanksA nonclustered index is a separate lookup structure that stores index values in order, and with each index value, it stores a pointer to the data page containing the row with this index value |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-12 : 10:37:30
|
| The pointer in a nonclustered is to the row, not to the page. If the table is a heap (no clustered index), the pointer is the RID, an 8-byte combination of file number, page number and slot array (position on the page)If the table is a clustered index, the pointer is the clustered index key value. SQL uses that to seek down the clustered index to find the row. The quote you posted is incorrect.That said, SQL never reads less than a page at a time, so it will have to crack the page open and find the row on the page. There are a few algorithms it uses for that, but that's far beyond the scope of this.--Gail ShawSQL Server MVP |
 |
|
|
java148
Yak Posting Veteran
63 Posts |
Posted - 2012-01-12 : 11:36:22
|
Thanks, much more clear. quote:
If the table is a heap (no clustered index), the pointer is the RID
if it is a heap, that means no PK, no any index, why there is a pointer ?quote:
SQL never reads less than a page at a time
we have the row location, so we don't need to scan the whole page at a time, we can directly jump to that row. My understanding is correct ?Thanks |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-12 : 12:15:20
|
quote:
Originally posted by java148
Thanks, much more clear. quote:
If the table is a heap (no clustered index), the pointer is the RID
if it is a heap, that means no PK, no any index, why there is a pointer ?
As I said, a heap is a table without a clustered index. If can have a primary key, it can have any number of nonclustered indexes, it just doesn't have a clustered index.quote:
quote:
SQL never reads less than a page at a time
we have the row location, so we don't need to scan the whole page at a time, we can directly jump to that row. My understanding is correct ?
No. SQL never reads less than a page at a time.--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
nonclustered index