StrataFrame Forum » StrataFrame Database Deployment Toolkit » Database Deployment Toolkit (How do I?) » How to create index with Included columns

RE: How to create index with Included columns

Edhy Rijo — Sat, 28 Jul 2012 15:48:46 GMT

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?

How to create index with Included columns

Jiri Brazda — Sat, 28 Jul 2012 15:48:46 GMT

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

RE: How to create index with Included columns

Jiri Brazda — Thu, 15 Sep 2011 02:22:56 GMT

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

RE: How to create index with Included columns

Edhy Rijo — Wed, 14 Sep 2011 22:59:01 GMT

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:
[code]
/****** 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]
[/code]
If I remember correctly there is an enhancement request for this feature, but don't know if the SF team will add it.