Subscribe to:
Post Comments (Atom)
Labels
About Me
Best Practices
Career
Data Mining
Documentation
Feature Requests
Humor
MagicPASS
Meme Monday
Mirroring
Parameter Sniffing
PASS
Performance
PowerShell
Presentations
Query Tuning
Recognition
Replication
Scripts
Security
SQL Power Doc
SQL Server 2005
SQL Server 2008
SQLH2
SQLRally
SQLSaturday
SYDI
T-SQL Tuesday
Tips
Troubleshooting
Updates
VirtualBox
Windows
XML
Copyright © 2015 Kendal Van Dyke. All rights reserved.
Kendal is a database strategist, community advocate, public speaker, and blogger. A practiced IT professional with over 15 years of SQL Server experience, Kendal excels at disaster recovery, high availability planning/implementation, & debugging/troubleshooting mission critical SQL Server environments. Kendal is a Senior Consultant on the Microsoft Premier Developer Support team and President of MagicPASS, the Orlando, FL based chapter of PASS. Before joining Microsoft, Kendal was a SQL Server/Data Platform MVP from 2011-2016.
[About Kendal]
(http://lh3.googleusercontent.com/-wrW1fk8IiFE/Vr36w9dtRxI/AAAAAAAADCw/tVa4vTgLWIw/w139-h140-p/IMG_3503.JPG)
2 comments
Great post!
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?
Thanks
Jag
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.)
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.
Post a Comment