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

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

Elementary management of many indexes, index and statistics names

Show all headers | View raw

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

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

Thread

Elementary management of many indexes, index and statistics names rja.carnegie@gmail.com - 2013-04-05 07:59 -0700
  Re: Elementary management of many indexes, index and statistics names "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2013-04-05 12:36 -0400
  Re: Elementary management of many indexes, index and statistics names "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2013-04-05 12:44 -0400
  Re: Elementary management of many indexes, index and statistics names Erland Sommarskog <esquel@sommarskog.se> - 2013-04-05 20:28 +0200
    Re: Elementary management of many indexes, index and statistics names rja.carnegie@gmail.com - 2013-04-05 15:49 -0700
      Re: Elementary management of many indexes, index and statistics names Erland Sommarskog <esquel@sommarskog.se> - 2013-04-06 11:53 +0200

csiph-web