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

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

Some questions about relational indexes and statistics in SS2005

Show all headers | View raw

I have some questions about relational indexes and statistics in 
Microsoft SQL Server 2005, mainly Standard or Enterprise Editions.

This is in the context of improving indexes and statistics in many 
copies of a database that wasn't designed by me, and in which many 
tables are dropped and re-created by stored procedures overnight,
that I can change, after which users run various reports at will.

1. A nonclustered index can "include" columns that are not part of 
   the index key: can this be done with a nonclustered primary key
   constraint index?

I think the answer is "No", and I don't particularly want to do it
anyway, but the workaround might be to /not/ have a primary key, 
but have a unique nonclustered index with included columns, which 
does everything except display that it is the table's primary key, 
/or/, to create and then /disable/ the primary key index (if you 
can do that), /and/ create the unique nonclustered index.

If you can do it, and if someone already has in my databases, 
then I want to be able to reproduce it.

2. Will SQL Server ever automatically create a separate statistics group 
   on columns that are already the key of a clustered or nonclustered 
   index?  And, if so, should I consider improving the index so that 
   it provides a satisfactory statistics group, and, how can I do that?

I think the answer is "No" in the first place, but that I may find 
cases where (1) an index was created later than when the table was 
created, and a statistics group was already auto-created on the 
columns before the index was; (2) one copy of a table has an index,
and another copy does not have the index, but has the statistics;
(3) an index and a statistics group are on more than one column
and have some of the same columns, or have columns in a different order;
(4) a user-created statistics group has the same columns as an index.
Maybe all at the same time.  All I know now is, they're there,
and I think they're a bad sign.

Thanks for advice,

Robert Carnegie

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

Thread

Some questions about relational indexes and statistics in SS2005 rja.carnegie@gmail.com - 2013-05-07 04:39 -0700
  Re: Some questions about relational indexes and statistics in SS2005 Erland Sommarskog <esquel@sommarskog.se> - 2013-05-07 21:26 +0200
    Re: Some questions about relational indexes and statistics in SS2005 rja.carnegie@gmail.com - 2013-05-09 14:45 -0700
      Re: Some questions about relational indexes and statistics in SS2005 Erland Sommarskog <esquel@sommarskog.se> - 2013-05-10 11:05 +0200
        Re: Some questions about relational indexes and statistics in SS2005 rja.carnegie@gmail.com - 2013-05-12 10:01 -0700
          Re: Some questions about relational indexes and statistics in SS2005 Erland Sommarskog <esquel@sommarskog.se> - 2013-05-12 19:22 +0200

csiph-web