October 10, 2011 at 5:03 am
Hi
I have a doubt about Index which is;
Is Clustered Index sort and store data in physical order in table or Not:hehe:?
Ali
MCTS SQL Server2k8
October 10, 2011 at 5:07 am
Yes, sure Clustered indexes store data rows in a sorted way based on the key values, this is their main characteristic.
The consequence is that there can only be one clustered index per table, because the data rows themselves can only be sorted in one order.
October 10, 2011 at 5:07 am
There is no gurantee for this...
October 10, 2011 at 5:08 am
Not sure? I wonder why... MS says it is:
http://msdn.microsoft.com/en-us/library/ms177443.aspx
A primary key can be a nonclustered index, so the Primary key is no guarantee that rows are sorted after it unless you are sure it is a Clustered Index. Or I am wrong?
October 10, 2011 at 5:16 am
sqlzealot-81 (10/10/2011)
There is no gurantee for this...
:w00t: Could you elaborate, please?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 10, 2011 at 5:19 am
If the clustered index is not created with the UNIQUE property, the Database Engine automatically adds a 4-byte uniqueifier column to the table. When it is required, the Database Engine automatically adds a uniqueifier value to a row to make each key unique. This column and its values are used internally and cannot be seen or accessed by users.
October 10, 2011 at 5:26 am
Please have a look at this blog too...
http://sqlwithmanoj.wordpress.com/2011/03/02/clustered-vs-nonclustered-indexes-and-data-sorting/
October 10, 2011 at 5:30 am
Fabrizio Faleni (10/10/2011)
Yes, sure Clustered indexes store data rows in a sorted way based on the key values, this is their main characteristic.The consequence is that there can only be one clustered index per table, because the data rows themselves can only be sorted in one order.
I have doubt about Physical word, many where its exlplained that data sorted and stores on disk in physical order. see the link:
http://msdn.microsoft.com/en-us/library/aa933131(v=sql.80).aspx
and also see the opposition in attached page no93 of MS press book 'Self Passed TrainingKit Exam70-432.pdf'
What is true?
Ali
MCTS SQL Server2k8
October 10, 2011 at 5:33 am
OK, OK, I surrender!
I think I should stop believing MSDN statements like "Clustered indexes sort and store the data rows in the table based on their key values. There can only be one clustered index per table, because the data rows themselves can only be sorted in one order"... 😉
Let's say I'm not so sure anymore of the answer... needs more investigation...
October 10, 2011 at 8:01 am
sqlzealot-81 (10/10/2011)
If the clustered index is not created with the UNIQUE property, the Database Engine automatically adds a 4-byte uniqueifier column to the table. When it is required, the Database Engine automatically adds a uniqueifier value to a row to make each key unique. This column and its values are used internally and cannot be seen or accessed by users.
That still doesn't violate the rule. this just simply means that SQL Server will internally "break ties" when the clustered index columns generate duplicate values.
So - it WILL store the records in a LOGICAL order, where the contents of the pages will be ordered based on the clustered index.
It's a logical ordering because a. the pages are allocated on the fly (so the pages may be in different segments on the physical drives), AND b. because the INNER contents of a page may or may not be sorted (since you have to retrive the entire page anyway). Remember, per the "12 rules", the RDBMS must be able to operate independently of the disk storage, so the only level it can be concerned with is a logical one.
What isn't guaranteed is that retrieval of data might be ordered in any way unless you have an ORDER BY in the outer part of a statement. That doesn't have any bearing on the ordering aspect for storage purposes.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 10, 2011 at 10:12 am
Clustered index enforces the logical order of the data, not the physical.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 10, 2011 at 11:17 am
I think if you check table properties it does show index usage space on the disk meaning there by a physical storage for indexes.
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
October 10, 2011 at 12:00 pm
SQL_By_Chance (10/10/2011)
I think if you check table properties it does show index usage space on the disk meaning there by a physical storage for indexes.
sure. things get stored physically on disk (most of the time).
That said - the indexing isn't getting involved with what the "physical storage" actually is, how it's organized, where pages go. It could just as easily be a single disk (compressed or not), a raid stripe over several physical disks, a virtual RAM disk, several thousand slices from SAN LUNs, etc...
The pointers to the pages are also virtual (they point to ID's maintained by the storage function of SQL server, and again - those pointers have nothing to do with the physical storage).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 11, 2011 at 2:17 am
GilaMonster (10/10/2011)
Clustered index enforces the logical order of the data, not the physical.
hi gail;
I'm totaly confuse on this topics 'Physical or Logical' Please see the following link of MSDN:
http://msdn.microsoft.com/en-us/library/aa933131(v=sql.80).aspx
Can you please explain.
Thanks...
Ali
MCTS SQL Server2k8
October 11, 2011 at 5:22 am
You are confused because the article you refer to uses confusing terminology.
It starts "A clustered index determines the physical order of data in a table".
This means that if your table occupies 100 pages, the lowest key values will be in page 1 and the highest in page 100 in the database.
However it does not mean that
(1) The extents in which the pages sit will be alongside each other in SQL Server
or
(2) There might not be fragmentation or other characteristcs of the physical disk / SAN that put the data elsewhere at file system level.
By the way you are referring to a SQL 2000 article and this is a SQL 2008 forum.
Tim
.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply