Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1409

Elementary management of many indexes, best practice?

Show all headers | View raw

Hi, today I'm looking for advice on how to manage a large number of similar 
table indexes.  I may have asked before, but it didn't get done.

I have data to work on spread through many tables in many databases, with 
names such as [BO-Stud18].[dbo].[Calendar_2012], where all "Calendar" tables 
have the same structure, or more or less so.  Sometimes these tables are 
dropped and re-created.  This isn't an ideal design, but I'm kind of stuck 
with it.

Also, stored procedures that operate on the data tend to create an index 
when the programmer has decided that it's needed.  But the indexes are not 
necessarily well designed, and not necessarily appropriate, e.g. with 
a unique key specified where it actually may not be unique in data.

Well, presently I'm considering putting all of the index definitions into 
a huge stored procedure which will be called with parameters 'Calendar' and 
the other parts of the table and database name separated out, and optionally 
a particular index name, and in which, something like this happens:

IF ( @tableName = N'Calendar' )
BEGIN
    IF ( @indexName = N'PK' OR @indexName IS NULL ) -- AND (1=0) 
    BEGIN
        SET @template = 
        N'ALTER TABLE @{table} ADD CONSTRAINT @{index} PRIMARY KEY CLUSTERED
        (authcode, id) --X';
        SET @workstring = 
                REPLACE(REPLACE(
                          @template
                        , N'@{table}', @tableName_fq)
                        , N'@{index}', QUOTENAME(@indexNamePK))
        ;
        EXEC sp_executesql @workstring;
    END
    
    IF ( @indexName = N'IX02' OR @indexName IS NULL ) 
    BEGIN
        SET @template = 
        N'CREATE NONCLUSTERED INDEX IX02 ON @{table} 
        (id, expectedCurrentCostcentre) ON [INDEX] --X';
        SET @workstring = REPLACE(@template, N'@{table}', @tableName_fq);
        EXEC sp_executesql @workstring;
    END
    
    IF ( @indexName = N'IX03' OR @indexName IS NULL ) 
    BEGIN
        SET @template = 
        N'CREATE NONCLUSTERED INDEX IX03 ON @{table} 
        (expectedCurrentCostcentre) ON [INDEX] --X';
        SET @workstring = REPLACE(@template, N'@{table}', @tableName_fq);
        EXEC sp_executesql @workstring;
    END
END

ELSE  -- a similar block for another index

When I want to change the design of indexes, I would put the new index
declaration in the procedure, and execute it for all of the tables that 
should have that index.  And I go on executing it forever.

Since this will be a big, ugly program, one refinement that comes to mind 
is to have the index definitions for each table held in a function named 
after that table, instead.  Or, there may be a completely different good 
way to do the job.

I don't want to go down the wrong path, I don't want to re-invent the wheel - 
so, what do I want to know, but I don't know it?

Thanks!

Robert Carnegie

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

Elementary management of many indexes, best practice? rja.carnegie@gmail.com - 2013-03-12 07:48 -0700
  Re: Elementary management of many indexes, best practice? Erland Sommarskog <esquel@sommarskog.se> - 2013-03-12 22:36 +0100
    Re: Elementary management of many indexes, best practice? rja.carnegie@gmail.com - 2013-03-15 19:23 -0700
      Re: Elementary management of many indexes, best practice? Erland Sommarskog <esquel@sommarskog.se> - 2013-03-16 12:32 +0100
        Re: Elementary management of many indexes, best practice? rja.carnegie@gmail.com - 2013-03-16 14:12 -0700
          Re: Elementary management of many indexes, best practice? Erland Sommarskog <esquel@sommarskog.se> - 2013-03-16 23:08 +0100
      Re: Elementary management of many indexes, best practice? Mark D Powell <Mark.Powell2@hp.com> - 2013-03-27 11:20 -0700

csiph-web