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

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

Re: Elementary management of many indexes, index and statistics names

Show all headers | View raw

On Friday, 5 April 2013 19:28:20 UTC+1, Erland Sommarskog  wrote:
> (rja.carnegie@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.

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