Elementary management of many indexes, index and statistics names

[rja.ca...@gmail.com]

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

[Bob Barrows]

rja.ca...@gmail.com wrote:
> 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.
>

<snip>
I can't seem to find a question here ...? This is very similar to a question
you asked 3 weeks ago

[Bob Barrows]

rja.ca...@gmail.com wrote:
>
> 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?
>

Oh wait. I think I found the question :)
The answer is: object names within a database need to be unique within that
database. Object names can be duplicated in separate databases. This is
something you could easily have tested on your own ...

[Erland Sommarskog]

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

Yes, you can, since these tables are in different schemas.

Whether you should is a different matter. If you are generating all this stuff, creating unique names for all indexes, keys or not, is a no-brainer.

--
Erland Sommarskog, SQL Server MVP, esq...@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

[rja.ca...@gmail.com]

On Friday, 5 April 2013 19:28:20 UTC+1, Erland Sommarskog wrote:
> (rja.ca...@gmail.com) writes:
>
> > 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?
>
> Yes, you can, since these tables are in different schemas.
>
> Whether you should is a different matter. If you are generating all
> this stuff, creating unique names for all indexes, keys or not,
> is a no-brainer.

I'll take "yes, you can", thank you. I /could/ generate distinct
index names by a rational system, but will that make queries run
faster? I don't think so. So if effectively I don't want to have
index names at all... primary key is the only place that my
SS2005 skill set for this sort of problem is going to let me get
myself into that kind of trouble?

Warning: brace yourself for extended complaining about other people...
I'm hoping to make other developers here use this system to put
indexes on new tables - so if they have to read the stored procedure
to see what's in existing indexes, then so much the better. ;-)

I have low expectations because these are mainly Java programmers,
in the sense that that's the skill set that they were hired for
and rated on. So, I've got tables with no indexes, I've got tables
with dozens of indexes, I've got tables whose schema is wrong
anyway... I suppose ideally I want to educate colleagues to
at /least/ put a primary key /or/ one other suitable index on
most tables, even small ones, and then if more is needed,
to have one location where more indexes are defined, for any
given table - so that when I'm called in to fix some problem,
/I/ have one place to go and put and index in /or take one out/.
For instance we've been caught by cases where we thought
(with whatever level of justification) that some columns form
a unique key, and then one day they don't anymore.

In our development environment, hideous table designs and queries
can work adequately, until they meet some real data and fall off
a performance cliff. This means that bad choices in SQL design
aren't penalised at the testing stage. Conversely... I think I
once calculated that each end-user of our applications is
entitled to use SQL Server itself for 10 seconds a day - which
should be okay, but I don't think they test against that, either.

I also have to justify the preventative measures that I have in mind -
if good indexing counts as that - to a manager, also not a SQL
specialist, before I'm /permitted/ to do it.

Having said all that, I probably am going to be depending on
distinctive index names to find and remove rogue index definitions
in the code! Hmm... well, my system doesn't /enforce/ uninformative
names... if you were curious, there's another program higher in the
design that reads metadata and drops one or more existing indexes as
required, without knowing what they are (except, clustered index
is dropped last), so that the code to create them can be
relatively clean.

Maybe I can get people to create indexes uniquely named after the date
they wrote the program, such as IX_20130405_02 ... nope, there'd
be collisions. Okay, put the programmer's initials on as well...
Java has the concept of a worldwide-unique class namespace, but they way
it's achieved if applied here is - possibly useful, possibly dreadful.

Incidentally I'd also like to be pointed to a good index or
schema-and-index design resource. I think I can do a pretty
good job already although improvement is possible, but I more
want something that I can persuade colleagues to read.
Does <http://www.sommarskog.se/yourownindex.html> cover this?

Google for ("SQL Server" index tutorial) also quickly offers me
<http://use-the-index-luke.com/>, which by its contents page
appears to be a free resource with appropriate depth of
coverage, such as why you don't (I think you don't?) create
three indexes, one for each of the three columns in the table
primary key that you're using in queries.

[Erland Sommarskog]

(rja.ca...@gmail.com) writes:
> I'll take "yes, you can", thank you. I /could/ generate distinct
> index names by a rational system, but will that make queries run
> faster? I don't think so.

Of course, you are right that index names and performance are entirely
unrelated. It's only a matter of manageability. What if you decide
to put those table in the same schema?

It's not a big deal, but since you are generating the name anyway, it's
not difficult to build unique index names by using schema and table name.