|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Basic question.... on Index....
Why the name clustered is used in the clustered index? What is exactly
clustered here? Thanks, Jessy Hi,
Physical ordering of data inside the data page will be same as the Index key order. The pages in the data chain and the records on the pages are ordered on the value of the clustered index key. All inserts are made at the point the key value in the inserted row fits in the ordering sequence. See more details of cluetsred index in books online. Thanks Hari SQL Server MVP Show quoteHide quote "Jessy Martin" <Jessy_Smit***@hotmail.com> wrote in message news:%23Sy2IfsZFHA.3852@TK2MSFTNGP10.phx.gbl... > Why the name clustered is used in the clustered index? What is exactly > clustered here? > > Thanks, > > Jessy > > Clustered means the index keys and the table are clustered in the same page?
Jessy Show quoteHide quote "Hari Prasad" <hari_prasa***@hotmail.com> wrote in message news:%23xMrpksZFHA.2128@TK2MSFTNGP14.phx.gbl... > Hi, > > Physical ordering of data inside the data page will be same as the Index key > order. > > The pages in the data chain and the records on the pages are ordered on the > value of the clustered index key. All inserts are made at the point the key > value in the inserted row fits in the ordering sequence. > > See more details of cluetsred index in books online. > > > Thanks > Hari > SQL Server MVP > > > > "Jessy Martin" <Jessy_Smit***@hotmail.com> wrote in message > news:%23Sy2IfsZFHA.3852@TK2MSFTNGP10.phx.gbl... > > Why the name clustered is used in the clustered index? What is exactly > > clustered here? > > > > Thanks, > > > > Jessy > > > > > > Hi,
No. There are seperate Index and Data pages. read the below article. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_5h6b.asp Thanks Hari SQL Server MVP Show quoteHide quote "Jessy Martin" <Jessy_Smit***@hotmail.com> wrote in message news:%23rDFQpsZFHA.2124@TK2MSFTNGP14.phx.gbl... > Clustered means the index keys and the table are clustered in the same > page? > Jessy > > > "Hari Prasad" <hari_prasa***@hotmail.com> wrote in message > news:%23xMrpksZFHA.2128@TK2MSFTNGP14.phx.gbl... >> Hi, >> >> Physical ordering of data inside the data page will be same as the Index > key >> order. >> >> The pages in the data chain and the records on the pages are ordered on > the >> value of the clustered index key. All inserts are made at the point the > key >> value in the inserted row fits in the ordering sequence. >> >> See more details of cluetsred index in books online. >> >> >> Thanks >> Hari >> SQL Server MVP >> >> >> >> "Jessy Martin" <Jessy_Smit***@hotmail.com> wrote in message >> news:%23Sy2IfsZFHA.3852@TK2MSFTNGP10.phx.gbl... >> > Why the name clustered is used in the clustered index? What is exactly >> > clustered here? >> > >> > Thanks, >> > >> > Jessy >> > >> > >> >> > > Hari:
I'm not sure what you mean by this statement. Your statement is true for non-clustered indexes, but for the clustered index, the index key and the data are on the same pages and ARE the data pages. The clustered index does not use Index pages; they are the data pages, at the leaf level. Sincerely, Anthony Thomas -- "Hari Prasad" <hari_prasa***@hotmail.com> wrote in message No. There are seperate Index and Data pages. read the below article.news:%23Tcu7vsZFHA.3120@TK2MSFTNGP12.phx.gbl... Hi, http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_5h6b.asp Thanks Hari SQL Server MVP Show quoteHide quote "Jessy Martin" <Jessy_Smit***@hotmail.com> wrote in message news:%23rDFQpsZFHA.2124@TK2MSFTNGP14.phx.gbl... > Clustered means the index keys and the table are clustered in the same > page? > Jessy > > > "Hari Prasad" <hari_prasa***@hotmail.com> wrote in message > news:%23xMrpksZFHA.2128@TK2MSFTNGP14.phx.gbl... >> Hi, >> >> Physical ordering of data inside the data page will be same as the Index > key >> order. >> >> The pages in the data chain and the records on the pages are ordered on > the >> value of the clustered index key. All inserts are made at the point the > key >> value in the inserted row fits in the ordering sequence. >> >> See more details of cluetsred index in books online. >> >> >> Thanks >> Hari >> SQL Server MVP >> >> >> >> "Jessy Martin" <Jessy_Smit***@hotmail.com> wrote in message >> news:%23Sy2IfsZFHA.3852@TK2MSFTNGP10.phx.gbl... >> > Why the name clustered is used in the clustered index? What is exactly >> > clustered here? >> > >> > Thanks, >> > >> > Jessy >> > >> > >> >> > > "Jessy Martin" <Jessy_Smit***@hotmail.com> wrote in message As an example...news:#Sy2IfsZFHA.3852@TK2MSFTNGP10.phx.gbl... > Why the name clustered is used in the clustered index? What is exactly > clustered here? > > Thanks, > > Jessy > > A clustered index is sort of like a dictionary. The keys (dictionary words) are stored in sorted order. If you need to add a new row called Alligator, then it would be placed in the correct location in the dictionary. A non-clustered index is more like the index you find at the back of a book. The index at the back of the book is stored in sorted order, however, it has page numbers which tell you to go to page xx to find your word. When you insert a row here, the data will be appended to the end of the table (unless you have a clustered index on it), and the index at the back of the book will be updated appropriately. One thing to note is that when you have a nonclustered index on a table that has a clustered index, the "page numbers" don't actually point at the pages, but rather point at the clustered index key (dictionary word from above). When you do an insert, the nonclustered index has it's row modified and it then points to the clustered index key and the row is added in the dictionary order. HTH Rick Sawtell MCT, MCSD, MCDBA Excellent explanation. This is the sort of explanation I was looking
Thank you very much, Jessy Show quoteHide quote "Rick Sawtell" <r_sawt***@hotmail.com> wrote in message news:OYPXjysZFHA.1456@TK2MSFTNGP15.phx.gbl... > > "Jessy Martin" <Jessy_Smit***@hotmail.com> wrote in message > news:#Sy2IfsZFHA.3852@TK2MSFTNGP10.phx.gbl... > > Why the name clustered is used in the clustered index? What is exactly > > clustered here? > > > > Thanks, > > > > Jessy > > > > > > As an example... > > A clustered index is sort of like a dictionary. The keys (dictionary words) > are stored in sorted order. If you need to add a new row called Alligator, > then it would be placed in the correct location in the dictionary. > > A non-clustered index is more like the index you find at the back of a book. > The index at the back of the book is stored in sorted order, however, it has > page numbers which tell you to go to page xx to find your word. When you > insert a row here, the data will be appended to the end of the table (unless > you have a clustered index on it), and the index at the back of the book > will be updated appropriately. > > One thing to note is that when you have a nonclustered index on a table that > has a clustered index, the "page numbers" don't actually point at the pages, > but rather point at the clustered index key (dictionary word from above). > When you do an insert, the nonclustered index has it's row modified and it > then points to the clustered index key and the row is added in the > dictionary order. > > HTH > > Rick Sawtell > MCT, MCSD, MCDBA > > > One of the main benefits of clustered indexes is that you can get better
response for range queries or queries with duplicate keys, because the data is stored on the same or side by side pages. An IO of 1 page will return multiple rows that satisfy such a query, reducing IO time in a big way. -- Show quoteHide quoteWayne Snyder, MCDBA, SQL Server MVP Mariner, Charlotte, NC www.mariner-usa.com (Please respond only to the newsgroups.) I support the Professional Association of SQL Server (PASS) and it's community of SQL Server professionals. www.sqlpass.org "Jessy Martin" <Jessy_Smit***@hotmail.com> wrote in message news:%23Sy2IfsZFHA.3852@TK2MSFTNGP10.phx.gbl... > Why the name clustered is used in the clustered index? What is exactly > clustered here? > > Thanks, > > Jessy > >  
Other interesting topics
Problem with osql.exe -L
full drives full trans log version 7 alter table drop column and dbcc cleantable Simple Question: What's the best way to duplicate a DB under another name? Server: Msg 7391, Level 16, State 1, Procedure <OBJECT>, Line varchar vs nvarchar stored procedure with different criteria SQL7 2 DB files down to one??? Autonumber Removing Identity Property |
|||||||||||||||||||||||
