CODEWITHSUNDEEP
sqlsql-serverindexing
Aparan
Aparan

Reputation: 1273

Table without clustered indexes

I m trying to get an idea about indexes and began with this msdn article. It says,

When a table is stored as a heap, individual rows are identified by reference to a row identifier (RID) consisting of the file number, data page number, and slot on the page.

I have no idea what they mean by "file number, data page number, and slot on the page"

Can anyone clarify please?

Upvotes: 2

Views: 358

Answers (2)

Quassnoi
Quassnoi

Reputation: 425271

When using a clustered table, index records store the clustered key (logical identifier) of a table record.

Say, if you use a clustered primary table like this:

id   name
1    Jonh
2    Alice

and create an index on name, the index will store those data:

name   row_pointer
Alice  2
John   1

If your table were a heap, the index would store physical pointers to the table:

name   row_pointer
Alice  <file C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\db.mdf, page 1234, slot 2>
John   <file C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\db.mdf, page 1234, slot 1>

(this of course looks more compact in its real binary form)

File is self-explanatory, page is the 8K block in a file, slot is the record number in the page (the record TOC is stored in the header of each page)

Upvotes: 3

Ɖiamond ǤeezeƦ
Ɖiamond ǤeezeƦ

Reputation: 3331

A heap is a collection of data pages. A page is the smallest unit of data storage in Microsoft SQL Server and contains the data in the rows. A row can only reside in one page.

If you want lots more details on the internals of SQL Server, then perhaps read Microsoft® SQL Server® 2008 Internals by Kalen Delaney. I've found her books helpful in the past.

MSDN article: Tables and Index Data Structures Architecture

Upvotes: 1

Related Questions