InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 19:47:29 GMT20http://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspx[quote][b]Peter Trast (3/20/2010)[/b][hr]Maybe if the data were not previously PARTITIONED, yes, but an assumption not in the question. The clustered index existed before as stated in the question so if both the clustered index and partition scheme were in place previously, data would not move.... anyone say different, please explain... please! :)Oh I don't know...maybe the MOVE TO clause of DROP INDEX was used? In general though, you are right.[quote]And, yes, you said that before but I have found it is best just to participate in the discussion without being terribly critical :) Point out specific errors, yes, but never generalize, as in using terms like "terrible", just hurts feelings. Everyone gets a chance to be incorrect now and then. After all, "it depends" is the number one phrase that I associate with SQL...[/quote]That's fine. I do not often use such words, but I felt it appropriate to do so here. Each to their own.Sat, 20 Mar 2010 22:17:47 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspx[quote][b]Paul White NZ (3/20/2010)[/b][hr][quote][b]Peter Trast (3/20/2010)[/b][hr]"Dropping and rebuilding a clustered index on a partition scheme may move data to other drives. " ?Really? How can that be? The idea of the partition is to locate data in a particular filegroup based on the data in a particular column, right? And the purpose of using the filegroups is to allow the partitioned data to be on different drives, for performance or organizational reasons, correct? So if the data has not changed, why would it be moved to another drive?If this is true, it really damages what I thought I understood about partition schemes... but that is why I come here, to learn these nuances :-)[/quote]I suppose it is just possible that the clustered index was not partitioned to begin with. Depends how you read it, I guess ;-)Terrible article. Did I mention that already? :-D[/quote]Maybe if the data were not previously PARTITIONED, yes, but an assumption not in the question :cool:The clustered index existed before as stated in the question so if both the clustered index and partition scheme were in place previously, data would not move.... anyone say different, please explain... please! :)And, yes, you said that before but I have found it is best just to participate in the discussion without being terribly critical :) Point out specific errors, yes, but never generalize, as in using terms like "terrible", just hurts feelings. Everyone gets a chance to be incorrect now and then. After all, "it depends" is the number one phrase that I associate with SQL...Sat, 20 Mar 2010 16:05:55 GMTPeter Trasthttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspx[quote][b]Peter Trast (3/20/2010)[/b][hr]"Dropping and rebuilding a clustered index on a partition scheme may move data to other drives. " ?Really? How can that be? The idea of the partition is to locate data in a particular filegroup based on the data in a particular column, right? And the purpose of using the filegroups is to allow the partitioned data to be on different drives, for performance or organizational reasons, correct? So if the data has not changed, why would it be moved to another drive?If this is true, it really damages what I thought I understood about partition schemes... but that is why I come here, to learn these nuances :-)[/quote]I suppose it is just possible that the clustered index was not partitioned to begin with. Depends how you read it, I guess ;-)Terrible article. Did I mention that already? :-DSat, 20 Mar 2010 15:05:12 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspx"Dropping and rebuilding a clustered index on a partition scheme may move data to other drives. " ?Really? How can that be? The idea of the partition is to locate data in a particular filegroup based on the data in a particular column, right? And the purpose of using the filegroups is to allow the partitioned data to be on different drives, for performance or organizational reasons, correct? So if the data has not changed, why would it be moved to another drive?If this is true, it really damages what I thought I understood about partition schemes... but that is why I come here, to learn these nuances :-)Sat, 20 Mar 2010 14:45:17 GMTPeter Trasthttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspxAwful. Just awful.Mon, 01 Feb 2010 05:38:11 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspx[quote][b]duda (12/21/2009)[/b][hr][quote][b]ChiragNS (12/9/2009)[/b][hr]"Dropping a rebuilding a clustered index on a partition scheme will move disk to other drives." - Very confusing.[/quote]I think you misunduerstood what the statement said but if you were to look at it again... "Dropping a rebuilding a clustered index on a partition scheme MAY move disk to other drives." This is not going to be something that will happen always but sometimes...[/quote]This was how the question was originally phrased - hence most of the thread.Mon, 21 Dec 2009 18:23:37 GMTRichBhttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspx[quote][b]ChiragNS (12/9/2009)[/b][hr]"Dropping a rebuilding a clustered index on a partition scheme will move disk to other drives." - Very confusing.[/quote]I think you misunduerstood what the statement said but if you were to look at it again... "Dropping a rebuilding a clustered index on a partition scheme MAY move disk to other drives." This is not going to be something that will happen always but sometimes...Mon, 21 Dec 2009 01:18:16 GMTBudaClihttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspxGlad to hear the system thinks for its authors...LOLFri, 18 Dec 2009 15:27:27 GMTmacrostarrphishhttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspx[quote][b]macrostarrphish (12/18/2009)[/b][hr]5.) Don't use multi-select if only one answer is to be selected, change answer set to radio buttons.[/quote]Actually... this is how it works by default. The person putting the question in indicates which answers are correct. If there is only one, it uses radio buttons. If there are more than one, it uses checkboxes... I've used this small tidbit of information to my advantage more than once. :-DChadFri, 18 Dec 2009 15:22:56 GMT Chad Crawfordhttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspxOK, so I didn't think the second statement was true, and apparantly it is...That isn't my problem with the question, my problem is (as stated before) authors don't know how to write good questions and don't know how to create the right answer sets. This should have been a radio button choice set with all of the above as the last answer. You should NEVER give multi-select answer set where all are the correct choice. The should be two qualifiers for multi-select:1.) there should be at least one false answer2.) there should be a qualifier, such as "choose three" (of four or five answers). Obviously choose three would give away the answer here, since there was only three choices. I am assuming the point of these excercises isn't to fool the reader but rather to educate, thus you are not trying to confuse or trick the user by not giving them a clue as to how many answers are you expecting them to select.Other things to keep in mind:3.) if you are going to use radios and two choices are "all of the above" and "none of the above," make sure "all of the above" is placed above "none of the above", as the reverse would make "all of the above" false by default.CORRECT WAY OF WRITING THIS ANSWER SETa.) true statementb.) true statementc.) true statementd.) all of the above <-- still truee.) none of the above <-- could be true, doesn't negate answer d.INCORRECT WAY OF WRITING THIS ANSWER SETa.) true statementb.) true statementc.) true statementd.) none of the above <-- could be true, however negates answer e.e.) all of the above <-- this can NEVER be true4.) True/false statements should try and avoid ALWAYS and NEVER, as there is most likely an exception to the rule. If using ALWAYS or NEVER, make sure it is a 100% of the time case, or the statement is bad/false. [NOTE: Generally speaking, when taking a test and you see ALWAYS or NEVER on a T/F question, you should pick false, unless you can make a 100% case qualifying the statement...]5.) Don't use multi-select if only one answer is to be selected, change answer set to radio buttons.Please, try and use this as a guide to writing better questions...this isn't everything we used to enforce our authors to create a good set of learning materials, but it is a start.I worked for an internal learning network for three years and we had very strict guidelines on how/what/when a properly worded question could go into our system. "Fluff" answer (answers that are obviously wrong) like "Donald Duck" when asking presidents of the US, are frowned upon as well, for education purposes. [Note: unless your point is to lighten the mood by making an obvious joke - we all need some humor in our lives]Thanks for listening...Fri, 18 Dec 2009 15:06:03 GMTmacrostarrphishhttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspxI thought that SQL Server maintained a "logical file", but the OS maintained the "physical file". If you start with a small mdf file that grows in small chunks on a heavily used disk, each time the file grows, it would grab a piece of disk that was available, but that new piece might not be physically contiguous with the last piece, and might actually be before any of the other parts of the file or intermixed with others (I'm thinking about disk fragmentation here). SQL Server would see one long continuous "logical file", but at the physical disk level, it would actually be spread all over the disk. Because of this, a clustered index would be logically ordered, but might not be physically ordered on the disk. I'm using the terms logical and physical file loosely here (not really sure what term is most appropriate to describe what I'm thinking).If it didn't work this way, every time your mdf file grew, it might have to rebuild some of your clustered indexes to make sure they were physically ordered on disk, right?I know my technical jargon might be a little off, but am I correct on the concept? I always thought that saying clustered indexes were "ordered on disk" was a good way to think about and understand the concept, but was not always completely technically true when you got down to the nuts-n-bolts.Thanks,ChadWed, 16 Dec 2009 10:25:28 GMT Chad Crawfordhttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspx[quote][b]Robert Frasca (12/10/2009)[/b][hr][b]Dropping and rebuilding a clustered index on a partition scheme may move data to other drives. [/b] The whole point of using a partition scheme is to distribute the data across multiple disks to improve performance by distributing the I/O as well as by creating logically and physically smaller partitions to search. Even if you don't use partitions, the best way to copy a table from one filegroup to another is to drop the clustered index and re-create it using a different file group. That has also been true for quite awhile.[/quote]No its not. The best way is to create with drop existing - not dropping and creating a new one. Many tools on the market take this approach - with the typical effect that they need to drop and recreate every foreign key that references the primary key that you are dropping and creating. Try that on a table with 30 rows but referenced by one with 4bn... Mon, 14 Dec 2009 10:53:37 GMTRichBhttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspxRobert is right, the quote is not quite the same as in the book. The following email will explain the difference ;-)--**********************************************************************************Hi BrandonOf course you can publish this note. I would suggest a minor edit to your last sentence:When the clustered index is created, it does in fact reorder the data physically on disk according to the cluster key, or as close to physical order as possible given the available space. After that, maintaining the clustered index is a logical operation. Rebuilding the index will again order the data physically. Reorganizing the index is an attempt to make the logical and the physical order match, without all the work of rebuilding the index. Best regards,Kalen Delaney, SQL Server MVPwww.SQLServerInternals.comThu, 10 Dec 2009 14:52:48 GMTBrandon Foresthttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspx[quote][b]Robert Frasca (12/10/2009)[/b][hr]I find it particularly amusing that some of you have the chutzpah to claim that Kalen Delaney is wrong. :-)[/quote]Far be it for me to question Kalen !!!! I just had never heard of the linked lists and still am not totally comfortable with the partitioning part. I still think that the partitioning function will put data right back where it had originally been ....Thu, 10 Dec 2009 11:09:02 GMTSource-NHhttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspx[quote][b]Mauve (12/10/2009)[/b][hr]Re: [i]A clustered index physically rearanges data on disk.[/i]Technically, the use of the word "disk" is wrong. It rearranges data within the (SQL Server) pages and extents. Where these pages are placed on the [u]physical[/u] disk (track and cylinder) is not within the control of SQL Server. It is the OS (Windows) and/or the physical hardware implementation (e.g., a SAN) that determines where the data is actually placed on the physical disk.The only item that I know of that "physically rearanges data on disk" is a disk defragmenter.[/quote]That's a semantic argument that isn't even accurate. Those pages and extents exist in files on disk and the disk controller must access them. If I rebuild the clustered index then the pages (and their contents) that were logically connected via page chains will be re-arranged (on the disk), i.e. an individual row may move from one physical disk sector of a file extent to another. If I insert a million new rows and my file group needs to extend then that additional disk space must be allocated. I'll grant you that the file system created the extent but it is SQL Server that is creating the physical pages that will be stored on the extent.I suppose you could say that "technically" the only thing that writes to a disk are the read-write heads which are managed by the firmware of the device but that kind of defeats the purpose of the discussion. We don't need to be concerned with that level of abstraction. It's a lot simpler to say that SQL Server physically rearranges data on disk.Thu, 10 Dec 2009 09:48:48 GMTRobert Frascahttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspxRe: [i]A clustered index physically rearanges data on disk.[/i]Technically, the use of the word "disk" is wrong. It rearranges data within the (SQL Server) pages and extents. Where these pages are placed on the [u]physical[/u] disk (track and cylinder) is not within the control of SQL Server. It is the OS (Windows) and/or the physical hardware implementation (e.g., a SAN) that determines where the data is actually placed on the physical disk.The only item that I know of that "physically rearanges data on disk" is a disk defragmenter.Thu, 10 Dec 2009 09:23:57 GMTMauvehttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspxMy page 311 paragaph 1 says something different than your page 311 paragraph 1. Mine says: "...This order is maintained through a doubly linked list called a page chain. (Note that pages in a heap are not linked in any way to each other.) The order of pages in the page chain, and the order of rows on the data pages, is based on the definition of the clustered index. Deciding on which column(s) to cluster is an important performance consideration." (End of Paragraph)This part isn't in my book."When the clustered index is created, it does in fact reorder the data physically on disk according to the cluster key, or as close to physical order as possible given the available space. After that, maintaining the clustered index is a logical operation. Rebuilding the index will again order the data physically. Reorganizing the index is an attempt to make the logical and the physical order match, without all the work of rebuilding the index...." That being said, I don't understand why everyone is confused.[b]A clustered index does physically rearrange data on a disk[/b] when it is created and can continue to do so if there are page splits or when the index is rebuilt. There's no ambiguity here.[b]A clustered index logically tracks data on disk using doubly linked lists.[/b] This has been true for a long time.[b]Dropping and rebuilding a clustered index on a partition scheme may move data to other drives. [/b] The whole point of using a partition scheme is to distribute the data across multiple disks to improve performance by distributing the I/O as well as by creating logically and physically smaller partitions to search. Even if you don't use partitions, the best way to copy a table from one filegroup to another is to drop the clustered index and re-create it using a different file group. That has also been true for quite awhile.I find it particularly amusing that some of you have the chutzpah to claim that Kalen Delaney is wrong. :-)Thu, 10 Dec 2009 09:03:11 GMTRobert Frascahttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspx"doubly linked lists".Well, i learn something today :)I thought it was B-Tree.I don't like questions where all answers are right, it don't learn us the limits, it makes me confusing, and there are some in these QoD. :/Thu, 10 Dec 2009 01:55:38 GMTDude76http://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspx[quote][b]Rich-403221 (12/9/2009)[/b][hr]Also, the doubly linked list would imply that there are pointers on every page to the previous and next pages in the table. I have NEVER read anywhere in any documentation that this exists. As far as I knew, the IAM pages contained the bit maps providing the allocation of pages to a table. Can anyone point me to documentation that describes this linked list?[/quote]It's actually been documented since SQLServer was first released by Sybase, but a lot of the Microsoft Documentation is pretty poor and badly arranged. Look in BOL under "Clustered Index Structures" http://msdn.microsoft.com/en-us/library/ms177443.aspxCheersRoddyWed, 09 Dec 2009 14:51:35 GMTRoddy.CAMERONhttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspxIt have the book and I agree it is a great book, but the answers to the question were a little misleading I thought.Wed, 09 Dec 2009 12:05:55 GMTCliff Joneshttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspxDropping and recreating a Clustered Index can definitely be used to move data to another disk. This is one of several documented/recommended ways to move data of certain tables to another disk in case the original disk got too small or too slow. So the statement itself is correct, but sort of incomplete.Incomplete, because it requires additional actions : the creation of a new Filegroup on a different disk and the "ON <new-filegroup>" clause in the CREATE INDEX statement.Excerpt from the SQL 2005 Books Online :[b][i]Because the leaf level of a clustered index and the data pages are the same by definition, creating a clustered index and using the ON partition_scheme_name or ON filegroup_name clause effectively moves a table from the filegroup on which the table was created to the new partition scheme or filegroup. Before creating tables or indexes on specific filegroups, verify which filegroups are available and that they have enough empty space for the index. For more information, see Determining Index Disk Space Requirements. [/i][/b]However - I sense some sort of inconsistency here.I repeatedly read that "data is *copied* to the leaf pages of the Clustered Index". That would contradict the phrase that it "effectively moves a table", because the original Table would still exist with its original data pages.So - is the data from the original Table physically *copied* or *moved* to the Clustered Index leaf pages ? If they are copied - what happens to the original Table ? Does it still occupy the diskspace and add to the database size but is not used anymore ?I *think* to remember that I had done this procedure once, 3-4 years ago on a SQL 2000 server, to move the Index workload to another disk.And as far as I remember I was able to shrink the original MDF file significantly which would confirm that the data are actually moved.So the table definition still remains in its original filegroup, but the "leaf level" with the data content is actually saved in the Clustered Index on the other filegroup/disk ? And are table data modifications done directly on these moved data pages on the other filegroup/disk within the Clustered Index ?Or on the original filegroup/disk and only moved physically to the Index filegroup/disk when dropping/recreating the Index ?Very confusing.....Wed, 09 Dec 2009 10:49:48 GMTTrekmanhttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspxThere have been a few concerns regarding the moving of data to a different drive if you rebuild an index in a partition scheme.I want to provide a scenario to support the "may" in this option.When partitioning a table, you create a filegroup for each partition. Once created files may be created for each filegroup. It may be necessary for each file to be assigned to a different drive. Let's say the table is 300GB and spread across 2 partitions and filegroups. Each of these filegroups has 10 files spread across 10 drives - that means each file is roughly 15GB, if we have partitioned the data down the middle (it's easier for this scenario).Each partition data will remain in that partition when a rebuild occurs, however, due to the rebuild we have a scenario where the data could now potentially move from one file to another in the filegroup and thus be on a different drive.Wed, 09 Dec 2009 10:10:10 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspxI am very interested in how the rebuilding of a clustered index would result in data being moved to another partition. The partition of a particular record in a table is determined through a calculation based on data in the record itself. Unless either the data or the partition function are changed, than a given record will always be placed into the same partition that it was previously placed in. If this is an accurate statement, than an index rebuild should NEVER result in data being moved into a different partition.Also, the doubly linked list would imply that there are pointers on every page to the previous and next pages in the table. I have NEVER read anywhere in any documentation that this exists. As far as I knew, the IAM pages contained the bit maps providing the allocation of pages to a table. Can anyone point me to documentation that describes this linked list?Wed, 09 Dec 2009 09:39:57 GMTSource-NHhttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspxIf you cause a page split. But if not? It is still false as a general statement.Wed, 09 Dec 2009 09:34:58 GMTadrian.buzilahttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspxActually I'm not sure it's wrong. When you insert/update data you can cause a page split. In that case, the data is rearranged on disk.Wed, 09 Dec 2009 09:29:28 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspxI agree that is not wrong, but is false as a general statement. It is out of context.It is true where you create/rebuilt an index, but false when you insert/update/delete on a table. When you insert/update/delete the order for the existing data is not modified on the disk. The order in this case is mantained logically.Wed, 09 Dec 2009 09:23:40 GMTadrian.buzilahttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspxNot that there is anything wrong, but there is no explanation in the answer about the "Dropping and rebuilding a clustered index on a partition scheme may move data to other drives" statement.The clustered index on a partition scheme has one row in the sys.partitions per partition, each with index_id = 1. Instead of having one, it has as many b-tree structures as the number of partitions. Dropping and rebuilding index on a partition will rearrange the data, but where the moving of the data to other drives come into play? I am not saying that it is not happening, but the QoD does not provide an explanation to this point. In a mean time it would be nice to find it out.OlegWed, 09 Dec 2009 09:10:18 GMTOleg Netchaevhttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspx[quote][b]jts_2003 (12/9/2009)[/b][hr]This reminded me of something that has been puzzling me recently about clustered indexes. If the leaf level of a clustered index actually is the table's data itself, does this mean the table data only exists in the index, or is there another copy somewhere else?e.g. if a table without a clustered index has one created, does all the table data get copied into the leaf level of the clustered index, or does it get 'moved'.[/quote]as you say the the leaf level of the clustered index is the table data itself so by definition there is only one copy of it. The clustered 'index' consists of the root node, a variable no of intermediate nodes, the last of which points to the actual data as the clustered index controls the logical ordering of the data. Its more accurate to think of the leaf node of the clustered index existing as the table data.As to the question itself the explanation contradicts the answer [i]as posed[/i]: Explanation: "... all the columns of every row in the table are in the leaf level of a clustered index. At the time a clustered index is created, data in the table is copied and ordered by the clustering key. [b]Once created, a clustered index is maintained logically rather than physically[/b]. therefore the clustered index only dictates physical order at creation or rebuild time, so the options missed an important word, rebuild, as in A clustered index [b]rebuild [/b] physically rearranges data on disk.so long as the question generates discussion it has fulfilled its purpose. :-)Wed, 09 Dec 2009 08:39:32 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspxThis is wrong:"A clustered index physically rearanges data on disk."because it is out of contextand because: "Once created, a clustered index is maintained logically rather than physically."Wed, 09 Dec 2009 07:22:42 GMTadrian.buzilahttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspxSorry, I wasn't able to understand the last option. Even after few attempts to translate to some other language.Wed, 09 Dec 2009 07:14:40 GMThonza.mfhttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspxIf you think something is wrong, please provide a reason and reference. We have not reason to take your work for it over anyone else's.The third answer has been edited and we are looking for another reference to support the partitioning item.Wed, 09 Dec 2009 07:13:56 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspxThis is false:A clustered index physically rearanges data on disk.Wed, 09 Dec 2009 04:43:04 GMTadrian.buzilahttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspxThis last answer would make perfect sense if it was phrased as 'May' instead of 'Will'.Poorly worded question - especially given how many of the questions on here are irritatingly pernickety about this type of pedantic points.Wed, 09 Dec 2009 03:55:13 GMTRichBhttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspxthe options where bit confusing :hehe:Wed, 09 Dec 2009 03:38:12 GMTBhavesh_Patelhttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspx"Dropping a rebuilding a clustered index on a partition scheme will move disk to other drives." - Very confusing.Wed, 09 Dec 2009 03:29:22 GMTChiragNShttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspx"Dropping a rebuilding a clustered index on a partition scheme will move disk to other drives." :w00t:Are you serious? What if I only have one disk (physical or logical (like in a SAN))? Where will it move? To Limbo or what?I would say that the answer is wrong, becuase data will only be moved to other drives if the partition scheme uses a File group that is placed on another drive.Wed, 09 Dec 2009 02:49:45 GMThakan.wintherhttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspxThis reminded me of something that has been puzzling me recently about clustered indexes. If the leaf level of a clustered index actually is the table's data itself, does this mean the table data only exists in the index, or is there another copy somewhere else?e.g. if a table without a clustered index has one created, does all the table data get copied into the leaf level of the clustered index, or does it get 'moved'.Wed, 09 Dec 2009 02:40:11 GMTjts_2003http://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspx"Dropping a rebuilding a clustered index on a partition scheme will move disk to other drives."I have no idea what this means, and the answer doesn't seem to mention it.Can someone explain please?!Wed, 09 Dec 2009 02:19:44 GMTToreadorhttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspxvk-kirov, I agree with you. Question and answer is incomprehensible!!!!Wed, 09 Dec 2009 01:14:34 GMTCarlo Romagnanohttp://www.sqlservercentral.com/Forums/Topic831157-1398-1.aspxTerribly phrased options to choose.[quote]A clustered index physically rearanges data on disk.[/quote]rea[b]rr[/b]anges?I think this option should look like this: "[b]When created or rebuilded[/b], a clustered index physically rearranges data on disk".A quote from the explanation (and from the Delaney's book):[quote]Once created, a clustered index is maintained [b]logically[/b] rather than physically....After that (after creation), maintaining the clustered index is a [b]logical[/b] operation.[/quote]Another option from the question:[quote]Dropping a rebuilding a clustered index on a partition scheme will move disk to other drives.[/quote]Maybe, "Dropping [b]and[/b] rebuilding"?"Move disk to other drives" – what is the meaning of this? Sounds like abracadabra for me :-)Maybe, it means "moving logical disks C:, D:, E: to other hard disk drives"? :hehe:Wed, 09 Dec 2009 00:43:37 GMTvk-kirov