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

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

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

Index size.

Where can I get the size of the index to add to the below query.
select
t.name [Table],
i.Name [Index],
c.name [Column],
ic.key_ordinal [Key Ordinal],
is_included_column [Included],
is_descending_key [Descending key],
i.type_desc [Index Type],
i.is_unique [Unique Index],
i.is_disabled [Index disabled],
i.fill_factor [Fill Factor],
i.is_hypothetical [hypothetical]
from
sys.indexes i
inner join sys.index_columns ic
on i.index_id = ic.index_id
and i.object_id = ic.object_id
inner join sys.columns c
on c.column_id = ic.column_id
and c.object_id = ic.object_id
inner join sys.tables t
on i.object_id = t.object_id
where t.name='Orders'
order by
t.object_id,
i.index_id,
is_included_column,
c.column_id
Thanks
Shiju Samuel
Hi
SELECT object_name(a.[object_id]) as TableName,a.index_id,
isnull(b.name,'HEAP') as IndexName, sum(a.page_count) as
pages,sum(a.page_count)*1.0/1024 as Mb
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, 'DETAILED')
AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id =
b.index_id
group by a.[object_id],a.index_id, b.name
order by pages desc;
"Shiju Samuel" <shiju.samuel@.gmail.com> wrote in message
news:a3495e0c-cb67-4dfd-9eca-671e8bee8e0d@.y43g2000hsy.googlegroups.com...
> Where can I get the size of the index to add to the below query.
> select
> t.name [Table],
> i.Name [Index],
> c.name [Column],
> ic.key_ordinal [Key Ordinal],
> is_included_column [Included],
> is_descending_key [Descending key],
> i.type_desc [Index Type],
> i.is_unique [Unique Index],
> i.is_disabled [Index disabled],
> i.fill_factor [Fill Factor],
> i.is_hypothetical [hypothetical]
> from
> sys.indexes i
> inner join sys.index_columns ic
> on i.index_id = ic.index_id
> and i.object_id = ic.object_id
> inner join sys.columns c
> on c.column_id = ic.column_id
> and c.object_id = ic.object_id
> inner join sys.tables t
> on i.object_id = t.object_id
> where t.name='Orders'
> order by
> t.object_id,
> i.index_id,
> is_included_column,
> c.column_id
> Thanks
> Shiju Samuel
|||Thanks Uri.

Index size.

Where can I get the size of the index to add to the below query.
select
t.name [Table],
i.Name [Index],
c.name [Column],
ic.key_ordinal [Key Ordinal],
is_included_column [Included],
is_descending_key [Descending key],
i.type_desc [Index Type],
i.is_unique [Unique Index],
i.is_disabled [Index disabled],
i.fill_factor [Fill Factor],
i.is_hypothetical [hypothetical]
from
sys.indexes i
inner join sys.index_columns ic
on i.index_id = ic.index_id
and i.object_id = ic.object_id
inner join sys.columns c
on c.column_id = ic.column_id
and c.object_id = ic.object_id
inner join sys.tables t
on i.object_id = t.object_id
where t.name='Orders'
order by
t.object_id,
i.index_id,
is_included_column,
c.column_id
Thanks
Shiju SamuelHi
SELECT object_name(a.[object_id]) as TableName,a.index_id,
isnull(b.name,'HEAP') as IndexName, sum(a.page_count) as
pages,sum(a.page_count)*1.0/1024 as Mb
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, 'DETAILED')
AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id =b.index_id
group by a.[object_id],a.index_id, b.name
order by pages desc;
"Shiju Samuel" <shiju.samuel@.gmail.com> wrote in message
news:a3495e0c-cb67-4dfd-9eca-671e8bee8e0d@.y43g2000hsy.googlegroups.com...
> Where can I get the size of the index to add to the below query.
> select
> t.name [Table],
> i.Name [Index],
> c.name [Column],
> ic.key_ordinal [Key Ordinal],
> is_included_column [Included],
> is_descending_key [Descending key],
> i.type_desc [Index Type],
> i.is_unique [Unique Index],
> i.is_disabled [Index disabled],
> i.fill_factor [Fill Factor],
> i.is_hypothetical [hypothetical]
> from
> sys.indexes i
> inner join sys.index_columns ic
> on i.index_id = ic.index_id
> and i.object_id = ic.object_id
> inner join sys.columns c
> on c.column_id = ic.column_id
> and c.object_id = ic.object_id
> inner join sys.tables t
> on i.object_id = t.object_id
> where t.name='Orders'
> order by
> t.object_id,
> i.index_id,
> is_included_column,
> c.column_id
> Thanks
> Shiju Samuel|||Thanks Uri.

Index size

Hi!
If I have an index on a nvarchar-column and change this column to varchar,
will the index size decrease? and the db-size too?
Thanks
//MalinNumber of data pages will probably decrease. So will number of pages used by
the index. This will
mean that number of pages used in the database will decrease.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:ejc4dEZ0GHA.3656@.TK2MSFTNGP04.phx.gbl...
> Hi!
> If I have an index on a nvarchar-column and change this column to varchar,
will the index size
> decrease? and the db-size too?
> Thanks
> //Malin
>|||Hi,
Add on to Tibor; I just verfied te scenario with an example. As Tibor
mentioned while converting from NVarchar to Varchar
data type the Index size decreased.
Thanks
Hari
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eTN$BLa0GHA.3464@.TK2MSFTNGP03.phx.gbl...
> Number of data pages will probably decrease. So will number of pages used
> by the index. This will mean that number of pages used in the database
> will decrease.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
> news:ejc4dEZ0GHA.3656@.TK2MSFTNGP04.phx.gbl...
>

Index size

Hi!
If I have an index on a nvarchar-column and change this column to varchar,
will the index size decrease? and the db-size too?
Thanks
//MalinNumber of data pages will probably decrease. So will number of pages used by the index. This will
mean that number of pages used in the database will decrease.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:ejc4dEZ0GHA.3656@.TK2MSFTNGP04.phx.gbl...
> Hi!
> If I have an index on a nvarchar-column and change this column to varchar, will the index size
> decrease? and the db-size too?
> Thanks
> //Malin
>|||Hi,
Add on to Tibor; I just verfied te scenario with an example. As Tibor
mentioned while converting from NVarchar to Varchar
data type the Index size decreased.
Thanks
Hari
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eTN$BLa0GHA.3464@.TK2MSFTNGP03.phx.gbl...
> Number of data pages will probably decrease. So will number of pages used
> by the index. This will mean that number of pages used in the database
> will decrease.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
> news:ejc4dEZ0GHA.3656@.TK2MSFTNGP04.phx.gbl...
>> Hi!
>> If I have an index on a nvarchar-column and change this column to
>> varchar, will the index size decrease? and the db-size too?
>> Thanks
>> //Malin
>sql

Index scan

If i have only one index and that being a clustered index on say a datetime
column namely date1 and if my query is
select col1,col2 from table1 where date1 between '2/1/2008' and '2/3/2008'
If I look at the query plan and it says its doing an index scan, does that
mean its actually touching each and every page of that table or will it
somehow start at the page that has the first entry for '2/1/2008' and
continues through the linked list at the leaf level of the pages and stops
after it reaches '2/3/2008' ?
How is this different if instead of a clustered index, its a non clustered
index ?
ThanksJohn
It is doing Clustered Index Scan , you meant? If you have CI on the table
that means SQL Server logicaly orders all data by Clustered Index Key.
It depends on the query , an optimizer may or may not decide to do a scan
, for example the table is pretty small .
In this case it scans index pages toread the data which ordered (logicaly)
by date column
In you case I'd suggest t create an index on col1,col2 and dt columns called
COVERING index.
> How is this different if instead of a clustered index, its a non clustered
> index ?
The difference is that clusterd index contains at the bottom level the
actual data , while noclustetred contains pointers to the data pages.
"John Doe" <Johndoe@.jd.com> wrote in message
news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
> If i have only one index and that being a clustered index on say a
> datetime column namely date1 and if my query is
> select col1,col2 from table1 where date1 between '2/1/2008' and '2/3/2008'
> If I look at the query plan and it says its doing an index scan, does that
> mean its actually touching each and every page of that table or will it
> somehow start at the page that has the first entry for '2/1/2008' and
> continues through the linked list at the leaf level of the pages and stops
> after it reaches '2/3/2008' ?
> How is this different if instead of a clustered index, its a non clustered
> index ?
> Thanks
>|||Uri,
In my case, its doing a clustered index scan and wanted to know if as a
result, its touching all the pages that may have dates prior to '2/1' and
after '2/3' as my query is only seeking to obtain data between '2/1/2008 and
'2/3/2008' and as you mentioned that data in the CI is ordered. Let me know
how the storage engine fetches the pages.
Also if it was a non clustered index on date1 instead of a clustered index,
i take it that the leaf level of the non clustered index is also
ordered..right ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OBuZpSscIHA.3932@.TK2MSFTNGP05.phx.gbl...
> John
> It is doing Clustered Index Scan , you meant? If you have CI on the table
> that means SQL Server logicaly orders all data by Clustered Index Key.
> It depends on the query , an optimizer may or may not decide to do a scan
> , for example the table is pretty small .
> In this case it scans index pages toread the data which ordered (logicaly)
> by date column
> In you case I'd suggest t create an index on col1,col2 and dt columns
> called COVERING index.
>
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
> The difference is that clusterd index contains at the bottom level the
> actual data , while noclustetred contains pointers to the data pages.
>
> "John Doe" <Johndoe@.jd.com> wrote in message
> news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
>> If i have only one index and that being a clustered index on say a
>> datetime column namely date1 and if my query is
>> select col1,col2 from table1 where date1 between '2/1/2008' and
>> '2/3/2008'
>> If I look at the query plan and it says its doing an index scan, does
>> that mean its actually touching each and every page of that table or will
>> it somehow start at the page that has the first entry for '2/1/2008' and
>> continues through the linked list at the leaf level of the pages and
>> stops after it reaches '2/3/2008' ?
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> Thanks
>>
>|||Let me get back to the original question. SQL Server may decide to go for
partial scan (reading a range of values) if data is ordered.
And yes, data is ordered logically by the key in both cases, difference
being that in the CI, ALL data is in the leaf level.
MC
"John Doe" <Johndoe@.jd.com> wrote in message
news:uT7oOcscIHA.748@.TK2MSFTNGP04.phx.gbl...
> Uri,
> In my case, its doing a clustered index scan and wanted to know if as a
> result, its touching all the pages that may have dates prior to '2/1' and
> after '2/3' as my query is only seeking to obtain data between '2/1/2008
> and '2/3/2008' and as you mentioned that data in the CI is ordered. Let me
> know how the storage engine fetches the pages.
> Also if it was a non clustered index on date1 instead of a clustered
> index, i take it that the leaf level of the non clustered index is also
> ordered..right ?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OBuZpSscIHA.3932@.TK2MSFTNGP05.phx.gbl...
>> John
>> It is doing Clustered Index Scan , you meant? If you have CI on the table
>> that means SQL Server logicaly orders all data by Clustered Index Key.
>> It depends on the query , an optimizer may or may not decide to do a
>> scan , for example the table is pretty small .
>> In this case it scans index pages toread the data which ordered
>> (logicaly) by date column
>> In you case I'd suggest t create an index on col1,col2 and dt columns
>> called COVERING index.
>>
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> The difference is that clusterd index contains at the bottom level the
>> actual data , while noclustetred contains pointers to the data pages.
>>
>> "John Doe" <Johndoe@.jd.com> wrote in message
>> news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
>> If i have only one index and that being a clustered index on say a
>> datetime column namely date1 and if my query is
>> select col1,col2 from table1 where date1 between '2/1/2008' and
>> '2/3/2008'
>> If I look at the query plan and it says its doing an index scan, does
>> that mean its actually touching each and every page of that table or
>> will it somehow start at the page that has the first entry for
>> '2/1/2008' and continues through the linked list at the leaf level of
>> the pages and stops after it reaches '2/3/2008' ?
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> Thanks
>>
>>
>|||John
> Also if it was a non clustered index on date1 instead of a clustered
> index, i take it that the leaf level of the non clustered index is also
> ordered..right ?
Think about CI as phone book which is 'ordered' by LastName ,so if you
want to search by Lastname is easy to traverse and get it
But what if you want to search by NCI -FirstName, then you will have to
page by page which requires an 'extra work'
I'd suggest to take some course or buying a book to underastand the
structure and behaviour.
"John Doe" <Johndoe@.jd.com> wrote in message
news:uT7oOcscIHA.748@.TK2MSFTNGP04.phx.gbl...
> Uri,
> In my case, its doing a clustered index scan and wanted to know if as a
> result, its touching all the pages that may have dates prior to '2/1' and
> after '2/3' as my query is only seeking to obtain data between '2/1/2008
> and '2/3/2008' and as you mentioned that data in the CI is ordered. Let me
> know how the storage engine fetches the pages.
> Also if it was a non clustered index on date1 instead of a clustered
> index, i take it that the leaf level of the non clustered index is also
> ordered..right ?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OBuZpSscIHA.3932@.TK2MSFTNGP05.phx.gbl...
>> John
>> It is doing Clustered Index Scan , you meant? If you have CI on the table
>> that means SQL Server logicaly orders all data by Clustered Index Key.
>> It depends on the query , an optimizer may or may not decide to do a
>> scan , for example the table is pretty small .
>> In this case it scans index pages toread the data which ordered
>> (logicaly) by date column
>> In you case I'd suggest t create an index on col1,col2 and dt columns
>> called COVERING index.
>>
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> The difference is that clusterd index contains at the bottom level the
>> actual data , while noclustetred contains pointers to the data pages.
>>
>> "John Doe" <Johndoe@.jd.com> wrote in message
>> news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
>> If i have only one index and that being a clustered index on say a
>> datetime column namely date1 and if my query is
>> select col1,col2 from table1 where date1 between '2/1/2008' and
>> '2/3/2008'
>> If I look at the query plan and it says its doing an index scan, does
>> that mean its actually touching each and every page of that table or
>> will it somehow start at the page that has the first entry for
>> '2/1/2008' and continues through the linked list at the leaf level of
>> the pages and stops after it reaches '2/3/2008' ?
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> Thanks
>>
>>
>|||On Feb 19, 12:45=A0pm, "John Doe" <John...@.jd.com> wrote:
> If i have only one index and that being a clustered index on say a datetim=e
> column namely date1 and if my query is
> select col1,col2 from table1 where date1 between '2/1/2008' and '2/3/2008'=
> If I look at the query plan and it says its doing an index scan, does that=
> mean its actually touching each and every page of that table or will it
> somehow start at the page that has the first entry for '2/1/2008' and
> continues through the linked list at the leaf level of the pages and stops=
> after it reaches '2/3/2008' ?
> How is this different if instead of a clustered index, its a non clustered=
> index ?
> Thanks
With clustered indexes records are stored on the leaf level and
therefore faster but non-clustered indexes leaf level pages have
locations to the page of searched record and therefore slower. HTH.|||If you have a clustered index, yes, it will go to the first entry in the
table with a datetime between '2/1/2008' and '2/3/2008', do a partial scan
of the clustered index and stop as soon as it finds an datetime >
'2/3/2008'. BTW, it is best in SQL Server to specify datetimes as
'yyyy-mm-ddThh:mm:ss.mmm'
or 'yyyy-mm-ddThh:mm:ss'
or 'yyyymmdd'
When you use a date format like '2/1/2008', that is Feb 1 in some locations
and Jan 2 in others. But '20080201' is Feb 1 everywhere.
For a nonclustered index, the answer is, "it depends". If the index is
nonclustered, then SQL Server knows the rows for any given value of dt might
be scattered throughout the physical table. So, for example, the first row
might be in page 1000, followed by a bunch of rows in other pages, followed
by another row in page 1000. But by this time page 1000 might not be in
memory anymore, so the page must be physically read again (this could
possibly happen many times). So SQL Server attempts to keep statistics on
how many rows are in each range and will attempt to estimate what percentage
of the table your query will return. If it is a small percentage, it will
use your index on dt, go to the first entry in the index with a date >='2/1/2008', start there and scan the index until it reaches a row with a dt
> '2/3'2008' and then stop. For each row it finds in the index on dt, it
will then use the clustered index to find the actual row and return your
values. But if it is a large percentage, then SQL Server will just scan the
entire clustered index (that is, it won't use the nonclustered index on dt
at all), because it knows that way it only has to read each physical page in
the clustered index once.
As Uri points out, your nonclustered index can be what is known as a
"covering index" for your query. This occurs when every column you need for
your query is in the index (either explicitly as part of the key or in the
INCLUDED columns or implicitly because the column is in the key of the
clustered index). When that happens, SQL Server knows it can satisify the
query requirements without ever reading from the table, so it will use the
index and only scan the part of the index it needs for the range of data you
want.
Tom
"John Doe" <Johndoe@.jd.com> wrote in message
news:uT7oOcscIHA.748@.TK2MSFTNGP04.phx.gbl...
> Uri,
> In my case, its doing a clustered index scan and wanted to know if as a
> result, its touching all the pages that may have dates prior to '2/1' and
> after '2/3' as my query is only seeking to obtain data between '2/1/2008
> and '2/3/2008' and as you mentioned that data in the CI is ordered. Let me
> know how the storage engine fetches the pages.
> Also if it was a non clustered index on date1 instead of a clustered
> index, i take it that the leaf level of the non clustered index is also
> ordered..right ?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OBuZpSscIHA.3932@.TK2MSFTNGP05.phx.gbl...
>> John
>> It is doing Clustered Index Scan , you meant? If you have CI on the table
>> that means SQL Server logicaly orders all data by Clustered Index Key.
>> It depends on the query , an optimizer may or may not decide to do a
>> scan , for example the table is pretty small .
>> In this case it scans index pages toread the data which ordered
>> (logicaly) by date column
>> In you case I'd suggest t create an index on col1,col2 and dt columns
>> called COVERING index.
>>
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> The difference is that clusterd index contains at the bottom level the
>> actual data , while noclustetred contains pointers to the data pages.
>>
>> "John Doe" <Johndoe@.jd.com> wrote in message
>> news:eXI36LscIHA.484@.TK2MSFTNGP06.phx.gbl...
>> If i have only one index and that being a clustered index on say a
>> datetime column namely date1 and if my query is
>> select col1,col2 from table1 where date1 between '2/1/2008' and
>> '2/3/2008'
>> If I look at the query plan and it says its doing an index scan, does
>> that mean its actually touching each and every page of that table or
>> will it somehow start at the page that has the first entry for
>> '2/1/2008' and continues through the linked list at the leaf level of
>> the pages and stops after it reaches '2/3/2008' ?
>> How is this different if instead of a clustered index, its a non
>> clustered index ?
>> Thanks
>>
>>
>

Index related problems? Whats happening here?

All queries for a particular table seems to be slow. It has one
clustered index on the primary key column which of data type INT and
has identity insert ON. This table has < 10000 rows and is fast with
response in all other circumstances. The clustered index is at a fill
factor of 90% and I have toyed upto 70% fillfactor.
When it is slow I ran DBCC SHOWCONTIG and there were signs of
fragmentation which didn't look very serious. The BOL says it is not
reliable for smaller tables.
I run DBCC INDEXDEFRAG on a particular database. The results suggest
that there were 72 pages and 72 pages were moved and 0 deleted. Still
no improvement in performance.
I run DBCC DBREINDEX and viola query runs fast... I am happy but what
is happening here?
All help is welcome and appreciated...
ThanksDid you do a lot of updates/inserts/deletes and you didn't update statistics?
http://sqlservercode.blogspot.com/
"MasterNone" wrote:
> All queries for a particular table seems to be slow. It has one
> clustered index on the primary key column which of data type INT and
> has identity insert ON. This table has < 10000 rows and is fast with
> response in all other circumstances. The clustered index is at a fill
> factor of 90% and I have toyed upto 70% fillfactor.
> When it is slow I ran DBCC SHOWCONTIG and there were signs of
> fragmentation which didn't look very serious. The BOL says it is not
> reliable for smaller tables.
> I run DBCC INDEXDEFRAG on a particular database. The results suggest
> that there were 72 pages and 72 pages were moved and 0 deleted. Still
> no improvement in performance.
> I run DBCC DBREINDEX and viola query runs fast... I am happy but what
> is happening here?
>
> All help is welcome and appreciated...
> Thanks
>|||I had been monitoring the inserts they are of the order of 10-11 for a
table of 7500 rows. There were the same number of updates but not to
the primary key/indexed column. Currently the Autoupdate Statistics
option is turned on.|||MasterNone wrote:
> I had been monitoring the inserts they are of the order of 10-11 for a
> table of 7500 rows. There were the same number of updates but not to
> the primary key/indexed column. Currently the Autoupdate Statistics
> option is turned on.
Please post table DDL and your slow queries. You should also look at the
query plan with QA. A common cause for the phenomenon you seem to observe
is that the index is not used at all.
Regards
robert

Index questions

Q1: When creating indexes, which is the best way:
1. Create one index for each column which needs to be indexed?
2. Create one index, which contains all the columns, which need to be indexe
d?
Q2: Does indexex affect the datafile (physical file) size a lot?Hi
http://msdn.microsoft.com/library/d...>
_05_2ri0.asp
What you are describing is a composite index.
http://www.sql-server-performance.c...ite_indexes.asp
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"David Vonasek" wrote:

> Q1: When creating indexes, which is the best way:
> 1. Create one index for each column which needs to be indexed?
> 2. Create one index, which contains all the columns, which need to be inde
xed?
> Q2: Does indexex affect the datafile (physical file) size a lot?
>|||Creating too many indexes also brings down the performance of the system as
the sorting needs to be done on the data files.
Create indexes only on those columns which you think are involved in a WHERE
or ORDER BY clause.
thanks and regards
Chandra
"David Vonasek" wrote:

> Q1: When creating indexes, which is the best way:
> 1. Create one index for each column which needs to be indexed?
> 2. Create one index, which contains all the columns, which need to be inde
xed?
> Q2: Does indexex affect the datafile (physical file) size a lot?
>|||Regarding your first question, it depends.
The first column of the index is very important. Because the optimiser
decides to use that index if your WHERE clause is querying the first column
of the index.
Sometime it is useful to have a covered index. That is a composite index
that contains all the columns required by a special query. See "Designing an
Index" in BOL for more info.
Indexes do consume space. Also, having too many indexes on an OLTP
(read/write) database hurts write performance too.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"David Vonasek" <David Vonasek@.discussions.microsoft.com> wrote in message
news:F09D8517-35D2-4F15-9526-7B161C0230A9@.microsoft.com...
Q1: When creating indexes, which is the best way:
1. Create one index for each column which needs to be indexed?
2. Create one index, which contains all the columns, which need to be
indexed?
Q2: Does indexex affect the datafile (physical file) size a lot?|||Another possibility to consider is whether or not the criteria that you
typically query on is fixed or not. For example if you really only have one
query and that query has criteria on 3 columns, then a covering index will
probably be best. If you support lots of adhoc queries and there may be
criteria on one or more of lets say 8 columns, it may make better sense to
just create 8 individual indexes and the the optimizer use index intersectio
n
in the query plans.
"Narayana Vyas Kondreddi" wrote:

> Regarding your first question, it depends.
> The first column of the index is very important. Because the optimiser
> decides to use that index if your WHERE clause is querying the first colum
n
> of the index.
> Sometime it is useful to have a covered index. That is a composite index
> that contains all the columns required by a special query. See "Designing
an
> Index" in BOL for more info.
> Indexes do consume space. Also, having too many indexes on an OLTP
> (read/write) database hurts write performance too.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "David Vonasek" <David Vonasek@.discussions.microsoft.com> wrote in message
> news:F09D8517-35D2-4F15-9526-7B161C0230A9@.microsoft.com...
> Q1: When creating indexes, which is the best way:
> 1. Create one index for each column which needs to be indexed?
> 2. Create one index, which contains all the columns, which need to be
> indexed?
> Q2: Does indexex affect the datafile (physical file) size a lot?
>
>

Index question

Does the performance increase if the same index created on
the table twice ?. Say I have a 2 nonclustered index on
the same column. Does it increase the performance or the
performance will be the same if there is only 1 index on
that column ?
Thanks.
The engine can only use one index, so no. In fact, I would bet this would
be worse off for your system... first, the engine has to consider another
option when creating a query plan, plus the extra space the additional index
will require.
Why do you think this would be better?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:eb3a01c43cef$a527b630$a301280a@.phx.gbl...
> Does the performance increase if the same index created on
> the table twice ?. Say I have a 2 nonclustered index on
> the same column. Does it increase the performance or the
> performance will be the same if there is only 1 index on
> that column ?
> Thanks.
|||I never thought it would be better. Our dumb developers
created a couple of indexes like that in the production
database and I removed the duplicates thinking it wouldn't
cause performance issues............
Thanks.........

>--Original Message--
>The engine can only use one index, so no. In fact, I
would bet this would
>be worse off for your system... first, the engine has to
consider another
>option when creating a query plan, plus the extra space
the additional index
>will require.
>Why do you think this would be better?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Jim" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:eb3a01c43cef$a527b630$a301280a@.phx.gbl...
on
>
>.
>
|||Yes bad practise - no reason for it and like Aaron said, there's the disk space too. Also, when you modify the data you have to change the data in the table and the two indexes. You should get your developers to script their indexes (and make sure they na
me them!)
Alicia
Http://www.sqlporn.co.uk

Index Question

Hello,
I have a 500,000 record table with the primary key being a bigint
identity column (clustered). I have another column (smallint) that it is not
unique and only has 20 possible values, this column is indexed in ascending
order.
When I do a select statement in the query analyzer filtering by the
smallint column, I notice that in the execution plan the index of this
column is not being used, it does only a clustered scan. Is it because the
smallint column is not unique? Other reason?
Thanks in advance...
Jose.
Most likely because of the low selectivity on that index. With a very low
number of unique values compared to the number of rows in the table, doing
an index seek is probably more expensive than a table (or clustered index)
scan. This is particularly true if the query is not "covered" by the index
in question. You could verify this by using an index hint in your query and
looking at the execution plan compared to the execution plan for the full
scan.
"Jose Ines Cantu Arrambide" <joseine@.nospam.com> wrote in message
news:%23dAp9jULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a 500,000 record table with the primary key being a bigint
> identity column (clustered). I have another column (smallint) that it is
not
> unique and only has 20 possible values, this column is indexed in
ascending
> order.
> When I do a select statement in the query analyzer filtering by the
> smallint column, I notice that in the execution plan the index of this
> column is not being used, it does only a clustered scan. Is it because the
> smallint column is not unique? Other reason?
> Thanks in advance...
> Jose.
>
|||Yes I did it and the smallint index turned out with 0% cost.
Thanks.
"Don Peterson" <no1@.nunya.com> wrote in message
news:%23bxFqwULEHA.3472@.TK2MSFTNGP09.phx.gbl...
> Most likely because of the low selectivity on that index. With a very low
> number of unique values compared to the number of rows in the table, doing
> an index seek is probably more expensive than a table (or clustered index)
> scan. This is particularly true if the query is not "covered" by the
index
> in question. You could verify this by using an index hint in your query
and[vbcol=seagreen]
> looking at the execution plan compared to the execution plan for the full
> scan.
> "Jose Ines Cantu Arrambide" <joseine@.nospam.com> wrote in message
> news:%23dAp9jULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> not
> ascending
the
>
sql

Index question

If I create two indexes for the same column, how would I know which index is
used?
for example,
create unique index UNI_INDX1 ON table1(column1 desc)
create unique index UNI_INDX1 ON table1(column1 asc)
You cannot create more than 1 index with the same name. If you run the
statement you will get the following error:
Server: Msg 1913, Level 16, State 1, Line 1
There is already an index on table 'Customers' named 'UNI_INDX1'.
If you want to see the execution plan in QA hit Ctl+k or select it from the
Query menu.
If you want to see the plan in text, use the statement
SET SHOWPLAN_ALL ON
GO
before your DML statement.
Bryan Bitzer MCP
Senior Database Administrator
Marshall & Swift / Boeckh
www.msbinfo.com
"light_wt" <light_wt@.discussions.microsoft.com> wrote in message
news:D555C184-17FB-4B5A-8F31-F659C605F2C0@.microsoft.com...
> If I create two indexes for the same column, how would I know which index
is
> used?
> for example,
> create unique index UNI_INDX1 ON table1(column1 desc)
> create unique index UNI_INDX1 ON table1(column1 asc)
>
|||In addition to Bryan's post:
There's absolutely no reason to create both ASC and DESC index on one column. SQL Server can
traverse an index in both directions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"light_wt" <light_wt@.discussions.microsoft.com> wrote in message
news:D555C184-17FB-4B5A-8F31-F659C605F2C0@.microsoft.com...
> If I create two indexes for the same column, how would I know which index is
> used?
> for example,
> create unique index UNI_INDX1 ON table1(column1 desc)
> create unique index UNI_INDX1 ON table1(column1 asc)
>
|||Bryan and Tibor,
Thanks for the idea. Bryan is right that I can't have same name for two
different index.
Well I am not understanding when, if there is a need, to create multiple
types of Index for one or muiltiple columns.
Another word, why would SQLsrvr2k allow muliple index in one column?
Ideas? Thanks again.
|||Say you have a query like:
...
ORDER BY col1 ASC, col2 DESC
In this case you'd want an index defined in the same way as your ORDER BY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"light_wt" <light_wt@.discussions.microsoft.com> wrote in message
news:86DCF615-C593-476A-89CD-7A418F8E1436@.microsoft.com...
> Bryan and Tibor,
> Thanks for the idea. Bryan is right that I can't have same name for two
> different index.
> Well I am not understanding when, if there is a need, to create multiple
> types of Index for one or muiltiple columns.
> Another word, why would SQLsrvr2k allow muliple index in one column?
> Ideas? Thanks again.
>
|||On Thu, 2 Sep 2004 08:29:06 -0700, light_wt wrote:

>Bryan and Tibor,
>Thanks for the idea. Bryan is right that I can't have same name for two
>different index.
>Well I am not understanding when, if there is a need, to create multiple
>types of Index for one or muiltiple columns.
>Another word, why would SQLsrvr2k allow muliple index in one column?
>Ideas? Thanks again.
Hi light_wt,
Two indexes on one columns is redundancy. But two indexes on a set of two
columns may be interesting. If you have an index on (colA, colB), it can
be used for queries where both colA and colB must be equal to some value;
it can also be used if only colA is known. But this index serves no
purpose if I have to find all rows where colB = some value. If I often
have to search for colB, I might wish to create another index on only
colB, or on colB plus one or more other columns.
Another reason why SQL Server MUST allow multiple index in one column is
that indexes are used to check UNIQUE and PRIMARY KEY constraints. There
are lots of scenario's where both (Col1, Col2) and (Col1, Col3) are unique
combinations.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks, Hugo. Your answer make sense.
light_wt.

Index question

Does the performance increase if the same index created on
the table twice ?. Say I have a 2 nonclustered index on
the same column. Does it increase the performance or the
performance will be the same if there is only 1 index on
that column '
Thanks.The engine can only use one index, so no. In fact, I would bet this would
be worse off for your system... first, the engine has to consider another
option when creating a query plan, plus the extra space the additional index
will require.
Why do you think this would be better?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:eb3a01c43cef$a527b630$a301280a@.phx.gbl...
> Does the performance increase if the same index created on
> the table twice ?. Say I have a 2 nonclustered index on
> the same column. Does it increase the performance or the
> performance will be the same if there is only 1 index on
> that column '
> Thanks.|||I never thought it would be better. Our dumb developers
created a couple of indexes like that in the production
database and I removed the duplicates thinking it wouldn't
cause performance issues............
Thanks.........

>--Original Message--
>The engine can only use one index, so no. In fact, I
would bet this would
>be worse off for your system... first, the engine has to
consider another
>option when creating a query plan, plus the extra space
the additional index
>will require.
>Why do you think this would be better?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Jim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:eb3a01c43cef$a527b630$a301280a@.phx.gbl...
on[vbcol=seagreen]
>
>.
>|||Yes bad practise - no reason for it and like Aaron said, there's the disk sp
ace too. Also, when you modify the data you have to change the data in the t
able and the two indexes. You should get your developers to script their ind
exes (and make sure they na
me them!)
Alicia
Http://www.sqlporn.co.uk

Index question

If I create two indexes for the same column, how would I know which index is
used?
for example,
create unique index UNI_INDX1 ON table1(column1 desc)
create unique index UNI_INDX1 ON table1(column1 asc)You cannot create more than 1 index with the same name. If you run the
statement you will get the following error:
Server: Msg 1913, Level 16, State 1, Line 1
There is already an index on table 'Customers' named 'UNI_INDX1'.
If you want to see the execution plan in QA hit Ctl+k or select it from the
Query menu.
If you want to see the plan in text, use the statement
SET SHOWPLAN_ALL ON
GO
before your DML statement.
Bryan Bitzer MCP
Senior Database Administrator
Marshall & Swift / Boeckh
www.msbinfo.com
"light_wt" <light_wt@.discussions.microsoft.com> wrote in message
news:D555C184-17FB-4B5A-8F31-F659C605F2C0@.microsoft.com...
> If I create two indexes for the same column, how would I know which index
is
> used?
> for example,
> create unique index UNI_INDX1 ON table1(column1 desc)
> create unique index UNI_INDX1 ON table1(column1 asc)
>|||In addition to Bryan's post:
There's absolutely no reason to create both ASC and DESC index on one column
. SQL Server can
traverse an index in both directions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"light_wt" <light_wt@.discussions.microsoft.com> wrote in message
news:D555C184-17FB-4B5A-8F31-F659C605F2C0@.microsoft.com...
> If I create two indexes for the same column, how would I know which index
is
> used?
> for example,
> create unique index UNI_INDX1 ON table1(column1 desc)
> create unique index UNI_INDX1 ON table1(column1 asc)
>|||Bryan and Tibor,
Thanks for the idea. Bryan is right that I can't have same name for two
different index.
Well I am not understanding when, if there is a need, to create multiple
types of Index for one or muiltiple columns.
Another word, why would SQLsrvr2k allow muliple index in one column?
Ideas? Thanks again.|||Say you have a query like:
...
ORDER BY col1 ASC, col2 DESC
In this case you'd want an index defined in the same way as your ORDER BY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"light_wt" <light_wt@.discussions.microsoft.com> wrote in message
news:86DCF615-C593-476A-89CD-7A418F8E1436@.microsoft.com...
> Bryan and Tibor,
> Thanks for the idea. Bryan is right that I can't have same name for two
> different index.
> Well I am not understanding when, if there is a need, to create multiple
> types of Index for one or muiltiple columns.
> Another word, why would SQLsrvr2k allow muliple index in one column?
> Ideas? Thanks again.
>|||On Thu, 2 Sep 2004 08:29:06 -0700, light_wt wrote:

>Bryan and Tibor,
>Thanks for the idea. Bryan is right that I can't have same name for two
>different index.
>Well I am not understanding when, if there is a need, to create multiple
>types of Index for one or muiltiple columns.
>Another word, why would SQLsrvr2k allow muliple index in one column?
>Ideas? Thanks again.
Hi light_wt,
Two indexes on one columns is redundancy. But two indexes on a set of two
columns may be interesting. If you have an index on (colA, colB), it can
be used for queries where both colA and colB must be equal to some value;
it can also be used if only colA is known. But this index serves no
purpose if I have to find all rows where colB = some value. If I often
have to search for colB, I might wish to create another index on only
colB, or on colB plus one or more other columns.
Another reason why SQL Server MUST allow multiple index in one column is
that indexes are used to check UNIQUE and PRIMARY KEY constraints. There
are lots of scenario's where both (Col1, Col2) and (Col1, Col3) are unique
combinations.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks, Hugo. Your answer make sense.
light_wt.

Index Question

Hello,
I have a 500,000 record table with the primary key being a bigint
identity column (clustered). I have another column (smallint) that it is not
unique and only has 20 possible values, this column is indexed in ascending
order.
When I do a select statement in the query analyzer filtering by the
smallint column, I notice that in the execution plan the index of this
column is not being used, it does only a clustered scan. Is it because the
smallint column is not unique? Other reason?
Thanks in advance...
Jose.Most likely because of the low selectivity on that index. With a very low
number of unique values compared to the number of rows in the table, doing
an index seek is probably more expensive than a table (or clustered index)
scan. This is particularly true if the query is not "covered" by the index
in question. You could verify this by using an index hint in your query and
looking at the execution plan compared to the execution plan for the full
scan.
"Jose Ines Cantu Arrambide" <joseine@.nospam.com> wrote in message
news:%23dAp9jULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a 500,000 record table with the primary key being a bigint
> identity column (clustered). I have another column (smallint) that it is
not
> unique and only has 20 possible values, this column is indexed in
ascending
> order.
> When I do a select statement in the query analyzer filtering by the
> smallint column, I notice that in the execution plan the index of this
> column is not being used, it does only a clustered scan. Is it because the
> smallint column is not unique? Other reason?
> Thanks in advance...
> Jose.
>|||Yes I did it and the smallint index turned out with 0% cost.
Thanks.
"Don Peterson" <no1@.nunya.com> wrote in message
news:%23bxFqwULEHA.3472@.TK2MSFTNGP09.phx.gbl...
> Most likely because of the low selectivity on that index. With a very low
> number of unique values compared to the number of rows in the table, doing
> an index seek is probably more expensive than a table (or clustered index)
> scan. This is particularly true if the query is not "covered" by the
index
> in question. You could verify this by using an index hint in your query
and
> looking at the execution plan compared to the execution plan for the full
> scan.
> "Jose Ines Cantu Arrambide" <joseine@.nospam.com> wrote in message
> news:%23dAp9jULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> not
> ascending
the[vbcol=seagreen]
>sql

Index question

If I create two indexes for the same column, how would I know which index is
used?
for example,
create unique index UNI_INDX1 ON table1(column1 desc)
create unique index UNI_INDX1 ON table1(column1 asc)You cannot create more than 1 index with the same name. If you run the
statement you will get the following error:
Server: Msg 1913, Level 16, State 1, Line 1
There is already an index on table 'Customers' named 'UNI_INDX1'.
If you want to see the execution plan in QA hit Ctl+k or select it from the
Query menu.
If you want to see the plan in text, use the statement
SET SHOWPLAN_ALL ON
GO
before your DML statement.
--
Bryan Bitzer MCP
Senior Database Administrator
Marshall & Swift / Boeckh
www.msbinfo.com
"light_wt" <light_wt@.discussions.microsoft.com> wrote in message
news:D555C184-17FB-4B5A-8F31-F659C605F2C0@.microsoft.com...
> If I create two indexes for the same column, how would I know which index
is
> used?
> for example,
> create unique index UNI_INDX1 ON table1(column1 desc)
> create unique index UNI_INDX1 ON table1(column1 asc)
>|||In addition to Bryan's post:
There's absolutely no reason to create both ASC and DESC index on one column. SQL Server can
traverse an index in both directions.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"light_wt" <light_wt@.discussions.microsoft.com> wrote in message
news:D555C184-17FB-4B5A-8F31-F659C605F2C0@.microsoft.com...
> If I create two indexes for the same column, how would I know which index is
> used?
> for example,
> create unique index UNI_INDX1 ON table1(column1 desc)
> create unique index UNI_INDX1 ON table1(column1 asc)
>|||Say you have a query like:
...
ORDER BY col1 ASC, col2 DESC
In this case you'd want an index defined in the same way as your ORDER BY.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"light_wt" <light_wt@.discussions.microsoft.com> wrote in message
news:86DCF615-C593-476A-89CD-7A418F8E1436@.microsoft.com...
> Bryan and Tibor,
> Thanks for the idea. Bryan is right that I can't have same name for two
> different index.
> Well I am not understanding when, if there is a need, to create multiple
> types of Index for one or muiltiple columns.
> Another word, why would SQLsrvr2k allow muliple index in one column?
> Ideas? Thanks again.
>|||On Thu, 2 Sep 2004 08:29:06 -0700, light_wt wrote:
>Bryan and Tibor,
>Thanks for the idea. Bryan is right that I can't have same name for two
>different index.
>Well I am not understanding when, if there is a need, to create multiple
>types of Index for one or muiltiple columns.
>Another word, why would SQLsrvr2k allow muliple index in one column?
>Ideas? Thanks again.
Hi light_wt,
Two indexes on one columns is redundancy. But two indexes on a set of two
columns may be interesting. If you have an index on (colA, colB), it can
be used for queries where both colA and colB must be equal to some value;
it can also be used if only colA is known. But this index serves no
purpose if I have to find all rows where colB = some value. If I often
have to search for colB, I might wish to create another index on only
colB, or on colB plus one or more other columns.
Another reason why SQL Server MUST allow multiple index in one column is
that indexes are used to check UNIQUE and PRIMARY KEY constraints. There
are lots of scenario's where both (Col1, Col2) and (Col1, Col3) are unique
combinations.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks, Hugo. Your answer make sense. :)
light_wt.

Index question

Does the performance increase if the same index created on
the table twice ?. Say I have a 2 nonclustered index on
the same column. Does it increase the performance or the
performance will be the same if there is only 1 index on
that column '
Thanks.The engine can only use one index, so no. In fact, I would bet this would
be worse off for your system... first, the engine has to consider another
option when creating a query plan, plus the extra space the additional index
will require.
Why do you think this would be better?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:eb3a01c43cef$a527b630$a301280a@.phx.gbl...
> Does the performance increase if the same index created on
> the table twice ?. Say I have a 2 nonclustered index on
> the same column. Does it increase the performance or the
> performance will be the same if there is only 1 index on
> that column '
> Thanks.|||I never thought it would be better. Our dumb developers
created a couple of indexes like that in the production
database and I removed the duplicates thinking it wouldn't
cause performance issues............
Thanks.........
>--Original Message--
>The engine can only use one index, so no. In fact, I
would bet this would
>be worse off for your system... first, the engine has to
consider another
>option when creating a query plan, plus the extra space
the additional index
>will require.
>Why do you think this would be better?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Jim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:eb3a01c43cef$a527b630$a301280a@.phx.gbl...
>> Does the performance increase if the same index created
on
>> the table twice ?. Say I have a 2 nonclustered index on
>> the same column. Does it increase the performance or the
>> performance will be the same if there is only 1 index on
>> that column '
>> Thanks.
>
>.
>sql

Index Question

Hello,
I have a 500,000 record table with the primary key being a bigint
identity column (clustered). I have another column (smallint) that it is not
unique and only has 20 possible values, this column is indexed in ascending
order.
When I do a select statement in the query analyzer filtering by the
smallint column, I notice that in the execution plan the index of this
column is not being used, it does only a clustered scan. Is it because the
smallint column is not unique? Other reason?
Thanks in advance...
Jose.Most likely because of the low selectivity on that index. With a very low
number of unique values compared to the number of rows in the table, doing
an index seek is probably more expensive than a table (or clustered index)
scan. This is particularly true if the query is not "covered" by the index
in question. You could verify this by using an index hint in your query and
looking at the execution plan compared to the execution plan for the full
scan.
"Jose Ines Cantu Arrambide" <joseine@.nospam.com> wrote in message
news:%23dAp9jULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a 500,000 record table with the primary key being a bigint
> identity column (clustered). I have another column (smallint) that it is
not
> unique and only has 20 possible values, this column is indexed in
ascending
> order.
> When I do a select statement in the query analyzer filtering by the
> smallint column, I notice that in the execution plan the index of this
> column is not being used, it does only a clustered scan. Is it because the
> smallint column is not unique? Other reason?
> Thanks in advance...
> Jose.
>|||Yes I did it and the smallint index turned out with 0% cost.
Thanks.
"Don Peterson" <no1@.nunya.com> wrote in message
news:%23bxFqwULEHA.3472@.TK2MSFTNGP09.phx.gbl...
> Most likely because of the low selectivity on that index. With a very low
> number of unique values compared to the number of rows in the table, doing
> an index seek is probably more expensive than a table (or clustered index)
> scan. This is particularly true if the query is not "covered" by the
index
> in question. You could verify this by using an index hint in your query
and
> looking at the execution plan compared to the execution plan for the full
> scan.
> "Jose Ines Cantu Arrambide" <joseine@.nospam.com> wrote in message
> news:%23dAp9jULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> > Hello,
> > I have a 500,000 record table with the primary key being a bigint
> > identity column (clustered). I have another column (smallint) that it is
> not
> > unique and only has 20 possible values, this column is indexed in
> ascending
> > order.
> > When I do a select statement in the query analyzer filtering by the
> > smallint column, I notice that in the execution plan the index of this
> > column is not being used, it does only a clustered scan. Is it because
the
> > smallint column is not unique? Other reason?
> >
> > Thanks in advance...
> > Jose.
> >
> >
>

Index problem and another question

create clustered index AOP_C_Idx on AOP_Master([Date])

Running this query gives the error

Server: Msg 169, Level 15, State 2, Line 2
A column has been specified more than once in the order by list. Columns in the order by list must be unique.


However I do not have any other indexes on the table. What could be the problem ?

And where did the dBForums Yak corral vanish ??Ensure that there are no duplicates in the specified column.|||No duplicates ??Can a clustered index only be unique ? What if I do not have unique values in the table ?|||Any Ideas Gurus ??|||I don't know...started a new one...|||Originally posted by Enigma
create clustered index AOP_C_Idx on AOP_Master([Date])

Running this query gives the error

Server: Msg 169, Level 15, State 2, Line 2
A column has been specified more than once in the order by list. Columns in the order by list must be unique.


However I do not have any other indexes on the table. What could be the problem ?

And where did the dBForums Yak corral vanish ??

Is that the exact sql?|||Originally posted by Enigma
No duplicates ??Can a clustered index only be unique ? What if I do not have unique values in the table ?

CREATE a non unique index?

What are you trying to do?|||I am trying to create a non unique Clustered index on a table which already exists and getting the error .

And yes this is the exact sql I am using

create clustered index AOP_C_Idx on AOP_Master([Date])|||What happens when you run

select *
from AOP_Master
order by [Date]?

Do you somehow have two date columns in the table? Can't be.|||I've had similiar strange unexplainable problems trying to create foreign keys and indexes too. I would try to create the fk or index and SQL server thought the index was already there eventhough it wasn't. I had to drop and re-create the table in order to get the fk or index to create. This is a shot in the dark but hope it helps.|||http://support.microsoft.com/default.aspx?scid=kb;en-us;293177

Here is the solution to the problem ...

I was hoping and I was right ....

IT IS A DOCUMENTED BUG|||Please post this also to SQLTeam.com for completeness|||Originally posted by Enigma
And where did the dBForums Yak corral vanish ??

I guess I have been away for a while... what is a sbForums Yak corral?