indexes in sysindex that don't exist

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

indexes in sysindex that don't exist

Author

Topic

kirkkjohnson
Starting Member

3 Posts

Posted - 2004-09-30 : 12:28:31

I see what appears to be indexes for tables in sysindexes that don't actually exist. When I look at the tables in question using the object browser in sql query analyzer the indexes are not listed. How does this situation occur and how can I resynch the database so it eliminates the defunct index listings from the sysindexes table?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-30 : 12:33:57

You are probably looking at statistics and/or hypothetical indexes. To see only "real" indexes:

SELECT i.name, o.name
FROM dbo.sysindexes i
INNER JOIN dbo.sysobjects o
ON i.id = o.id
WHERE indid > 0 AND indid < 255 AND o.type = 'U' AND
(i.status & (64 | 8388608)) <= 0

Tara

kirkkjohnson
Starting Member

3 Posts

Posted - 2004-09-30 : 12:54:45

Thank you. What the heck is a hypothetical index as opposed to "real" indexes? I use Erwin as a design tool and the unreal or statistical indexes show up as real indexes when doing a compare.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-30 : 12:58:54

Hypothetical indexes:

http://www.sql-server-performance.com/index_tuning_wizard_tips.asp

Look about 3/4 of the way down for an explanation.

If you haven't run the index tuning wizard which caused the hypothetical indexes to be created, then what you've got are statistics in there. Books Online has information about statistics if you are interested.

Tara

aegluke
Starting Member

2 Posts

Posted - 2004-10-20 : 09:30:53

Hi,

do you have informations about the status-field in sysindexes? what kind of informations the bits stands for?

erik

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-20 : 13:02:00

I have whatever Books Online has.

Tara

aegluke
Starting Member

2 Posts

Posted - 2004-10-21 : 03:08:22

Hi,

i need an address or book-name/number or what do you mean with books online? the online help? :) the msdn information ends with status-bit for 4096. above bits are not listed.

Erik

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources