(OLD): Updates (fixes) to sp_helpindex2

NOTE: I’ve released other versions of sp_helpindex since this post. Check out the category: sp_helpindex rewrites for all of the options!

I first posted an update to sp_helpindex here. My version of sp_helpindex was solely to expand what sp_helpindex showed and adds 1 or 2 things based on version: for SQL2005+ it adds included columns and for SQL2008 it also adds the filter predicate. So, there were two versions of sp_helpindex2 depending on which verison you’re using. A lot of folks like the changes to this sp but, alas, it had a bug (or two :) and in fact, I found a few others when I went back over this as well. So, thanks to Josh (who commented here) and to a private email (thanks Vasco!), I have an updated version of sp_helpindex2:

For SQL Server 2005, here’s your new sp_helpindex2 script: sp_helpindex2_2005.zip (2.89 KB)

And, here’s a simple test script for 2005:

 

DROP TABLE tbl1
GO

 

CREATE TABLE tbl1( c1 int, c2 int, c3 int, c4 int)
GO
CREATE INDEX ix_1 ON tbl1(c1) INCLUDE (c2)
CREATE INDEX ix_2 ON tbl1(c1)
CREATE INDEX ix_3 ON tbl1(c1) INCLUDE (c2, c3)
CREATE INDEX ix_4 ON tbl1(c1, c3) INCLUDE (c2)
CREATE INDEX ix_5 ON tbl1(c3) INCLUDE (c1, c2, c4)
CREATE INDEX ix_6 ON tbl1(c1, c2) INCLUDE (c3, c4)
go

sp_helpindex2 tbl1
go

 

index_name index_description index_keys included_columns
ix_1 nonclustered located on fg1 c1 c2
ix_2 nonclustered located on fg1 c1 NULL
ix_3 nonclustered located on fg1 c1 c2, c3
ix_4 nonclustered located on fg1 c1, c3 c2
ix_5 nonclustered located on fg1 c3 c1, c2, c4
ix_6 nonclustered located on fg1 c1, c2 c3, c4

For SQL Server 2008, here’s your new sp_helpindex2 script: sp_helpindex2_2008.zip (2.84 KB)

And, here’s a simple test script for 2008:

 

DROP TABLE tbl1
GO

 

CREATE TABLE tbl1( c1 int, c2 int, c3 int, c4 int)
CREATE INDEX ix_1 ON tbl1(c1) INCLUDE (c2)
CREATE INDEX ix_2 ON tbl1(c1)
CREATE INDEX ix_3 ON tbl1(c1) INCLUDE (c2, c3)
CREATE INDEX ix_4 ON tbl1(c1, c3) INCLUDE (c2)
CREATE INDEX ix_5 ON tbl1(c3) INCLUDE (c1, c2, c4)
CREATE INDEX ix_6 ON tbl1(c1, c2) INCLUDE (c3, c4)

CREATE INDEX ix_1f ON tbl1(c1) INCLUDE (c2)
WHERE c3 IS NOT NULL

CREATE INDEX ix_2f ON tbl1(c1)
WHERE c4 > 2

 

CREATE INDEX ix_3f ON tbl1(c1) INCLUDE (c2, c3)
WHERE c4 > 2 AND c1 < 50 AND c2 = 12

 

CREATE INDEX ix_4f ON tbl1(c1, c3) INCLUDE (c2)
WHERE c4 IS NOT NULL AND c1 = 12

 

CREATE INDEX ix_5f ON tbl1(c3) INCLUDE (c1, c2, c4)
WHERE c1 > 5

CREATE INDEX ix_6f ON tbl1(c1, c2) INCLUDE (c3, c4)
WHERE c4 < 20
go

sp_helpindex2 tbl1
go

index_name index_description index_keys included_columns filter_definition
ix_1 nonclustered located on PRIMARY c1 c2 NULL
ix_1f nonclustered located on PRIMARY c1 c2 ([c3] IS NOT NULL)
ix_2 nonclustered located on PRIMARY c1 c2 NULL
ix_2f nonclustered located on PRIMARY c1 c2 ([c4]>(2))
ix_3 nonclustered located on PRIMARY c1 c2, c3 NULL
ix_3f nonclustered located on PRIMARY c1 c2, c3 ([c4]>(2) AND [c1]<(50) AND [c2]=(12))
ix_4 nonclustered located on PRIMARY c1, c3 c2 NULL
ix_4f nonclustered located on PRIMARY c1, c3 c2 ([c4] IS NOT NULL AND [c1]=(12))
ix_5 nonclustered located on PRIMARY c3 c1, c2, c4 NULL
ix_5f nonclustered located on PRIMARY c3 c1, c2, c4 ([c1]>(5))
ix_6 nonclustered located on PRIMARY c1, c2 c3, c4 NULL
ix_6f nonclustered located on PRIMARY c1, c2 c3, c4 ([c4]<(20))

Have fun!
kt

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

SQLskills SQL101: Partitioning

Continuing on our path to understanding the basics and core concepts, there’s a big topic that’s often greatly misunderstood and that’s partitioning. I know I’m

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.