Hi :

For performance reasons, it has been identified that dropping an index
before loading a table and then recreating it after loading, is
beneficial.
I would like to do this, but cannot get the syntax straight.
If you have done it, please share your syntax

1. truncate tableA
2. drop indexes on tableA. (all indexes or just pkindex or just
clustered index ?, drop_existing?)
3. insert into tableA from another tableB using a transform data task
4. recreate index.

If you could give me syntax for steps 2 and 4, that would be great.
Also , i am assuming i would put it in two ExecSQLtasks one before and
one after the transform data task .

thank you in advance
RS

You'll need to swap round steps (1) and (2).
Then use SSMS to generate the skeleton scripts that you'll need. Use Tasks,
Generate Scripts with Include Indexes set to true. Select the tables you need
and script to new window. At this stage you'll have the basis of the script
you need to create and drop the indexes - it just needs trimming down.
You'll start with this sort of thing:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
OBJECT_ID(N'[dbo].[tblProducts]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[tblProducts](
[ProductsCode] [varchar](20) NOT NULL,
[ProductsDescription] [varchar](255) NULL
) ON [CRMCORE_Group1]
END
GO

IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id =
OBJECT_ID(N'[dbo].[tblProducts]') AND name = N'idxProductsCode')
CREATE NONCLUSTERED INDEX [idxProductsCode] ON [dbo].[tblProducts]
(
[ProductsCode] ASC
) ON [CRMCORE_Group1]
GO

Chop out the irrelevant parts and you'll have :

-- DROP
IF EXISTS (SELECT * FROM dbo.sysindexes WHERE id =
OBJECT_ID(N'[dbo].[tblProducts]') AND name = N'idxProductsCode')
DROP INDEX ON [tblProducts].[idxProductsCode]
(
[ProductsCode] ASC
) ON [CRMCORE_Group1]
GO

-- CREATE
IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id =
OBJECT_ID(N'[dbo].[tblProducts]') AND name = N'idxProductsCode')
CREATE NONCLUSTERED INDEX [idxProductsCode] ON [dbo].[tblProducts]
(
[ProductsCode] ASC
) ON [CRMCORE_Group1]
GO

HTH,
Paul Ibison



[quoted text, click to view]