Hello:
I am attempting to tune the indexes on a frequently used
SQL 2000 (Enterprise Edition)database. I used Profiler
to create a workload file that captured 3/4 of a day's
transactions.
I started the Index Tuning Wizard and went through the
steps to have the wizard analyze my workload file. After
analyzing the database for awhile, the wizard returns the
following message:
The workload does not contain any events or queries that
can be tuned against the current database. Check to see
that the trace contains valid events or that the SQL
script contains valid queries and you are tuning against
the right database.
The workload file is close to 5GB in size and definitely
contains stored procedures and SQL statements. And I am
definitely sitting in the proper database.
Any comments or suggestions would be greatly appreciated
BrennanThis is a multi-part message in MIME format.
--=_NextPart_000_08A3_01C39C8E.8B8E6540
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
You may need to save it as a script and then put in a use statement.
Otherwise, it will assume master. For example, if your database is MyDB:
use MyDB
go
-- the rest of the script goes here
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Brennan" <anonymous@.discussions.microsoft.com> wrote in message
news:011601c39cb7$c7373c60$a401280a@.phx.gbl...
Hello:
I am attempting to tune the indexes on a frequently used
SQL 2000 (Enterprise Edition)database. I used Profiler
to create a workload file that captured 3/4 of a day's
transactions.
I started the Index Tuning Wizard and went through the
steps to have the wizard analyze my workload file. After
analyzing the database for awhile, the wizard returns the
following message:
The workload does not contain any events or queries that
can be tuned against the current database. Check to see
that the trace contains valid events or that the SQL
script contains valid queries and you are tuning against
the right database.
The workload file is close to 5GB in size and definitely
contains stored procedures and SQL statements. And I am
definitely sitting in the proper database.
Any comments or suggestions would be greatly appreciated
Brennan
--=_NextPart_000_08A3_01C39C8E.8B8E6540
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
You may need to save it as a script =and then put in a use statement. Otherwise, it will assume master. For =example, if your database is MyDB:
use MyDB
go
-- the rest of the script goes here
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Brennan" wrote in message news:011601c39cb7$c7=373c60$a401280a@.phx.gbl...Hello:I am attempting to tune the indexes on a frequently used SQL 2000 =(Enterprise Edition)database. I used Profiler to create a workload file =that captured 3/4 of a day's transactions.I started the Index =Tuning Wizard and went through the steps to have the wizard analyze my =workload file. After analyzing the database for awhile, the wizard =returns the following message:The workload does not contain any events =or queries that can be tuned against the current database. Check =to see that the trace contains valid events or that the SQL script =contains valid queries and you are tuning against the right =database.The workload file is close to 5GB in size and definitely contains stored =procedures and SQL statements. And I am definitely sitting in =the proper database.Any comments or suggestions would be greatly =appreciated Brennan
--=_NextPart_000_08A3_01C39C8E.8B8E6540--|||How do I save it as a script?
Brennan
>--Original Message--
>You may need to save it as a script and then put in a
use statement.
>Otherwise, it will assume master. For example, if your
database is MyDB:
>use MyDB
>go
>-- the rest of the script goes here
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Brennan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:011601c39cb7$c7373c60$a401280a@.phx.gbl...
>Hello:
>I am attempting to tune the indexes on a frequently used
>SQL 2000 (Enterprise Edition)database. I used Profiler
>to create a workload file that captured 3/4 of a day's
>transactions.
>I started the Index Tuning Wizard and went through the
>steps to have the wizard analyze my workload file. After
>analyzing the database for awhile, the wizard returns the
>following message:
>The workload does not contain any events or queries that
>can be tuned against the current database. Check to see
>that the trace contains valid events or that the SQL
>script contains valid queries and you are tuning against
>the right database.
>The workload file is close to 5GB in size and definitely
>contains stored procedures and SQL statements. And I am
>definitely sitting in the proper database.
>Any comments or suggestions would be greatly appreciated
>Brennan
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0944_01C39C96.6BD78620
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
I was referring to your workload script. You can load your trace into the
Profiler and then click on File->Save As->SQL Script.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Brennan" <anonymous@.discussions.microsoft.com> wrote in message
news:038301c39cbf$4da0b450$a101280a@.phx.gbl...
How do I save it as a script?
Brennan
>--Original Message--
>You may need to save it as a script and then put in a
use statement.
>Otherwise, it will assume master. For example, if your
database is MyDB:
>use MyDB
>go
>-- the rest of the script goes here
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Brennan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:011601c39cb7$c7373c60$a401280a@.phx.gbl...
>Hello:
>I am attempting to tune the indexes on a frequently used
>SQL 2000 (Enterprise Edition)database. I used Profiler
>to create a workload file that captured 3/4 of a day's
>transactions.
>I started the Index Tuning Wizard and went through the
>steps to have the wizard analyze my workload file. After
>analyzing the database for awhile, the wizard returns the
>following message:
>The workload does not contain any events or queries that
>can be tuned against the current database. Check to see
>that the trace contains valid events or that the SQL
>script contains valid queries and you are tuning against
>the right database.
>The workload file is close to 5GB in size and definitely
>contains stored procedures and SQL statements. And I am
>definitely sitting in the proper database.
>Any comments or suggestions would be greatly appreciated
>Brennan
>
--=_NextPart_000_0944_01C39C96.6BD78620
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I was referring to your workload =script. You can load your trace into the Profiler and then click on File->Save =As->SQL Script.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Brennan" wrote in message news:038301c39cbf$4d=a0b450$a101280a@.phx.gbl...How do I save it as a script?Brennan>--Original Message-->You may need to save it as a script and then put in =a use statement.>Otherwise, it will assume master. For =example, if your database is MyDB:>>use =MyDB>go>-- the rest of the script goes here>>-->Tom>>--=---->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, =SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"Bren=nan" wrote in message>news:011601c39cb7$c7373c60$a401280a@.phx.gbl...>=Hello:>>I am attempting to tune the indexes on a frequently used>SQL 2000 (Enterprise Edition)database. I used Profiler>to create a =workload file that captured 3/4 of a day's>transactions.>>I =started the Index Tuning Wizard and went through the>steps to have the =wizard analyze my workload file. After>analyzing the database for =awhile, the wizard returns the>following message:>>The =workload does not contain any events or queries that>can be tuned against =the current database. Check to see>that the trace contains =valid events or that the SQL>script contains valid queries and you are tuning against>the right database.>>The workload file is =close to 5GB in size and definitely>contains stored procedures and SQL statements. And I am>definitely sitting in the proper database.>>Any comments or suggestions would be greatly appreciated>>Brennan>
--=_NextPart_000_0944_01C39C96.6BD78620--
Showing posts with label edition. Show all posts
Showing posts with label edition. Show all posts
Friday, March 30, 2012
Index statement on SQL 2000 vs. SQL 2005
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 droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
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 droptable.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?
|||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:[vbcol=seagreen]
>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 droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
|||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 droptable.com"
<u3288@.uwe> wrote:
[vbcol=seagreen]
>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:
|||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 = 00110000011110000011001000110000
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:[vbcol=seagreen]
>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 droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
|||On Mon, 23 Oct 2006 17:24:10 GMT, "cbrichards via droptable.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 = 00110000011110000011001000110000
0x20 is hexidecimal 20, or decimal 32, or binary 100000; a single bit
is "on".
Roy Harvey
Beacon Falls, CT
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 droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
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 droptable.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?
|||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:[vbcol=seagreen]
>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 droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
|||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 droptable.com"
<u3288@.uwe> wrote:
[vbcol=seagreen]
>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:
|||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 = 00110000011110000011001000110000
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:[vbcol=seagreen]
>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 droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
|||On Mon, 23 Oct 2006 17:24:10 GMT, "cbrichards via droptable.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 = 00110000011110000011001000110000
0x20 is hexidecimal 20, or decimal 32, or binary 100000; a single bit
is "on".
Roy Harvey
Beacon Falls, CT
Index statement on SQL 2000 vs. SQL 2005
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 regu
lar
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 als
o
statistic names [_WA_Sys_...] and regular column names), what do I need
to do?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1Found 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 droptable.com"
<u3288@.uwe> wrote:
[vbcol=seagreen]
>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 reg
ular
>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 ru
n
>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 inde
x
>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 al
so
>statistic names [_WA_Sys_...] and regular column names), what do I need to do?[
/vbcol]|||Thanks Ron, that did the trick.
However, how do I make sense of "(sysindexes.status & 0x20) = 0"? It appear
s
to be performing a bitwise logical AND operation. So what I am trying to mak
e
sense, if I have a unique clustered index with a status of 18, then if I plu
g
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:[vbcol=seagreen]
>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 droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1|||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 droptable.com"
<u3288@.uwe> wrote:
[vbcol=seagreen]
>Thanks Ron, that did the trick.
>However, how do I make sense of "(sysindexes.status & 0x20) = 0"? It appea
rs
>to be performing a bitwise logical AND operation. So what I am trying to ma
ke
>sense, if I have a unique clustered index with a status of 18, then if I pl
ug
>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:|||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 = 00110000011110000011001000110000
BOL says: "The & bitwise operator performs a bitwise logical AND between the
two expressions, taking each corresponding bit for both expressions. The bit
s
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 "1
8
& 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:[vbcol=seagreen]
>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 droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1|||On Mon, 23 Oct 2006 17:24:10 GMT, "cbrichards via droptable.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 = 00110000011110000011001000110000
0x20 is hexidecimal 20, or decimal 32, or binary 100000; a single bit
is "on".
Roy Harvey
Beacon Falls, CT
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 regu
lar
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 als
o
statistic names [_WA_Sys_...] and regular column names), what do I need
to do?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1Found 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 droptable.com"
<u3288@.uwe> wrote:
[vbcol=seagreen]
>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 reg
ular
>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 ru
n
>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 inde
x
>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 al
so
>statistic names [_WA_Sys_...] and regular column names), what do I need to do?[
/vbcol]|||Thanks Ron, that did the trick.
However, how do I make sense of "(sysindexes.status & 0x20) = 0"? It appear
s
to be performing a bitwise logical AND operation. So what I am trying to mak
e
sense, if I have a unique clustered index with a status of 18, then if I plu
g
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:[vbcol=seagreen]
>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 droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1|||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 droptable.com"
<u3288@.uwe> wrote:
[vbcol=seagreen]
>Thanks Ron, that did the trick.
>However, how do I make sense of "(sysindexes.status & 0x20) = 0"? It appea
rs
>to be performing a bitwise logical AND operation. So what I am trying to ma
ke
>sense, if I have a unique clustered index with a status of 18, then if I pl
ug
>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:|||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 = 00110000011110000011001000110000
BOL says: "The & bitwise operator performs a bitwise logical AND between the
two expressions, taking each corresponding bit for both expressions. The bit
s
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 "1
8
& 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:[vbcol=seagreen]
>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 droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1|||On Mon, 23 Oct 2006 17:24:10 GMT, "cbrichards via droptable.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 = 00110000011110000011001000110000
0x20 is hexidecimal 20, or decimal 32, or binary 100000; a single bit
is "on".
Roy Harvey
Beacon Falls, CT
Index statement on SQL 2000 vs. SQL 2005
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 SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1Found 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 SQLMonster.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?|||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:
>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
>>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
>[quoted text clipped - 26 lines]
>>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 SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1|||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 SQLMonster.com"
<u3288@.uwe> wrote:
>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:
>>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
>>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
>>[quoted text clipped - 26 lines]
>>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?|||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 = 00110000011110000011001000110000
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:
>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
>>Thanks Ron, that did the trick.
>[quoted text clipped - 18 lines]
>>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 SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1|||On Mon, 23 Oct 2006 17:24:10 GMT, "cbrichards via SQLMonster.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 = 00110000011110000011001000110000
0x20 is hexidecimal 20, or decimal 32, or binary 100000; a single bit
is "on".
Roy Harvey
Beacon Falls, CT
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 SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1Found 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 SQLMonster.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?|||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:
>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
>>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
>[quoted text clipped - 26 lines]
>>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 SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1|||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 SQLMonster.com"
<u3288@.uwe> wrote:
>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:
>>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
>>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
>>[quoted text clipped - 26 lines]
>>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?|||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 = 00110000011110000011001000110000
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:
>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
>>Thanks Ron, that did the trick.
>[quoted text clipped - 18 lines]
>>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 SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1|||On Mon, 23 Oct 2006 17:24:10 GMT, "cbrichards via SQLMonster.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 = 00110000011110000011001000110000
0x20 is hexidecimal 20, or decimal 32, or binary 100000; a single bit
is "on".
Roy Harvey
Beacon Falls, CT
Subscribe to:
Posts (Atom)