[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

status and indid from sysindexes table

Jodie

3/28/2007 12:55:00 AM


Hi All,
I have a sqlstatement as below:
select * from sysindexes
where indid < 255 and (status & 64)=0
But I don't understand what is the mean of
1) indid < 255 and
2) (status & 64)=0
Would you please explain it for me.
Thanks In Advance,
JP
5 Answers

Tom Cooper

3/28/2007 3:20:00 AM

0

There are some rows in sysindexes that are not really indexes. There
include rows with indid = 255 (these rows indicate that the table has a text
or image column) and rows with status&64 <> 0 (these rows are for
statistics, not indexes). So whoever wrote this statement wanted to exclude
those rows.

Tom

"Jodie" <Jodie@discussions.microsoft.com> wrote in message
news:89687BC3-8BC3-4F65-BDC3-11D4BD4E523F@microsoft.com...
>
> Hi All,
> I have a sqlstatement as below:
> select * from sysindexes
> where indid < 255 and (status & 64)=0
> But I don't understand what is the mean of
> 1) indid < 255 and
> 2) (status & 64)=0
> Would you please explain it for me.
> Thanks In Advance,
> JP


Jodie

3/28/2007 4:34:00 PM

0

Tom, Thanks a lot for the answer.
I couldn't find the information related to status&64 <> 0 (these rows are
for
> statistics, not indexes). in BOL. Please help me to locate this
information in BOL.
Again thanks,
JP


"Jodie" wrote:

>
> Hi All,
> I have a sqlstatement as below:
> select * from sysindexes
> where indid < 255 and (status & 64)=0
> But I don't understand what is the mean of
> 1) indid < 255 and
> 2) (status & 64)=0
> Would you please explain it for me.
> Thanks In Advance,
> JP

Tom Cooper

3/28/2007 5:17:00 PM

0

Hi Jodie,

It's not documented in BOL (or, as far as I know, anywhere else by
Microsoft). The values used in the status column are not documented and,
therefore, are somewhat risky to use in production code since Microsoft is
free to change the meaning of this column and/or delete this column in
future releases). For that reason, it is better to use a documented way to
determine if an entry in sysindexes is a statistics entry rather than an
index. For example, in this case, you could use

Select name, * From sysindexes
Where indid < 255
And Coalesce(IndexProperty(id, name, 'IsStatistics'), 0) = 0

And that would be better code than status&64 = 0 since it is less likely to
break in future releases.

Nevertheless, in cases like yours where you have a piece of code that is
using an undocumented value in a column, one way to find what that value
means is to look in the Microsoft system stored procedures in master which
often give you good hints for the meaning of these columns and values. In
this case, if you look at sp_helpindex at about line 117, it says

"Jodie" <Jodie@discussions.microsoft.com> wrote in message
news:E1CFE983-FF27-4099-BB0E-4C31E5849F36@microsoft.com...
> Tom, Thanks a lot for the answer.
> I couldn't find the information related to status&64 <> 0 (these rows are
> for
>> statistics, not indexes). in BOL. Please help me to locate this
> information in BOL.
> Again thanks,
> JP
>
>
> "Jodie" wrote:
>
>>
>> Hi All,
>> I have a sqlstatement as below:
>> select * from sysindexes
>> where indid < 255 and (status & 64)=0
>> But I don't understand what is the mean of
>> 1) indid < 255 and
>> 2) (status & 64)=0
>> Would you please explain it for me.
>> Thanks In Advance,
>> JP


Tom Cooper

3/28/2007 5:24:00 PM

0

Sorry, hit send bb mistake, so continuing

at line 117, it says

case when (stats & 64)<>0 then ', '+@des64 else case when (stats & 32)<>0
then ', '+@des32 else @empty end end

(at line 84 it put the value from the status column in the stats column in
#spindtab)

and at line 106, it says

select @des64 = name from master.dbo.spt_values where type = 'I' and number
= 64

so it's displaying name from master.dbo.spt_values where type = 'I' and
number = 64 if status &64 <> 0.

So if you do a

select name from master.dbo.spt_values where type = 'I' and number = 64

you can see the value is 'Statistics'.

But like I say, it's best to use this just to figure out what previously
existing code is doing, not for writing new code.

HTH,
Tom
"Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message
news:%23hKrzzVcHHA.1244@TK2MSFTNGP04.phx.gbl...
> Hi Jodie,
>
> It's not documented in BOL (or, as far as I know, anywhere else by
> Microsoft). The values used in the status column are not documented and,
> therefore, are somewhat risky to use in production code since Microsoft is
> free to change the meaning of this column and/or delete this column in
> future releases). For that reason, it is better to use a documented way
> to determine if an entry in sysindexes is a statistics entry rather than
> an index. For example, in this case, you could use
>
> Select name, * From sysindexes
> Where indid < 255
> And Coalesce(IndexProperty(id, name, 'IsStatistics'), 0) = 0
>
> And that would be better code than status&64 = 0 since it is less likely
> to break in future releases.
>
> Nevertheless, in cases like yours where you have a piece of code that is
> using an undocumented value in a column, one way to find what that value
> means is to look in the Microsoft system stored procedures in master which
> often give you good hints for the meaning of these columns and values. In
> this case, if you look at sp_helpindex at about line 117, it says
>
> "Jodie" <Jodie@discussions.microsoft.com> wrote in message
> news:E1CFE983-FF27-4099-BB0E-4C31E5849F36@microsoft.com...
>> Tom, Thanks a lot for the answer.
>> I couldn't find the information related to status&64 <> 0 (these rows
>> are
>> for
>>> statistics, not indexes). in BOL. Please help me to locate this
>> information in BOL.
>> Again thanks,
>> JP
>>
>>
>> "Jodie" wrote:
>>
>>>
>>> Hi All,
>>> I have a sqlstatement as below:
>>> select * from sysindexes
>>> where indid < 255 and (status & 64)=0
>>> But I don't understand what is the mean of
>>> 1) indid < 255 and
>>> 2) (status & 64)=0
>>> Would you please explain it for me.
>>> Thanks In Advance,
>>> JP
>
>


Jodie

3/28/2007 5:36:00 PM

0


Thanks a ton Tom,
Very very clear explanation.
Once again I'd appreciate your answers.
"Tom Cooper" wrote:

> Sorry, hit send bb mistake, so continuing
>
> at line 117, it says
>
> case when (stats & 64)<>0 then ', '+@des64 else case when (stats & 32)<>0
> then ', '+@des32 else @empty end end
>
> (at line 84 it put the value from the status column in the stats column in
> #spindtab)
>
> and at line 106, it says
>
> select @des64 = name from master.dbo.spt_values where type = 'I' and number
> = 64
>
> so it's displaying name from master.dbo.spt_values where type = 'I' and
> number = 64 if status &64 <> 0.
>
> So if you do a
>
> select name from master.dbo.spt_values where type = 'I' and number = 64
>
> you can see the value is 'Statistics'.
>
> But like I say, it's best to use this just to figure out what previously
> existing code is doing, not for writing new code.
>
> HTH,
> Tom
> "Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message
> news:%23hKrzzVcHHA.1244@TK2MSFTNGP04.phx.gbl...
> > Hi Jodie,
> >
> > It's not documented in BOL (or, as far as I know, anywhere else by
> > Microsoft). The values used in the status column are not documented and,
> > therefore, are somewhat risky to use in production code since Microsoft is
> > free to change the meaning of this column and/or delete this column in
> > future releases). For that reason, it is better to use a documented way
> > to determine if an entry in sysindexes is a statistics entry rather than
> > an index. For example, in this case, you could use
> >
> > Select name, * From sysindexes
> > Where indid < 255
> > And Coalesce(IndexProperty(id, name, 'IsStatistics'), 0) = 0
> >
> > And that would be better code than status&64 = 0 since it is less likely
> > to break in future releases.
> >
> > Nevertheless, in cases like yours where you have a piece of code that is
> > using an undocumented value in a column, one way to find what that value
> > means is to look in the Microsoft system stored procedures in master which
> > often give you good hints for the meaning of these columns and values. In
> > this case, if you look at sp_helpindex at about line 117, it says
> >
> > "Jodie" <Jodie@discussions.microsoft.com> wrote in message
> > news:E1CFE983-FF27-4099-BB0E-4C31E5849F36@microsoft.com...
> >> Tom, Thanks a lot for the answer.
> >> I couldn't find the information related to status&64 <> 0 (these rows
> >> are
> >> for
> >>> statistics, not indexes). in BOL. Please help me to locate this
> >> information in BOL.
> >> Again thanks,
> >> JP
> >>
> >>
> >> "Jodie" wrote:
> >>
> >>>
> >>> Hi All,
> >>> I have a sqlstatement as below:
> >>> select * from sysindexes
> >>> where indid < 255 and (status & 64)=0
> >>> But I don't understand what is the mean of
> >>> 1) indid < 255 and
> >>> 2) (status & 64)=0
> >>> Would you please explain it for me.
> >>> Thanks In Advance,
> >>> JP
> >
> >
>
>
>