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

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

Re: Elementary management of many indexes, best practice?

Show all headers | View raw

(rja.carnegie@gmail.com) writes:
> 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. 

I would probably start there, as home-built partitioning tends to be
painful. Only if you have really good reasons, like the need to 
quickly age out all data, you should do things like this. And you
should use partitioned views in this case.

> 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: 

My strong preference is to keep all database code under version
control. The we organise tables, is that the table definition itself is
in one file:


CREATE TABLE abasubsystems(
   subsystem    ap_subsystem NOT NULL,
   ss_label     ap_sslabel   NULL,
   inhid        int          NULL,
   sortorder    int          NOT NULL
      CONSTRAINT ckc_ap_sub_sortorder CHECK (sortorder > 0),
   isincomplete bit          NOT NULL,
   deregdate    datetime     NULL,
   CONSTRAINT pk_ap_sub PRIMARY KEY NONCLUSTERED (subsystem)
)

As you see, this table also includes PK constraints and CHECK constraints.
However, foreign keys are in a separate file:

ALTER TABLE abasubsystems ADD
   CONSTRAINT fk1_ap_sub_ap_inh FOREIGN KEY (inhid)
      REFERENCES abainstallhistory (inhid),
   CONSTRAINT fk2_ap_sub_ap_sla FOREIGN KEY (subsystem, ss_label)
      REFERENCES abasslabels (subsystem, ss_label)

This makes it a lot easier to build the database, since we don't have to 
have a certain order.

By now you have guess that the indexes are also in a separate file:

 CREATE CLUSTERED INDEX ap_sub_sortorder_ix ON
    abasubsystems (sortorder)
go

We have tool that permits us to build an empty database from these
files in version control; the tool loads the files in a well-defined
order. We also have a tool that can generate an update script, 
by comparing the contents in version-control at two different labels.
When you add an index to an index file, that file will be reloaded, 
but the tool will recognize that the existing index is unchanged and
not touch it.

As it happens, this tool is available for the public on my web site:
http://www.sommarskog.se/AbaPerls/index.html

This is just one way to skin the cat. You can also use SQL Compare from
Red Gate or SQL Server Data Tools, a free download from Microsoft. But 
I don't like putting index definitions in stored procedures like you
are planning, because all the T-SQL trees will hide the index forest 
for you.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | 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