Adding an include to an existing Index

Question

Post reply

Adding an include to an existing Index

Edward-445599

Hall of Fame

Points: 3147
More actions
May 23, 2013 at 7:49 am

#274111

Hi Everyone,
What's the T-sql to add an include column to an existing index?
Many thanks
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

You will have to drop the existing index and then create the index with the INCLUDED columns

The link below can help you with the syntax to create the index with INCLUDED columns

http://msdn.microsoft.com/en-us/library/ms190806%28v=sql.90%29.aspx

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

mister.magoo SSC-Forever Points: 47068 More actions · Answer 2

Kingston Dhasian (5/23/2013)
You will have to drop the existing index and then create the index with the INCLUDED columns
The link below can help you with the syntax to create the index with INCLUDED columns
http://msdn.microsoft.com/en-us/library/ms190806%28v=sql.90%29.aspx

I don't have 2005 any more, but interestingly on 2012 at least, you can just issue a CREATE INDEX using the same index name without dropping it first!

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Edward-445599 Hall of Fame Points: 3147 More actions · Answer 3

Edward-445599

Hall of Fame

Points: 3147

May 23, 2013 at 9:15 am

#1617892

thanks guys

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

mister.magoo (5/23/2013)
I don't have 2005 any more, but interestingly on 2012 at least, you can just issue a CREATE INDEX using the same index name without dropping it first!

You can in most, if not all versions, by adding the DROP_EXISTING clause to the create index.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mister.magoo SSC-Forever Points: 47068 More actions · Answer 5

GilaMonster (5/23/2013)
mister.magoo (5/23/2013)
I don't have 2005 any more, but interestingly on 2012 at least, you can just issue a CREATE INDEX using the same index name without dropping it first!
You can in most, if not all versions, by adding the DROP_EXISTING clause to the create index.

Thanks Gail - I hadn't spotted that

DROP_EXISTING = ON

was indeed included in the CREATE INDEX script I was looking at!

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);