CODEWITHSUNDEEP
sqlindexingsql-server-2012
bendataclear
bendataclear

Reputation: 3848

Dropping and creating indexes using dynamic sql (sp_executesql)

I have a scenario where I am trying to create and maintain indexes on 30+ databases which are regularly restored from backups.

I have the stored procedure below:

ALTER PROCEDURE Create_Index 
    @DATABASE sysname 

AS
BEGIN

    DECLARE @DROPFIRSTINDEX nvarchar(MAX)
    DECLARE @DROPSECONDINDEX nvarchar(MAX)
    DECLARE @CREATEFIRSTINDEX nvarchar(MAX)
    DECLARE @CREATESECONDINDEX nvarchar(MAX)


    SET @DROPFIRSTINDEX = 

    N'IF  EXISTS (SELECT * FROM ' + QUOTENAME(@DATABASE) + N'.[sys].[indexes] WHERE object_id = OBJECT_ID(N''' + QUOTENAME(@DATABASE) + N'.[dbo].[FirstTable]'') AND name = N''IX_FIRSTINDEX'')
    DROP INDEX [IX_FIRSTINDEX] ON ' + QUOTENAME(@DATABASE) + N'.[dbo].[FirstTable] WITH ( ONLINE = OFF )'

    SET @DROPSECONDINDEX =

    N'IF  EXISTS (SELECT * FROM ' + QUOTENAME(@DATABASE) + N'.[sys].[indexes] WHERE object_id = OBJECT_ID(N''' + QUOTENAME(@DATABASE) + N'.[dbo].[SecondTable]'') AND name = N''IX_SECONDINDEX'')
    DROP INDEX [IX_SECONDINDEX] ON ' + QUOTENAME(@DATABASE) + N'.[dbo].[SecondTable] WITH ( ONLINE = OFF )'

    SET @CREATEFIRSTINDEX = 

    N'CREATE NONCLUSTERED INDEX [IX_FIRSTINDEX] ON ' + QUOTENAME(@DATABASE) + N'.[dbo].[FirstTable] 
    (
        [DateTime] ASC
    )WITH (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]'

    SET @CREATESECONDINDEX =

    N'CREATE NONCLUSTERED INDEX [IX_SECONDINDEX]
    ON ' + QUOTENAME(@DATABASE) + N'.[dbo].[SecondTable] ([CustomerID],[SessionID])
    INCLUDE ([Type])'

    EXEC sp_executesql @DROPFIRSTINDEX 

    EXEC sp_executesql @DROPSECONDINDEX 

    EXEC sp_executesql @CREATEFIRSTINDEX 

    EXEC sp_executesql @CREATESECONDINDEX 

    RETURN

END
GO

This works fine and drops and re-creates the indexes, however as they grow each iteration is taking longer and longer. Is there any way this can be changed over to skip the database if they already contain the appropriate indexes? I've tried thinking through a way to do this but have drawn a blank.

Upvotes: 0

Views: 4904

Answers (1)

Dave Jemison
Dave Jemison

Reputation: 704

You're already checking to see if the index exists, then dropping it if it does. So just extend it with IF ELSE logic

IF EXISTS...
   SET @FirstCommand='DROP INDEX...'
ELSE
   SET @FirstCommand='CREATE NONCLUSTERED INDEX...'

Then you only need to do one sp_executesql for each index (instead of one drop and one create).

Upvotes: 1

Related Questions