Sysindexes Status field

[sagn]

Could anyone explain to me what the status field
is for?

Amongst my indices (ones I have created)
some are zero and some are > 0.

WHat decides this?

Thanks

diane

 

[bperry]

I don't know if that column is very well documented. I didn't find anything on the MS site. In my copy of Inside SQL (Delaney), a few values are mentioned here and there, but I get the impression there are (perhaps many) others.

1 - Duplicate key
2- Unique ndex
4- Duplicate Row
16- Clustered Index
2048- Enforce Primary Key
4096- Enforece Unique Constraint

Like I said, I think there are other values besides that. Interesting that it looks like the values are powers of 2 (dunno of that holds for the missing values.)

Are these the kind of values you are seeing?

bperry

 

[tlbroadbent]

Take a look at the code for the system stored procedure sp_helpindex in the master database. You can see how the status column is interpreted. The status is interpreted using bitwise operations and looking up values in the table master.dbo.spt_values. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[sagn]

Thanks to both of you...

diane