MS SQL Server - Index statement on SQL 2000 vs. SQL 2005

Author

Index statement on SQL 2000 vs. SQL 2005

cbrichards via SQLMonster.com

2006-10-25, 6:00 am

If I run the following statement on SQL 2000 Standard Edition, SP4, I get 2,
054 rows. I notice that the values returned in the IndexName column are not
all index names, but also include statistic names [_WA_Sys_...] and regular
column names. This is the statement:

SELECT
s.name AS TableName,
i.name AS IndexName
FROM sys.objects s
JOIN sys.indexes i
ON s.object_id = i.object_id
WHERE s.object_id > 99

If I take the same database used above, and restore it to SQL 2005, then run
the following statement on SQL 2005 Standard Edition, SP1, I get 647 rows. I
notice that the values returned in the IndexName column are all valid index
names, and DO NOT include statistic names and regular column names. This is
the statement:

SELECT
s.name AS TableName,
i.name AS IndexName
FROM sys.objects s
JOIN sys.indexes i
ON s.object_id = i.object_id
WHERE s.object_id > 99

In order to get the two to retrieve the same results, or stated another way,
in order to get the SQL 2000 version to return just index names (and not also
statistic names [_WA_Sys_...] and regular column names), what do I need to do?

--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...server/200610/1

Roy Harvey

2006-10-25, 6:00 am

Found this on MSDN: "For Microsoft SQL Server 2000, use the predicate
sysindexes.status & 0x20=0 to identify real indexes."

http://msdn2.microsoft.com/en-us/library/ms190172.aspx

Roy Harvey
Beacon Falls, CT

On Mon, 23 Oct 2006 14:46:56 GMT, "cbrichards via webservertalk.com"
<u3288@uwe> wrote:

>If I run the following statement on SQL 2000 Standard Edition, SP4, I get 2,
>054 rows. I notice that the values returned in the IndexName column are not
>all index names, but also include statistic names [_WA_Sys_...] and regular
>column names. This is the statement:
>
>SELECT
> s.name AS TableName,
> i.name AS IndexName
>FROM sys.objects s
>JOIN sys.indexes i
> ON s.object_id = i.object_id
> WHERE s.object_id > 99
>
>If I take the same database used above, and restore it to SQL 2005, then run
>the following statement on SQL 2005 Standard Edition, SP1, I get 647 rows. I
>notice that the values returned in the IndexName column are all valid index
>names, and DO NOT include statistic names and regular column names. This is
>the statement:
>
>SELECT
> s.name AS TableName,
> i.name AS IndexName
>FROM sys.objects s
>JOIN sys.indexes i
> ON s.object_id = i.object_id
>WHERE s.object_id > 99
>
>
>In order to get the two to retrieve the same results, or stated another way,
>in order to get the SQL 2000 version to return just index names (and not also
>statistic names [_WA_Sys_...] and regular column names), what do I need to do?

cbrichards via SQLMonster.com

2006-10-25, 6:00 am

Thanks Ron, that did the trick.

However, how do I make sense of "(sysindexes.status & 0x20) = 0"? It appears
to be performing a bitwise logical AND operation. So what I am trying to make
sense, if I have a unique clustered index with a status of 18, then if I plug
that into a converter (18 & 0x20), it does not = 0, but 24. Can you
enlighten me how (18 & 0x20), for example = 0, or how "(sysindexes.status &
0x20) = 0"? Thanks.

Roy Harvey wrote:[color=darkred
]
>Found this on MSDN: "For Microsoft SQL Server 2000, use the predicate
>sysindexes.status & 0x20=0 to identify real indexes."
>
>http://msdn2.microsoft.com/en-us/library/ms190172.aspx
>
>Roy Harvey
>Beacon Falls, CT
>
>[quoted text clipped - 26 lines]

--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...server/200610/1

Roy Harvey

2006-10-25, 6:00 am

You simply add that to the WHERE clause:

SELECT O.name AS TableName,
I.name AS IndexName
FROM sysobjects O
JOIN sysindexes I
ON O.id = I.id
WHERE I.id > 99
AND I.status & 0x20 = 0

Roy Harvey
Beacon Falls, CT

On Mon, 23 Oct 2006 16:32:18 GMT, "cbrichards via webservertalk.com"
<u3288@uwe> wrote:
[color=darkred]
>Thanks Ron, that did the trick.
>
>However, how do I make sense of "(sysindexes.status & 0x20) = 0"? It appears
>to be performing a bitwise logical AND operation. So what I am trying to make
>sense, if I have a unique clustered index with a status of 18, then if I plug
>that into a converter (18 & 0x20), it does not = 0, but 24. Can you
>enlighten me how (18 & 0x20), for example = 0, or how "(sysindexes.status &
>0x20) = 0"? Thanks.
>
>Roy Harvey wrote:

cbrichards via SQLMonster.com

2006-10-25, 6:00 am

What I am trying to say is this...

Let's say I run the following:

SELECT TOP 1 i.status
FROM sysobjects O
JOIN sysindexes I
ON O.id = I.id
WHERE I.id > 99
AND I.status & 0x20 = 0

I return the value: 18

My binary representation for 18 = 0011000100111000
My binary representation for 0x20 = 00110000011110000011
001000110000

BOL says: "The & bitwise operator performs a bitwise logical AND between the
two expressions, taking each corresponding bit for both expressions. The bits
in the result are set to 1 if and only if both bits (for the current bit
being resolved) in the input expressions have a value of 1; otherwise, the
bit in the result is set to 0."

If I compare the binary representations, then I do not see how (in the query
statement above) "I.status & 0x20 = 0" or written with the returned value "18
& 0x20 = 0", when both bits = 1 on several instances.

I am not disputing the SQL statement works (returning only real indexes), as
it certainly does. What I am trying to understand is how the "Logical AND"
statement = 0.

Roy Harvey wrote:[color=darkred
]
>You simply add that to the WHERE clause:
>
>SELECT O.name AS TableName,
> I.name AS IndexName
> FROM sysobjects O
> JOIN sysindexes I
> ON O.id = I.id
> WHERE I.id > 99
> AND I.status & 0x20 = 0
>
>Roy Harvey
>Beacon Falls, CT
>
>[quoted text clipped - 18 lines]

--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...server/200610/1

Roy Harvey

2006-10-25, 6:00 am

On Mon, 23 Oct 2006 17:24:10 GMT, "cbrichards via webservertalk.com"
<u3288@uwe> wrote:

>My binary representation for 18 = 0011000100111000

0011000100111000 represents the ASCII CHARACTER STRING '18'. Binary
for the NUMBER 18 = 10010.

>My binary representation for 0x20 = 00110000011110000011
001000110000

0x20 is hexidecimal 20, or decimal 32, or binary 100000; a single bit
is "on".

Roy Harvey
Beacon Falls, CT

Sponsored Links