Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Adding an include to an existing Index Expand / Collapse
Author
Message
Posted Thursday, May 23, 2013 7:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 9:30 AM
Points: 193, Visits: 505
Hi Everyone,

What's the T-sql to add an include column to an existing index?

Many thanks
Post #1456015
Posted Thursday, May 23, 2013 7:58 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:26 AM
Points: 2,674, Visits: 4,740
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/
Post #1456023
Posted Thursday, May 23, 2013 8:11 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:42 PM
Points: 1,796, Visits: 5,805
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


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1456028
    Posted Thursday, May 23, 2013 9:15 AM
    SSC-Enthusiastic

    SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

    Group: General Forum Members
    Last Login: Today @ 9:30 AM
    Points: 193, Visits: 505
    thanks guys
    Post #1456047
    Posted Thursday, May 23, 2013 9:51 AM


    SSC-Forever

    SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

    Group: General Forum Members
    Last Login: Yesterday @ 6:05 AM
    Points: 40,258, Visits: 36,681
    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 2008, MVP
    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

    Post #1456064
    Posted Thursday, May 23, 2013 9:58 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 5:42 PM
    Points: 1,796, Visits: 5,805
    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


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1456070
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse