Skip navigation

Enhance Dev Studio to allow INCLUDE columns to be added to a SQL Server index at creation time

score 20
You have not voted. Not Planned

Currently Developer Studio only permits the creation of an index on its Key columns. SQL tuning of queries very often involves adding columns to the INCLUDE part of an index in SQL Server.

 

The current method is to

   (a) Create the index via Developer Studio and determine its Remedy generated name

   (b) Once it has been created it needs to be  dropped and created in the database with the INCLUDE columns added

 

Example below.

 

Let's say we would like to have an index on columns (C4, C6, C7) for table T111 and have it INCLUDE columns (C1, C179, C112, C60989, C60900, C60901)

 

    (a) We would create the index via Dev STudio and identify its name, say I111_4_1

 

    (b) We would then generate the index's "Drop and Create" SQL (shown below) and add the INCLUDE clause to it and run both the commands:

            

              drop index T111.I111_4_1;

 

              create nonclustered index I111_4_1 on T111 (C4, C6, C7) INCLUDE (C1, C179, C112, C60989, C60900, C60901)

 

This method is obviously manual and also time consuming. Developer Studio would make the whole process faster and simpler.

Comments

Vote history