SQL Server Performance Forum – Threads Archive
hi
can anyone brief me about nonclustered index.what is the main difference between clustered and nonclustered index.? vamsi krishna
Books Online provide a clear explaination on this. Refer to BOL.
KH
The big diff between clustered and nonclustered is follows
1.Arranges data in phisical order and other not
2. Stores data at leaf level and other stores data at root level
both have B- Tree Structure for more refer BOL
SRJ2005
hey vamsi look into this
http://msdn2.microsoft.com/en-us/library/ms188783.aspx shashank sarwey jana ,sukino bhavanthu…..
Hi Reethu NOTE that SQL no longer physically sorts the data within the leaf pages of a clustered index Cheers
Twan
HI TAWAN
How it sorts using Clustered index
CLUSTERED
Creates an index in which the logical order of the key values determines the physical order of the corresponding rows in a table. The bottom, or leaf, level of the clustered index contains the actual data rows of the table. A table or view is allowed one clustered index at a time. For more information, see Clustered Index Structures. SRJ2005
quote:Originally posted by reethu
HI TAWAN
How it sorts using Clustered index
CLUSTERED
Creates an index in which the logical order of the key values determines the physical order of the corresponding rows in a table. The bottom, or leaf, level of the clustered index contains the actual data rows of the table. A table or view is allowed one clustered index at a time. For more information, see Clustered Index Structures.
How it sorts using Clustered index
CLUSTERED
Creates an index in which the logical order of the key values determines the physical order of the corresponding rows in a table. The bottom, or leaf, level of the clustered index contains the actual data rows of the table. A table or view is allowed one clustered index at a time. For more information, see Clustered Index Structures.
Here the "physical" is in a "logical" sense. I agree that the documentation is kind of vague about that. But its not safe to assume that the rows are actually "physically" ordered in the data pages. Have a look at these artciles which proves my point. http://blogs.conchango.com/davidportas/archive/2006/05/30/4007.aspx
http://www.sqlmag.com/Article/ArticleID/92886/sql_server_92886.html Roji. P. Thomas
Microsoft SQL Server MVP
http://toponewithties.blogspot.com
quote:Originally posted by Roji. P. Thomas
Here the "physical" is in a "logical" sense. I agree that the documentation is kind of vague about that. But its not safe to assume that the rows are actually "physically" ordered in the data pages. Have a look at these artciles which proves my point. http://blogs.conchango.com/davidportas/archive/2006/05/30/4007.aspx
http://www.sqlmag.com/Article/ArticleID/92886/sql_server_92886.html Roji. P. Thomas
Microsoft SQL Server MVP
http://toponewithties.blogspot.com
quote:Originally posted by reethu
HI TAWAN
How it sorts using Clustered index
CLUSTERED
Creates an index in which the logical order of the key values determines the physical order of the corresponding rows in a table. The bottom, or leaf, level of the clustered index contains the actual data rows of the table. A table or view is allowed one clustered index at a time. For more information, see Clustered Index Structures.
How it sorts using Clustered index
CLUSTERED
Creates an index in which the logical order of the key values determines the physical order of the corresponding rows in a table. The bottom, or leaf, level of the clustered index contains the actual data rows of the table. A table or view is allowed one clustered index at a time. For more information, see Clustered Index Structures.
Here the "physical" is in a "logical" sense. I agree that the documentation is kind of vague about that. But its not safe to assume that the rows are actually "physically" ordered in the data pages. Have a look at these artciles which proves my point. http://blogs.conchango.com/davidportas/archive/2006/05/30/4007.aspx
http://www.sqlmag.com/Article/ArticleID/92886/sql_server_92886.html Roji. P. Thomas
Microsoft SQL Server MVP
http://toponewithties.blogspot.com
From MSDN: "Because the leaf level of a clustered index and the data pages are the same by definition, creating a clustered index and using the …" I read that to mean that the pages are ordered but the rows inside the pages are not. What happens when a clustered index is added to a heap table where a single data page contains rows that end up in different intermediate nodes of the index? Are those rows removed from their original page and re-sorted so that every row in the page belongs to the same intermediate node? Or does SQL Server’s B-tree implementation allow the rows to remain in the same pages? Keith Payne
Technical Marketing Solutions
www.tms-us.com
quote:Originally posted by kpayne
I read that to mean that the pages are ordered but the rows inside the pages are not.
Not really. try the sample code in Itzik’s article. It clearly depicts out of order pages.
I read that to mean that the pages are ordered but the rows inside the pages are not.
quote:
What happens when a clustered index is added to a heap table where a single data page contains rows that end up in different intermediate nodes of the index? Are those rows removed from their original page and re-sorted so that every row in the page belongs to the same intermediate node? Or does SQL Server’s B-tree implementation allow the rows to remain in the same pages?
Note that when you are creating or rebuilding an index, SQL server will try to put the data in a contigues manner. The point is that it is not guaranteed to be contiguous.
Roji. P. ThomasWhat happens when a clustered index is added to a heap table where a single data page contains rows that end up in different intermediate nodes of the index? Are those rows removed from their original page and re-sorted so that every row in the page belongs to the same intermediate node? Or does SQL Server’s B-tree implementation allow the rows to remain in the same pages?
Microsoft SQL Server MVP
http://toponewithties.blogspot.com
]]>