*_WA_Sys_* indexes in SysIndexes

In a solution I am currently working on, using SysIndexes. While testing the
solution I ran into an error due to certain records in SysIndexes having
Index Name something like *WA_Sys*, and I learned from Internet that these
files are created by SQL Server itself for optimization.
FYI, beside other columns, my table has one Unique Identifier column and one
TimeStamp column for which I show an entry in SysIndexes
Now the thing which surprises me is while I was trying to figure out, how I
can exclude theses indexes from my query, I created Primary Key in Enterprise
Manager and saved the table, which got rid of these indexes, now I could not
see these indexes in SysIndexes. I don't want to go in production with this
query, I want add a condition in "Where" clause to omit these indexes, is
there a way? I tried to find documentation for Status Column however, I found
very little.
You can use INDEXPROPERTY to identify hypothetical indexes and statistics.
For example:
SELECT name AS IndexName
FROM sysindexes
WHERE
indid BETWEEN 1 AND 254 AND
INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
INDEXPROPERTY(id, name, 'IsHypothetical') = 0
Hope this helps.
Dan Guzman
SQL Server MVP
"zero" <zero@.discussions.microsoft.com> wrote in message
news:3D0106E7-5D89-406F-9170-B4ADC3FE0E2B@.microsoft.com...
> In a solution I am currently working on, using SysIndexes. While testing
the
> solution I ran into an error due to certain records in SysIndexes having
> Index Name something like *WA_Sys*, and I learned from Internet that these
> files are created by SQL Server itself for optimization.
> FYI, beside other columns, my table has one Unique Identifier column and
one
> TimeStamp column for which I show an entry in SysIndexes
> Now the thing which surprises me is while I was trying to figure out, how
I
> can exclude theses indexes from my query, I created Primary Key in
Enterprise
> Manager and saved the table, which got rid of these indexes, now I could
not
> see these indexes in SysIndexes. I don't want to go in production with
this
> query, I want add a condition in "Where" clause to omit these indexes, is
> there a way? I tried to find documentation for Status Column however, I
found
> very little.
|||I hope INDEXPROPERTY will help. However, I am bit concerned, is there any
"Status" type other than the mentioned in documentation of INDEXPROPERTY?
Does any one has (or know of resource) all the different "Status" codes and
its meaning for the "Status" column of "SysIndexes" table?
|||Are there any specific index properties you are looking for and can't get
with INDEXPROPERTY? The issue with undocumented values is that these can
change between SQL Server versions and service packs and break scripts that
make assumptions on the values.
I did a cursory Google search and all of the sysindexes status values I
found are currently exposed via INDEXPROPERTY.
Hope this helps.
Dan Guzman
SQL Server MVP
"zero" <zero@.discussions.microsoft.com> wrote in message
news:360E60CD-EB7B-40DF-9FF7-80D80D4794AB@.microsoft.com...
> I hope INDEXPROPERTY will help. However, I am bit concerned, is there any
> "Status" type other than the mentioned in documentation of INDEXPROPERTY?
> Does any one has (or know of resource) all the different "Status" codes
and
> its meaning for the "Status" column of "SysIndexes" table?