SQL Server Performance Forum – Threads Archive
Clustered Index
Hi , When I create a tbl let’s say Customer with three 3 columns with the "date" is primary key. CustomerDate (Key)
Name
Address 1. This will only contain one set of data with pre-sorted date or 2 set of records in
MDF files ? Thanks ,
Travis
Sorted on the clustered index value, date. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
If you do not specify the PRIMARY KEY as NONCLUSTERED, and there is no clustered index on the table, then the PK is created as a clustered index. As long as you are not adding all columns of the table into the clustered index, then you will have separate storage for the clustered index and separate storage for the table values themselves. Do not assume that data is automatically sorted by PK or clustered index! In queries, you must always include an ORDER BY clause to retrieve data in a predictable order. Other than that, a date column would seem to be a very poor choice for a primary key in an address table.
quote:Originally posted by Adriaan
Do not assume that data is automatically sorted by PK or clustered index! In queries, you must always include an ORDER BY clause to retrieve data in a predictable order.
Do not assume that data is automatically sorted by PK or clustered index! In queries, you must always include an ORDER BY clause to retrieve data in a predictable order.
If a table has a clustered index, data rows are stored on the data pages sorted by the clustered index key. There is a doubly-linked list connecting all the data pages belong to the table, if you following the doubly-linked list, all the data pages are also sorted by the clustered index key. In other words, logically, data rows are sorted based on the clustered index key value, physically, not sorted. ——————
Bug explorer/finder/seeker/locator
——————
quote:
In other words, logically, data rows are sorted based on the clustered index key value, physically, not sorted.
On rebuild (and reindex) SQL Server tries to be contiguous in the sorting, but there is no guarantee.
—In other words, logically, data rows are sorted based on the clustered index key value, physically, not sorted.
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
]]>