X-Received: by 10.224.175.65 with SMTP id w1mr11354736qaz.7.1363099701009; Tue, 12 Mar 2013 07:48:21 -0700 (PDT)
X-Received: by 10.49.71.135 with SMTP id v7mr1248222qeu.28.1363099700954; Tue, 12 Mar 2013 07:48:20 -0700 (PDT)
Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!news.glorb.com!t2no1919693qal.0!news-out.google.com!o5ni743qas.0!nntp.google.com!dd2no1919214qab.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups: comp.databases.ms-sqlserver
Date: Tue, 12 Mar 2013 07:48:20 -0700 (PDT)
Complaints-To: groups-abuse@google.com
Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=194.83.173.135; posting-account=dELd-gkAAABehNzDMBP4sfQElk2tFztP
NNTP-Posting-Host: 194.83.173.135
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <0e799bb4-d98b-422d-8d01-c2f0a0ac5810@googlegroups.com>
Subject: Elementary management of many indexes, best practice?
From: rja.carnegie@gmail.com
Injection-Date: Tue, 12 Mar 2013 14:48:21 +0000
Content-Type: text/plain; charset=ISO-8859-1
Xref: csiph.com comp.databases.ms-sqlserver:1409

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