With permission from Kimberly Tripp, the creator of the fantastic duplicate index finder for SQL Server 2005 and 2008, I have ported this duplicate index finder to SQL Server 2000.
I am fortunate in many respects, in that SQL Server 2000 does not support included columns, nor disabled indexes, so the scripts themselves were straightforward.
The duplicate finder is based on a modified sp_helpindex (the standard stored procedure in SQL Server 2000 for providing information on indexes), using the general layout of the sp_SQLskills_SQL2008_helpindex script.
My original approach was to do a line-by-line conversion. As I pointed out in a previous post, the bitwise operators in the system tables are not for the faint-hearted, especially since all the hard work has already been done in the built-in stuff.
With that in mind, I threw away all my code and started afresh on Monday. It took about two hours in total, with some testing here and there, leveraging the existing sp_helpindex, and this evening I presented the greatly simplified scripts at the Immersion Event I’m attending (Week 2) in Tampa.
Bitwise operators aside, the challenge came in with the duplicate finder portion, which in Kimberly’s script relies a lot on NVARCHAR(MAX). Using the same method Paul Randal did for coming up with the index fragmentation thresholds in Books Online, I figured that 1200 characters should be sufficient to carry the values in the column, index and DROP fields respectively.
I believe Kimberly will make the scripts available on her blog at some stage, but you can grab them from my scripts collection on GitHub. Both scripts are available from there.
Photo by Kelly Sikkema on Unsplash.