X-Received: by 10.224.18.132 with SMTP id w4mr5586672qaa.1.1365173945559; Fri, 05 Apr 2013 07:59:05 -0700 (PDT)
X-Received: by 10.49.85.165 with SMTP id i5mr1023190qez.28.1365173945540; Fri, 05 Apr 2013 07:59:05 -0700 (PDT)
Path: csiph.com!newsfeed.hal-mli.net!feeder3.hal-mli.net!newsfeed.hal-mli.net!feeder1.hal-mli.net!newspeer1.nac.net!border4.nntp.dca.giganews.com!border2.nntp.dca.giganews.com!nntp.giganews.com!ca1no30628646qab.0!news-out.google.com!ef9ni1849qab.0!nntp.google.com!ca1no30628643qab.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups: comp.databases.ms-sqlserver
Date: Fri, 5 Apr 2013 07:59:05 -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: <d87de084-20ee-4c81-bc5b-e235f51b6fda@googlegroups.com>
Subject: Elementary management of many indexes, index and statistics names
From: rja.carnegie@gmail.com
Injection-Date: Fri, 05 Apr 2013 14:59:05 +0000
Content-Type: text/plain; charset=ISO-8859-1
Lines: 81
Xref: csiph.com comp.databases.ms-sqlserver:1433

I'm pursuing (at least mentally) the plan of using individual 
stored procedures, for instance, to create or re-create some 
simple indexes /and/ statistics on tables, so that the definitions
of these objects are in a single place.  

In a database design which I know is bad, one table design, and 
requirement for an index or statistics, is liable to exist in 
multiple copies with a different database name, and a different 
year number appended to the table name.  So the idea is to write a
procedure - example below - that can create the same index on 
any copy of the table.

What I want to check with you is this: in SQL Server 2005, 
am I OK to create similar indexes and statistics on several tables 
using the same index and statistic names each time, /except/
that a primary key index / constraint name must be /unique/
in the database......or in the schema??

Here's an untested prototype procedure; there will be one for each 
set of similar tables.  This is intended to be called with 
@tableName_fq such as N'[BO-Stud15].[dbo].[ExpectedCostcentre_2011]',
@indexName probably NULL, 'PK', 'IX02', or 'IX03',  and 
@indexNamePK = N'PK_ExpectedCostcentre_2011', to create 
a database-unique-named primary key object.  Obviously, I'm 
hoping to get away with using the same index and statistics names
for each table, except for the primary key.  In fact, I'm very fed up 
with index names, as you see.

Yes, each database has a filegroup called INDEX to put indexes in.  
And a dash in the database name.

I'm also not sure what happens if the table names are these:

N'[BO-Stud15].[year2011].[ExpectedCostcentre]'
N'[BO-Stud15].[year2012].[ExpectedCostcentre]'
N'[BO-Stud15].[year2013].[ExpectedCostcentre]'

Can I then use the same primary key name on each of these tables?

----

CREATE PROCEDURE CreateIndex_Stud_ExpectedCostcentre
          @tableName_fq sysname, @indexName sysname, @indexNamePK sysname
AS
DECLARE
      @template    nvarchar(max)
    , @workstring  nvarchar(max)


IF ( @indexName = N'PK' OR @indexName IS NULL )
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

GO