StrataFrame Forum

How to create index with Included columns

http://forum.strataframe.net/Topic30348.aspx

By Jiri Brazda - 9/14/2011

Hi,

is it possible to create an index with included columns using DDT? I can create such index using management studio but I cannot see any possibility to create it with DDT. In case it is not possible - is there any workaround?

Thanks

George
By Edhy Rijo - 9/14/2011

Hi Jiri,

That functionality is not included in the DDT.  What I am doing is creating a Post-Deploy Script with the TSQL code to add the indexes.  To get the code right, I create the index in Management Studio, then generate a script and copy the data to the DDT script, here is a sample of my script:

/****** Object:  Index [DuplicateImportCheck_idx]    Script Date: 04/07/2010 09:50:41 ******/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TransactionItemsImport]') AND name = N'DuplicateImportCheck_idx')
DROP INDEX [DuplicateImportCheck_idx] ON [dbo].[TransactionItemsImport] WITH ( ONLINE = OFF )
CREATE NONCLUSTERED INDEX [DuplicateImportCheck_idx] ON [dbo].[TransactionItemsImport] 
(
    [FK_Vendor_Carrier] ASC,
    [FK_Transaction] ASC,
    [ImportStatus] ASC
)
INCLUDE ( [FK_TransactionItems],
          [FK_Items],
          [CardLotNo],
          [CardSerialNumber])
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

If I remember correctly there is an enhancement request for this feature, but don't know if the SF team will add it.
By Jiri Brazda - 9/14/2011

Hi Edhy,

thank You for the tip. It is working for me. However - it would be really useful to include this functionality directly to DDT.

Thank You

Best regards

Jiri Brazda 
By Edhy Rijo - 7/28/2012

Hi Trent,

Will SF Team consider to add the feature of create index with INCLUDE columns to the DDT?

I have been using the Post-Deploy script for this, but I have customers with 37GB database size and re-creating the indexes all the time in the post-script cause a long delay over 2 hours for some customers and I am under the impression that there is a logic in DDT that will only re-create an index, if it is needed to do so, is this assumption right?