|
|
Posted by Erland Sommarskog on 05/04/07 21:28
Laurence Breeze (i.l.breeze@blahblah.ac.uk) writes:
> However, examining the sysindexes system
> catalog I find that there are indeed 249 entries, but that many
> (possibly all) of them have names of the following type:
>
> '_WA_Sys_XYZ_356BF102'
>
> where 'XYZ' is a column on the table in question.
>
> Looking at the same database across our DEV/TEST/PROD environments I
> notice that these system like index entries are not consistant. The
> names and number of entries differ. I've tried creating a new table
> from a script generated in EM and no such indexes/system objects are
> created.
>
> I'm confused. What are these things. I can't drop them, sp_helpindex
> doesn't refer to them. Examining the sysindexes/sysindexkeys catalogs
> appears to suggect that the indexes have columns but no rows/entries.
They are auto-generated statistics, and an sp_helpstats will list them.
> I've got around the immediate problem by deleting one of these entries
> from sysindexes and I've been able to create the index I wanted. All
> seems well, but is this likely to cause a problem ?
Delete? Does that mean that you turned on Allow updates and operated
on sysindexes directly? That's always a risk, as you may cause some
inconsistency in the system catalog by not deleting all. For instance,
I would expect that you need to delete the entries in sysindexkeys as
well.
Next time you should use DROP STATISTICS.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|