tag:blogger.com,1999:blog-2173119910600284569.post6161460326971932524..comments2024-02-06T04:58:09.639-05:00Comments on Kendal Van Dyke: Index Operations Showdown: Drop & Create vs. Create With Drop_Existing vs. RebuildUnknownnoreply@blogger.comBlogger2125tag:blogger.com,1999:blog-2173119910600284569.post-29619232924220953312010-10-04T00:37:47.851-04:002010-10-04T00:37:47.851-04:00I assume by "remove an index key", you r...I assume by "remove an index key", you really mean "remove an index field"? Anyway, your index field sizes should be much smaller than your table field sizes, so in any case "drop existing" should be more efficient in any case. (Which any decent documentation should also tell you. I had read the same info on this years ago, so it isn't anything new.)<br />Indexes use only the first field in the binary tree lookup. It will be significantly faster dropping a field other than the first one because the tree doesn't have to be rebuilt by scanning the entire index to rebuild the binary tree as well as the leaf pages of the index.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-2173119910600284569.post-16278734997947075172010-10-03T13:09:18.110-04:002010-10-03T13:09:18.110-04:00Great post!
Is create with drop existing also mor...Great post!<br /><br />Is create with drop existing also more effecient when re-creating the index to remove an index key but keeping the index on the same filegroup?<br /><br />Thanks<br />JagJagjit Thindhttps://www.blogger.com/profile/16214371563929994636noreply@blogger.com