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 create. Show all posts
Showing posts with label create. Show all posts
Friday, March 30, 2012
index statistics are created when?
If the auto create stats and auto update stats is off (actually the value is null) at the db level - are stats generated when an index is recreated?
Additionaly, running the sp_autostats on the above db shows that the auto update stats are on. Where is the option to set it on for an object rather than the db?
Mike
I just read by Kalen that if the options are false (off) this overrides the table level. Still not clear on what occurs when options are <null>True when you run DBCC DBREINDEX those stats will be updated too.
SP_AUTOSTATS is used to display or change the automatic UPDATE STATISTICS setting for a specific index and statistics, or for all indexes and statistics for a given table or indexed view in the current database.
Additionaly, running the sp_autostats on the above db shows that the auto update stats are on. Where is the option to set it on for an object rather than the db?
Mike
I just read by Kalen that if the options are false (off) this overrides the table level. Still not clear on what occurs when options are <null>True when you run DBCC DBREINDEX those stats will be updated too.
SP_AUTOSTATS is used to display or change the automatic UPDATE STATISTICS setting for a specific index and statistics, or for all indexes and statistics for a given table or indexed view in the current database.
Wednesday, March 28, 2012
index scan and seek help...
hi,
I create a view within 3 tables unoin and every table has a cluster index
(corgn ,cssym).
create view vsc1
as
(select * from tableA
union
select * from tableB
union
select * from tableC)
select * from vsc1 where corgn = '2213' and cssym = '200502'
It's very weired the execution plan shows one of the tables, says tableA,
is clustered index scan instead of clustered index seek.
I recreate the view with only one table (tableA) and found the execution
plan shows a clustered index seek.
Why SQL SERVER has such a different execution plan?
Any help is very appreciated.
moash wrote:
> hi,
> I create a view within 3 tables unoin and every table has a cluster
> index (corgn ,cssym).
> create view vsc1
> as
> (select * from tableA
> union
> select * from tableB
> union
> select * from tableC)
> select * from vsc1 where corgn = '2213' and cssym = '200502'
> It's very weired the execution plan shows one of the tables, says
> tableA, is clustered index scan instead of clustered index seek.
> I recreate the view with only one table (tableA) and found the
> execution plan shows a clustered index seek.
> Why SQL SERVER has such a different execution plan?
> Any help is very appreciated.
I guess your tables differ in any of
- size
- distribution
- indexes
You would have to provide table definitions including indexes and an
outline of the volume in those tables for more concrete answers.
Kind regards
robert
|||An index scan does not always mean a full scan. If you look closely it
usually says scanning an index or a particular range of rows from the index.
If the value you chose had several rows that matched it the leaf level of
the index can be scanned to retrieve all the matching rows after the initial
seek.
Andrew J. Kelly SQL MVP
"moash" <moashPPP@.hotmail.com> wrote in message
news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
> hi,
> I create a view within 3 tables unoin and every table has a cluster index
> (corgn ,cssym).
> create view vsc1
> as
> (select * from tableA
> union
> select * from tableB
> union
> select * from tableC)
> select * from vsc1 where corgn = '2213' and cssym = '200502'
> It's very weired the execution plan shows one of the tables, says tableA,
> is clustered index scan instead of clustered index seek.
> I recreate the view with only one table (tableA) and found the execution
> plan shows a clustered index seek.
> Why SQL SERVER has such a different execution plan?
> Any help is very appreciated.
>
|||Also, just as an FYI... the union operator automatically eliminates
duplicate rows, which can be very expensive... If you know there will not be
dupe rows, or do not care, use the Union All command instead.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"moash" <moashPPP@.hotmail.com> wrote in message
news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
> hi,
> I create a view within 3 tables unoin and every table has a cluster index
> (corgn ,cssym).
> create view vsc1
> as
> (select * from tableA
> union
> select * from tableB
> union
> select * from tableC)
> select * from vsc1 where corgn = '2213' and cssym = '200502'
> It's very weired the execution plan shows one of the tables, says tableA,
> is clustered index scan instead of clustered index seek.
> I recreate the view with only one table (tableA) and found the execution
> plan shows a clustered index seek.
> Why SQL SERVER has such a different execution plan?
> Any help is very appreciated.
>
|||Actually, I import these 3 tables to another DB in the same instance,with
same view and clustered indexes ,
and I found the execution plan showed all table are index seek.
But when I try to import these 3 tables with different table name to the
same DB,
the execution paln just nothing changed , tableA is always clustered index
scan instead of clustered index seek.
My box is 4 2.80 ZEON CPU,8G RAM(AWE enabled), SQL SERVER Tranditional
Chinese SP3,WIN2003 Enterprise,RAID 5
Any help is very appreciated.
> Also, just as an FYI... the union operator automatically eliminates
> duplicate rows, which can be very expensive... If you know there will not
be[vbcol=seagreen]
> dupe rows, or do not care, use the Union All command instead.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "moash" <moashPPP@.hotmail.com> wrote in message
> news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
index[vbcol=seagreen]
tableA,[vbcol=seagreen]
execution
>
|||How I can send u these infromation?
> moash wrote:
> I guess your tables differ in any of
> - size
> - distribution
> - indexes
> You would have to provide table definitions including indexes and an
> outline of the volume in those tables for more concrete answers.
> Kind regards
> robert
>
|||Also, to add to everyone else's comments, whenever you specify something
like SELECT *, chances are if there is a "Covering" Index, the Optimizer
will weigh the cost of scanning that index verses seeking a different index
and then incuring a Bookmark Lookup. The best of the two would be a
filtered "scan" on the clustered index because all columns are covered.
Since you specified that the View should return all columns, it is a safe
bet that the optimizer chose the cluster index scan over a seek because of
the covering effect.
You should always specify every column returned, explicitly, even if it is
every column.
Make this minor modification and see if it makes any difference, even when
you call the View.
Sincerely,
Anthony Thomas
"moash" <moashPPP@.hotmail.com> wrote in message
news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
hi,
I create a view within 3 tables unoin and every table has a cluster index
(corgn ,cssym).
create view vsc1
as
(select * from tableA
union
select * from tableB
union
select * from tableC)
select * from vsc1 where corgn = '2213' and cssym = '200502'
It's very weired the execution plan shows one of the tables, says tableA,
is clustered index scan instead of clustered index seek.
I recreate the view with only one table (tableA) and found the execution
plan shows a clustered index seek.
Why SQL SERVER has such a different execution plan?
Any help is very appreciated.
I create a view within 3 tables unoin and every table has a cluster index
(corgn ,cssym).
create view vsc1
as
(select * from tableA
union
select * from tableB
union
select * from tableC)
select * from vsc1 where corgn = '2213' and cssym = '200502'
It's very weired the execution plan shows one of the tables, says tableA,
is clustered index scan instead of clustered index seek.
I recreate the view with only one table (tableA) and found the execution
plan shows a clustered index seek.
Why SQL SERVER has such a different execution plan?
Any help is very appreciated.
moash wrote:
> hi,
> I create a view within 3 tables unoin and every table has a cluster
> index (corgn ,cssym).
> create view vsc1
> as
> (select * from tableA
> union
> select * from tableB
> union
> select * from tableC)
> select * from vsc1 where corgn = '2213' and cssym = '200502'
> It's very weired the execution plan shows one of the tables, says
> tableA, is clustered index scan instead of clustered index seek.
> I recreate the view with only one table (tableA) and found the
> execution plan shows a clustered index seek.
> Why SQL SERVER has such a different execution plan?
> Any help is very appreciated.
I guess your tables differ in any of
- size
- distribution
- indexes
You would have to provide table definitions including indexes and an
outline of the volume in those tables for more concrete answers.
Kind regards
robert
|||An index scan does not always mean a full scan. If you look closely it
usually says scanning an index or a particular range of rows from the index.
If the value you chose had several rows that matched it the leaf level of
the index can be scanned to retrieve all the matching rows after the initial
seek.
Andrew J. Kelly SQL MVP
"moash" <moashPPP@.hotmail.com> wrote in message
news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
> hi,
> I create a view within 3 tables unoin and every table has a cluster index
> (corgn ,cssym).
> create view vsc1
> as
> (select * from tableA
> union
> select * from tableB
> union
> select * from tableC)
> select * from vsc1 where corgn = '2213' and cssym = '200502'
> It's very weired the execution plan shows one of the tables, says tableA,
> is clustered index scan instead of clustered index seek.
> I recreate the view with only one table (tableA) and found the execution
> plan shows a clustered index seek.
> Why SQL SERVER has such a different execution plan?
> Any help is very appreciated.
>
|||Also, just as an FYI... the union operator automatically eliminates
duplicate rows, which can be very expensive... If you know there will not be
dupe rows, or do not care, use the Union All command instead.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"moash" <moashPPP@.hotmail.com> wrote in message
news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
> hi,
> I create a view within 3 tables unoin and every table has a cluster index
> (corgn ,cssym).
> create view vsc1
> as
> (select * from tableA
> union
> select * from tableB
> union
> select * from tableC)
> select * from vsc1 where corgn = '2213' and cssym = '200502'
> It's very weired the execution plan shows one of the tables, says tableA,
> is clustered index scan instead of clustered index seek.
> I recreate the view with only one table (tableA) and found the execution
> plan shows a clustered index seek.
> Why SQL SERVER has such a different execution plan?
> Any help is very appreciated.
>
|||Actually, I import these 3 tables to another DB in the same instance,with
same view and clustered indexes ,
and I found the execution plan showed all table are index seek.
But when I try to import these 3 tables with different table name to the
same DB,
the execution paln just nothing changed , tableA is always clustered index
scan instead of clustered index seek.
My box is 4 2.80 ZEON CPU,8G RAM(AWE enabled), SQL SERVER Tranditional
Chinese SP3,WIN2003 Enterprise,RAID 5
Any help is very appreciated.
> Also, just as an FYI... the union operator automatically eliminates
> duplicate rows, which can be very expensive... If you know there will not
be[vbcol=seagreen]
> dupe rows, or do not care, use the Union All command instead.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "moash" <moashPPP@.hotmail.com> wrote in message
> news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
index[vbcol=seagreen]
tableA,[vbcol=seagreen]
execution
>
|||How I can send u these infromation?
> moash wrote:
> I guess your tables differ in any of
> - size
> - distribution
> - indexes
> You would have to provide table definitions including indexes and an
> outline of the volume in those tables for more concrete answers.
> Kind regards
> robert
>
|||Also, to add to everyone else's comments, whenever you specify something
like SELECT *, chances are if there is a "Covering" Index, the Optimizer
will weigh the cost of scanning that index verses seeking a different index
and then incuring a Bookmark Lookup. The best of the two would be a
filtered "scan" on the clustered index because all columns are covered.
Since you specified that the View should return all columns, it is a safe
bet that the optimizer chose the cluster index scan over a seek because of
the covering effect.
You should always specify every column returned, explicitly, even if it is
every column.
Make this minor modification and see if it makes any difference, even when
you call the View.
Sincerely,
Anthony Thomas
"moash" <moashPPP@.hotmail.com> wrote in message
news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
hi,
I create a view within 3 tables unoin and every table has a cluster index
(corgn ,cssym).
create view vsc1
as
(select * from tableA
union
select * from tableB
union
select * from tableC)
select * from vsc1 where corgn = '2213' and cssym = '200502'
It's very weired the execution plan shows one of the tables, says tableA,
is clustered index scan instead of clustered index seek.
I recreate the view with only one table (tableA) and found the execution
plan shows a clustered index seek.
Why SQL SERVER has such a different execution plan?
Any help is very appreciated.
index scan and seek help...
hi,
I create a view within 3 tables unoin and every table has a cluster index
(corgn ,cssym).
create view vsc1
as
(select * from tableA
union
select * from tableB
union
select * from tableC)
select * from vsc1 where corgn = '2213' and cssym = '200502'
It's very weired the execution plan shows one of the tables, says tableA,
is clustered index scan instead of clustered index seek.
I recreate the view with only one table (tableA) and found the execution
plan shows a clustered index seek.
Why SQL SERVER has such a different execution plan?
Any help is very appreciated.moash wrote:
> hi,
> I create a view within 3 tables unoin and every table has a cluster
> index (corgn ,cssym).
> create view vsc1
> as
> (select * from tableA
> union
> select * from tableB
> union
> select * from tableC)
> select * from vsc1 where corgn = '2213' and cssym = '200502'
> It's very weired the execution plan shows one of the tables, says
> tableA, is clustered index scan instead of clustered index seek.
> I recreate the view with only one table (tableA) and found the
> execution plan shows a clustered index seek.
> Why SQL SERVER has such a different execution plan?
> Any help is very appreciated.
I guess your tables differ in any of
- size
- distribution
- indexes
You would have to provide table definitions including indexes and an
outline of the volume in those tables for more concrete answers.
Kind regards
robert|||An index scan does not always mean a full scan. If you look closely it
usually says scanning an index or a particular range of rows from the index.
If the value you chose had several rows that matched it the leaf level of
the index can be scanned to retrieve all the matching rows after the initial
seek.
Andrew J. Kelly SQL MVP
"moash" <moashPPP@.hotmail.com> wrote in message
news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
> hi,
> I create a view within 3 tables unoin and every table has a cluster index
> (corgn ,cssym).
> create view vsc1
> as
> (select * from tableA
> union
> select * from tableB
> union
> select * from tableC)
> select * from vsc1 where corgn = '2213' and cssym = '200502'
> It's very weired the execution plan shows one of the tables, says tableA,
> is clustered index scan instead of clustered index seek.
> I recreate the view with only one table (tableA) and found the execution
> plan shows a clustered index seek.
> Why SQL SERVER has such a different execution plan?
> Any help is very appreciated.
>|||Also, just as an FYI... the union operator automatically eliminates
duplicate rows, which can be very expensive... If you know there will not be
dupe rows, or do not care, use the Union All command instead.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"moash" <moashPPP@.hotmail.com> wrote in message
news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
> hi,
> I create a view within 3 tables unoin and every table has a cluster index
> (corgn ,cssym).
> create view vsc1
> as
> (select * from tableA
> union
> select * from tableB
> union
> select * from tableC)
> select * from vsc1 where corgn = '2213' and cssym = '200502'
> It's very weired the execution plan shows one of the tables, says tableA,
> is clustered index scan instead of clustered index seek.
> I recreate the view with only one table (tableA) and found the execution
> plan shows a clustered index seek.
> Why SQL SERVER has such a different execution plan?
> Any help is very appreciated.
>|||Actually, I import these 3 tables to another DB in the same instance,with
same view and clustered indexes ,
and I found the execution plan showed all table are index seek.
But when I try to import these 3 tables with different table name to the
same DB,
the execution paln just nothing changed , tableA is always clustered index
scan instead of clustered index seek.
My box is 4 2.80 ZEON CPU,8G RAM(AWE enabled), SQL SERVER Tranditional
Chinese SP3,WIN2003 Enterprise,RAID 5
Any help is very appreciated.
> Also, just as an FYI... the union operator automatically eliminates
> duplicate rows, which can be very expensive... If you know there will not
be
> dupe rows, or do not care, use the Union All command instead.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "moash" <moashPPP@.hotmail.com> wrote in message
> news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
index[vbcol=seagreen]
tableA,[vbcol=seagreen]
execution[vbcol=seagreen]
>|||How I can send u these infromation?
> moash wrote:
> I guess your tables differ in any of
> - size
> - distribution
> - indexes
> You would have to provide table definitions including indexes and an
> outline of the volume in those tables for more concrete answers.
> Kind regards
> robert
>|||Also, to add to everyone else's comments, whenever you specify something
like SELECT *, chances are if there is a "Covering" Index, the Optimizer
will weigh the cost of scanning that index verses seeking a different index
and then incuring a Bookmark Lookup. The best of the two would be a
filtered "scan" on the clustered index because all columns are covered.
Since you specified that the View should return all columns, it is a safe
bet that the optimizer chose the cluster index scan over a seek because of
the covering effect.
You should always specify every column returned, explicitly, even if it is
every column.
Make this minor modification and see if it makes any difference, even when
you call the View.
Sincerely,
Anthony Thomas
"moash" <moashPPP@.hotmail.com> wrote in message
news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
hi,
I create a view within 3 tables unoin and every table has a cluster index
(corgn ,cssym).
create view vsc1
as
(select * from tableA
union
select * from tableB
union
select * from tableC)
select * from vsc1 where corgn = '2213' and cssym = '200502'
It's very weired the execution plan shows one of the tables, says tableA,
is clustered index scan instead of clustered index seek.
I recreate the view with only one table (tableA) and found the execution
plan shows a clustered index seek.
Why SQL SERVER has such a different execution plan?
Any help is very appreciated.sql
I create a view within 3 tables unoin and every table has a cluster index
(corgn ,cssym).
create view vsc1
as
(select * from tableA
union
select * from tableB
union
select * from tableC)
select * from vsc1 where corgn = '2213' and cssym = '200502'
It's very weired the execution plan shows one of the tables, says tableA,
is clustered index scan instead of clustered index seek.
I recreate the view with only one table (tableA) and found the execution
plan shows a clustered index seek.
Why SQL SERVER has such a different execution plan?
Any help is very appreciated.moash wrote:
> hi,
> I create a view within 3 tables unoin and every table has a cluster
> index (corgn ,cssym).
> create view vsc1
> as
> (select * from tableA
> union
> select * from tableB
> union
> select * from tableC)
> select * from vsc1 where corgn = '2213' and cssym = '200502'
> It's very weired the execution plan shows one of the tables, says
> tableA, is clustered index scan instead of clustered index seek.
> I recreate the view with only one table (tableA) and found the
> execution plan shows a clustered index seek.
> Why SQL SERVER has such a different execution plan?
> Any help is very appreciated.
I guess your tables differ in any of
- size
- distribution
- indexes
You would have to provide table definitions including indexes and an
outline of the volume in those tables for more concrete answers.
Kind regards
robert|||An index scan does not always mean a full scan. If you look closely it
usually says scanning an index or a particular range of rows from the index.
If the value you chose had several rows that matched it the leaf level of
the index can be scanned to retrieve all the matching rows after the initial
seek.
Andrew J. Kelly SQL MVP
"moash" <moashPPP@.hotmail.com> wrote in message
news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
> hi,
> I create a view within 3 tables unoin and every table has a cluster index
> (corgn ,cssym).
> create view vsc1
> as
> (select * from tableA
> union
> select * from tableB
> union
> select * from tableC)
> select * from vsc1 where corgn = '2213' and cssym = '200502'
> It's very weired the execution plan shows one of the tables, says tableA,
> is clustered index scan instead of clustered index seek.
> I recreate the view with only one table (tableA) and found the execution
> plan shows a clustered index seek.
> Why SQL SERVER has such a different execution plan?
> Any help is very appreciated.
>|||Also, just as an FYI... the union operator automatically eliminates
duplicate rows, which can be very expensive... If you know there will not be
dupe rows, or do not care, use the Union All command instead.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"moash" <moashPPP@.hotmail.com> wrote in message
news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
> hi,
> I create a view within 3 tables unoin and every table has a cluster index
> (corgn ,cssym).
> create view vsc1
> as
> (select * from tableA
> union
> select * from tableB
> union
> select * from tableC)
> select * from vsc1 where corgn = '2213' and cssym = '200502'
> It's very weired the execution plan shows one of the tables, says tableA,
> is clustered index scan instead of clustered index seek.
> I recreate the view with only one table (tableA) and found the execution
> plan shows a clustered index seek.
> Why SQL SERVER has such a different execution plan?
> Any help is very appreciated.
>|||Actually, I import these 3 tables to another DB in the same instance,with
same view and clustered indexes ,
and I found the execution plan showed all table are index seek.
But when I try to import these 3 tables with different table name to the
same DB,
the execution paln just nothing changed , tableA is always clustered index
scan instead of clustered index seek.
My box is 4 2.80 ZEON CPU,8G RAM(AWE enabled), SQL SERVER Tranditional
Chinese SP3,WIN2003 Enterprise,RAID 5
Any help is very appreciated.
> Also, just as an FYI... the union operator automatically eliminates
> duplicate rows, which can be very expensive... If you know there will not
be
> dupe rows, or do not care, use the Union All command instead.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "moash" <moashPPP@.hotmail.com> wrote in message
> news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
index[vbcol=seagreen]
tableA,[vbcol=seagreen]
execution[vbcol=seagreen]
>|||How I can send u these infromation?
> moash wrote:
> I guess your tables differ in any of
> - size
> - distribution
> - indexes
> You would have to provide table definitions including indexes and an
> outline of the volume in those tables for more concrete answers.
> Kind regards
> robert
>|||Also, to add to everyone else's comments, whenever you specify something
like SELECT *, chances are if there is a "Covering" Index, the Optimizer
will weigh the cost of scanning that index verses seeking a different index
and then incuring a Bookmark Lookup. The best of the two would be a
filtered "scan" on the clustered index because all columns are covered.
Since you specified that the View should return all columns, it is a safe
bet that the optimizer chose the cluster index scan over a seek because of
the covering effect.
You should always specify every column returned, explicitly, even if it is
every column.
Make this minor modification and see if it makes any difference, even when
you call the View.
Sincerely,
Anthony Thomas
"moash" <moashPPP@.hotmail.com> wrote in message
news:%238pmbE1ZFHA.1448@.TK2MSFTNGP09.phx.gbl...
hi,
I create a view within 3 tables unoin and every table has a cluster index
(corgn ,cssym).
create view vsc1
as
(select * from tableA
union
select * from tableB
union
select * from tableC)
select * from vsc1 where corgn = '2213' and cssym = '200502'
It's very weired the execution plan shows one of the tables, says tableA,
is clustered index scan instead of clustered index seek.
I recreate the view with only one table (tableA) and found the execution
plan shows a clustered index seek.
Why SQL SERVER has such a different execution plan?
Any help is very appreciated.sql
Monday, March 26, 2012
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?
>
>
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 questions
there are two types of indexes in microsoft sql server
a) clustered
b) non clustered
Question
can u create a primary key which is non clustered ?
If yes what is the syntax ?
Question
can u create a compound primary key and if yes what is the syntax ?
Please excuse me from asking basic questions ?> there are two types of indexes in microsoft sql server
> a) clustered
> b) non clustered
> Question
> can u create a primary key which is non clustered ?
> If yes what is the syntax ?
> Question
> can u create a compound primary key and if yes what is the syntax ?
>
> Please excuse me from asking basic questions ?
Yes, u can!
use tempdb
go
create table test (
tID int NOT NULL
,tText varchar(100) NOT NULL
)
ALTER TABLE test ADD
CONSTRAINT [test_PK] PRIMARY KEY NONCLUSTERED (tID)
CREATE CLUSTERED INDEX [IX_test_tText] ON [test] (tText)|||
Thanks Gary
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Index question
I have the following index:
CREATE UNIQUE INDEX [MYINDEX1] ON [dbo].[LOGIN_TABLE]([Loginid],
logindate]) ON [PRIMARY]
GO
Do I still need to create the following index ?
CREATE INDEX [MYINDEX2] ON [dbo].[LOGIN_TABLE]([Loginid]) ON [PRIMARY]
GO
Thanks for any feedback........
DXC,
SQL Server stores statistics for the more left column in the key. This index
could be used for logic expressions referencing [Loginid] or ([Loginid] and
[logindate]). If you create the second index, may be SQL Server can decide to
use it because the key is shorter than the first one, so more rows can fit in
a page and less IO operations will be required.
Try some "select" statements with just the first index. If you are ok with
the response time and execution plan selected by SQL Server then do not
create the second one. Remember, indexes help sql server to find the data
faster, but also put more load for insert, delete, and update operations.
AMB
"DXC" wrote:
> I have the following index:
> CREATE UNIQUE INDEX [MYINDEX1] ON [dbo].[LOGIN_TABLE]([Loginid],
> logindate]) ON [PRIMARY]
> GO
> Do I still need to create the following index ?
>
> CREATE INDEX [MYINDEX2] ON [dbo].[LOGIN_TABLE]([Loginid]) ON [PRIMARY]
> GO
>
> Thanks for any feedback........
|||No. MYINDEX2 is redundant. SQL Server can use MYINDEX1 if it needs to seek
on Loginid
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:34A7F75B-5178-4DD1-BD31-AFEE69C13458@.microsoft.com...
>I have the following index:
> CREATE UNIQUE INDEX [MYINDEX1] ON [dbo].[LOGIN_TABLE]([Loginid],
> logindate]) ON [PRIMARY]
> GO
> Do I still need to create the following index ?
>
> CREATE INDEX [MYINDEX2] ON [dbo].[LOGIN_TABLE]([Loginid]) ON [PRIMARY]
> GO
>
> Thanks for any feedback........
|||That's what I thought............Thanks.
"Paul Wehland" wrote:
> No. MYINDEX2 is redundant. SQL Server can use MYINDEX1 if it needs to seek
> on Loginid
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:34A7F75B-5178-4DD1-BD31-AFEE69C13458@.microsoft.com...
>
>
CREATE UNIQUE INDEX [MYINDEX1] ON [dbo].[LOGIN_TABLE]([Loginid],
logindate]) ON [PRIMARY]
GO
Do I still need to create the following index ?
CREATE INDEX [MYINDEX2] ON [dbo].[LOGIN_TABLE]([Loginid]) ON [PRIMARY]
GO
Thanks for any feedback........
DXC,
SQL Server stores statistics for the more left column in the key. This index
could be used for logic expressions referencing [Loginid] or ([Loginid] and
[logindate]). If you create the second index, may be SQL Server can decide to
use it because the key is shorter than the first one, so more rows can fit in
a page and less IO operations will be required.
Try some "select" statements with just the first index. If you are ok with
the response time and execution plan selected by SQL Server then do not
create the second one. Remember, indexes help sql server to find the data
faster, but also put more load for insert, delete, and update operations.
AMB
"DXC" wrote:
> I have the following index:
> CREATE UNIQUE INDEX [MYINDEX1] ON [dbo].[LOGIN_TABLE]([Loginid],
> logindate]) ON [PRIMARY]
> GO
> Do I still need to create the following index ?
>
> CREATE INDEX [MYINDEX2] ON [dbo].[LOGIN_TABLE]([Loginid]) ON [PRIMARY]
> GO
>
> Thanks for any feedback........
|||No. MYINDEX2 is redundant. SQL Server can use MYINDEX1 if it needs to seek
on Loginid
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:34A7F75B-5178-4DD1-BD31-AFEE69C13458@.microsoft.com...
>I have the following index:
> CREATE UNIQUE INDEX [MYINDEX1] ON [dbo].[LOGIN_TABLE]([Loginid],
> logindate]) ON [PRIMARY]
> GO
> Do I still need to create the following index ?
>
> CREATE INDEX [MYINDEX2] ON [dbo].[LOGIN_TABLE]([Loginid]) ON [PRIMARY]
> GO
>
> Thanks for any feedback........
|||That's what I thought............Thanks.
"Paul Wehland" wrote:
> No. MYINDEX2 is redundant. SQL Server can use MYINDEX1 if it needs to seek
> on Loginid
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:34A7F75B-5178-4DD1-BD31-AFEE69C13458@.microsoft.com...
>
>
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.
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
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.
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.
Friday, March 23, 2012
Index Question
Hi,
I have the following table:
create table ProjectResource
(
ProjectCode nvarchar(20) not null,
RevisionNum int not null,
ResourceID nvarchar(15) not null,
ResourceSiteURN nvarchar(128) not null,
ActiveFlag int not null
default 1
constraint CK_ProjectResource_ActiveFlag check (ActiveFlag in (1,0)),
PrimaryFlag int not null
default 0
constraint CK_ProjectResource_PrimaryFlag check (PrimaryFlag in
(1,0)),
EPMProjectUID uniqueidentifier null ,
EPMResourceUID uniqueidentifier null ,
constraint PK_ProjectResource primary key (ProjectCode, RevisionNum,
ResourceID, ResourceSiteURN)
)
You will see that the PK constraint contains the ProjectCode, RevisionNum,
ResourceID, and ResourceSiteURN columns. I've been given a request to add a
new index on the table where it contains the following columns in the order
they are given: ProjectCode, RevisionNum, ResourceID, ResourceSiteURN,
PrimaryFlag.
What are the pros and cons of creating this new index?
Thanks in advance,
DeeThe pro is that an index that includes no more than those five columns
will be covered by the non-clustered index, and will perform a bit
faster.
The con is that the index takes up considerable space, and adds a
certain amount of overhead when rows are inserted or deleted.
Roy Harvey
Beacon Falls, CT
On Tue, 13 Nov 2007 11:02:02 -0800, bpdee
<bpdee@.discussions.microsoft.com> wrote:
>Hi,
>I have the following table:
>create table ProjectResource
>(
> ProjectCode nvarchar(20) not null,
> RevisionNum int not null,
> ResourceID nvarchar(15) not null,
> ResourceSiteURN nvarchar(128) not null,
> ActiveFlag int not null
> default 1
> constraint CK_ProjectResource_ActiveFlag check (ActiveFlag in (1,0)),
> PrimaryFlag int not null
> default 0
> constraint CK_ProjectResource_PrimaryFlag check (PrimaryFlag in
>(1,0)),
> EPMProjectUID uniqueidentifier null ,
> EPMResourceUID uniqueidentifier null ,
> constraint PK_ProjectResource primary key (ProjectCode, RevisionNum,
>ResourceID, ResourceSiteURN)
>)
>You will see that the PK constraint contains the ProjectCode, RevisionNum,
>ResourceID, and ResourceSiteURN columns. I've been given a request to add a
>new index on the table where it contains the following columns in the order
>they are given: ProjectCode, RevisionNum, ResourceID, ResourceSiteURN,
>PrimaryFlag.
>What are the pros and cons of creating this new index?
>Thanks in advance,
>Dee|||Thanks, Roy, for your quick response! I was a bit concerned at first since
we already have a clustered index on four of the five columns that are in the
non-clustered index. I thought that maybe this non-clustered index is
considered as a "duplicate" index.
"Roy Harvey (SQL Server MVP)" wrote:
> The pro is that an index that includes no more than those five columns
> will be covered by the non-clustered index, and will perform a bit
> faster.
> The con is that the index takes up considerable space, and adds a
> certain amount of overhead when rows are inserted or deleted.
> Roy Harvey
> Beacon Falls, CT
>
> On Tue, 13 Nov 2007 11:02:02 -0800, bpdee
> <bpdee@.discussions.microsoft.com> wrote:
> >Hi,
> >
> >I have the following table:
> >
> >create table ProjectResource
> >(
> > ProjectCode nvarchar(20) not null,
> > RevisionNum int not null,
> > ResourceID nvarchar(15) not null,
> > ResourceSiteURN nvarchar(128) not null,
> > ActiveFlag int not null
> > default 1
> > constraint CK_ProjectResource_ActiveFlag check (ActiveFlag in (1,0)),
> > PrimaryFlag int not null
> > default 0
> > constraint CK_ProjectResource_PrimaryFlag check (PrimaryFlag in
> >(1,0)),
> > EPMProjectUID uniqueidentifier null ,
> > EPMResourceUID uniqueidentifier null ,
> > constraint PK_ProjectResource primary key (ProjectCode, RevisionNum,
> >ResourceID, ResourceSiteURN)
> >)
> >
> >You will see that the PK constraint contains the ProjectCode, RevisionNum,
> >ResourceID, and ResourceSiteURN columns. I've been given a request to add a
> >new index on the table where it contains the following columns in the order
> >they are given: ProjectCode, RevisionNum, ResourceID, ResourceSiteURN,
> >PrimaryFlag.
> >
> >What are the pros and cons of creating this new index?
> >
> >Thanks in advance,
> >Dee
>|||On Tue, 13 Nov 2007 11:31:02 -0800, bpdee
<bpdee@.discussions.microsoft.com> wrote:
>Thanks, Roy, for your quick response! I was a bit concerned at first since
>we already have a clustered index on four of the five columns that are in the
>non-clustered index. I thought that maybe this non-clustered index is
>considered as a "duplicate" index.
I would not normally set up an index like that unless there was a
specific need for a covered index that it satisfied. Other than that
it really serves no purpose at all.
Roy Harvey
Beacon Falls, CT|||I agree with Roy. When the index key is narrow, and the row size
relatively wide, then such a "duplicate" index could be useful for
covering queries. However, in this case the row is only 36 bytes wider
than the index key of the "duplicate" index, which is probably not even
50% larger than the key size.
Had the primary key just been one int, then IMO it would have been a
different matter. As it is, I think adding this extra index is a waste
of space and will cause unnecessary contention for
inserts/updates/deletes.
BTW: I wonder why the two "Flag" columns are defined as int, and not as
something like tinyint.
--
Gert-Jan
bpdee wrote:
> Thanks, Roy, for your quick response! I was a bit concerned at first since
> we already have a clustered index on four of the five columns that are in the
> non-clustered index. I thought that maybe this non-clustered index is
> considered as a "duplicate" index.
> "Roy Harvey (SQL Server MVP)" wrote:
> > The pro is that an index that includes no more than those five columns
> > will be covered by the non-clustered index, and will perform a bit
> > faster.
> >
> > The con is that the index takes up considerable space, and adds a
> > certain amount of overhead when rows are inserted or deleted.
> >
> > Roy Harvey
> > Beacon Falls, CT
> >
> >
> > On Tue, 13 Nov 2007 11:02:02 -0800, bpdee
> > <bpdee@.discussions.microsoft.com> wrote:
> >
> > >Hi,
> > >
> > >I have the following table:
> > >
> > >create table ProjectResource
> > >(
> > > ProjectCode nvarchar(20) not null,
> > > RevisionNum int not null,
> > > ResourceID nvarchar(15) not null,
> > > ResourceSiteURN nvarchar(128) not null,
> > > ActiveFlag int not null
> > > default 1
> > > constraint CK_ProjectResource_ActiveFlag check (ActiveFlag in (1,0)),
> > > PrimaryFlag int not null
> > > default 0
> > > constraint CK_ProjectResource_PrimaryFlag check (PrimaryFlag in
> > >(1,0)),
> > > EPMProjectUID uniqueidentifier null ,
> > > EPMResourceUID uniqueidentifier null ,
> > > constraint PK_ProjectResource primary key (ProjectCode, RevisionNum,
> > >ResourceID, ResourceSiteURN)
> > >)
> > >
> > >You will see that the PK constraint contains the ProjectCode, RevisionNum,
> > >ResourceID, and ResourceSiteURN columns. I've been given a request to add a
> > >new index on the table where it contains the following columns in the order
> > >they are given: ProjectCode, RevisionNum, ResourceID, ResourceSiteURN,
> > >PrimaryFlag.
> > >
> > >What are the pros and cons of creating this new index?
> > >
> > >Thanks in advance,
> > >Dee
> >
I have the following table:
create table ProjectResource
(
ProjectCode nvarchar(20) not null,
RevisionNum int not null,
ResourceID nvarchar(15) not null,
ResourceSiteURN nvarchar(128) not null,
ActiveFlag int not null
default 1
constraint CK_ProjectResource_ActiveFlag check (ActiveFlag in (1,0)),
PrimaryFlag int not null
default 0
constraint CK_ProjectResource_PrimaryFlag check (PrimaryFlag in
(1,0)),
EPMProjectUID uniqueidentifier null ,
EPMResourceUID uniqueidentifier null ,
constraint PK_ProjectResource primary key (ProjectCode, RevisionNum,
ResourceID, ResourceSiteURN)
)
You will see that the PK constraint contains the ProjectCode, RevisionNum,
ResourceID, and ResourceSiteURN columns. I've been given a request to add a
new index on the table where it contains the following columns in the order
they are given: ProjectCode, RevisionNum, ResourceID, ResourceSiteURN,
PrimaryFlag.
What are the pros and cons of creating this new index?
Thanks in advance,
DeeThe pro is that an index that includes no more than those five columns
will be covered by the non-clustered index, and will perform a bit
faster.
The con is that the index takes up considerable space, and adds a
certain amount of overhead when rows are inserted or deleted.
Roy Harvey
Beacon Falls, CT
On Tue, 13 Nov 2007 11:02:02 -0800, bpdee
<bpdee@.discussions.microsoft.com> wrote:
>Hi,
>I have the following table:
>create table ProjectResource
>(
> ProjectCode nvarchar(20) not null,
> RevisionNum int not null,
> ResourceID nvarchar(15) not null,
> ResourceSiteURN nvarchar(128) not null,
> ActiveFlag int not null
> default 1
> constraint CK_ProjectResource_ActiveFlag check (ActiveFlag in (1,0)),
> PrimaryFlag int not null
> default 0
> constraint CK_ProjectResource_PrimaryFlag check (PrimaryFlag in
>(1,0)),
> EPMProjectUID uniqueidentifier null ,
> EPMResourceUID uniqueidentifier null ,
> constraint PK_ProjectResource primary key (ProjectCode, RevisionNum,
>ResourceID, ResourceSiteURN)
>)
>You will see that the PK constraint contains the ProjectCode, RevisionNum,
>ResourceID, and ResourceSiteURN columns. I've been given a request to add a
>new index on the table where it contains the following columns in the order
>they are given: ProjectCode, RevisionNum, ResourceID, ResourceSiteURN,
>PrimaryFlag.
>What are the pros and cons of creating this new index?
>Thanks in advance,
>Dee|||Thanks, Roy, for your quick response! I was a bit concerned at first since
we already have a clustered index on four of the five columns that are in the
non-clustered index. I thought that maybe this non-clustered index is
considered as a "duplicate" index.
"Roy Harvey (SQL Server MVP)" wrote:
> The pro is that an index that includes no more than those five columns
> will be covered by the non-clustered index, and will perform a bit
> faster.
> The con is that the index takes up considerable space, and adds a
> certain amount of overhead when rows are inserted or deleted.
> Roy Harvey
> Beacon Falls, CT
>
> On Tue, 13 Nov 2007 11:02:02 -0800, bpdee
> <bpdee@.discussions.microsoft.com> wrote:
> >Hi,
> >
> >I have the following table:
> >
> >create table ProjectResource
> >(
> > ProjectCode nvarchar(20) not null,
> > RevisionNum int not null,
> > ResourceID nvarchar(15) not null,
> > ResourceSiteURN nvarchar(128) not null,
> > ActiveFlag int not null
> > default 1
> > constraint CK_ProjectResource_ActiveFlag check (ActiveFlag in (1,0)),
> > PrimaryFlag int not null
> > default 0
> > constraint CK_ProjectResource_PrimaryFlag check (PrimaryFlag in
> >(1,0)),
> > EPMProjectUID uniqueidentifier null ,
> > EPMResourceUID uniqueidentifier null ,
> > constraint PK_ProjectResource primary key (ProjectCode, RevisionNum,
> >ResourceID, ResourceSiteURN)
> >)
> >
> >You will see that the PK constraint contains the ProjectCode, RevisionNum,
> >ResourceID, and ResourceSiteURN columns. I've been given a request to add a
> >new index on the table where it contains the following columns in the order
> >they are given: ProjectCode, RevisionNum, ResourceID, ResourceSiteURN,
> >PrimaryFlag.
> >
> >What are the pros and cons of creating this new index?
> >
> >Thanks in advance,
> >Dee
>|||On Tue, 13 Nov 2007 11:31:02 -0800, bpdee
<bpdee@.discussions.microsoft.com> wrote:
>Thanks, Roy, for your quick response! I was a bit concerned at first since
>we already have a clustered index on four of the five columns that are in the
>non-clustered index. I thought that maybe this non-clustered index is
>considered as a "duplicate" index.
I would not normally set up an index like that unless there was a
specific need for a covered index that it satisfied. Other than that
it really serves no purpose at all.
Roy Harvey
Beacon Falls, CT|||I agree with Roy. When the index key is narrow, and the row size
relatively wide, then such a "duplicate" index could be useful for
covering queries. However, in this case the row is only 36 bytes wider
than the index key of the "duplicate" index, which is probably not even
50% larger than the key size.
Had the primary key just been one int, then IMO it would have been a
different matter. As it is, I think adding this extra index is a waste
of space and will cause unnecessary contention for
inserts/updates/deletes.
BTW: I wonder why the two "Flag" columns are defined as int, and not as
something like tinyint.
--
Gert-Jan
bpdee wrote:
> Thanks, Roy, for your quick response! I was a bit concerned at first since
> we already have a clustered index on four of the five columns that are in the
> non-clustered index. I thought that maybe this non-clustered index is
> considered as a "duplicate" index.
> "Roy Harvey (SQL Server MVP)" wrote:
> > The pro is that an index that includes no more than those five columns
> > will be covered by the non-clustered index, and will perform a bit
> > faster.
> >
> > The con is that the index takes up considerable space, and adds a
> > certain amount of overhead when rows are inserted or deleted.
> >
> > Roy Harvey
> > Beacon Falls, CT
> >
> >
> > On Tue, 13 Nov 2007 11:02:02 -0800, bpdee
> > <bpdee@.discussions.microsoft.com> wrote:
> >
> > >Hi,
> > >
> > >I have the following table:
> > >
> > >create table ProjectResource
> > >(
> > > ProjectCode nvarchar(20) not null,
> > > RevisionNum int not null,
> > > ResourceID nvarchar(15) not null,
> > > ResourceSiteURN nvarchar(128) not null,
> > > ActiveFlag int not null
> > > default 1
> > > constraint CK_ProjectResource_ActiveFlag check (ActiveFlag in (1,0)),
> > > PrimaryFlag int not null
> > > default 0
> > > constraint CK_ProjectResource_PrimaryFlag check (PrimaryFlag in
> > >(1,0)),
> > > EPMProjectUID uniqueidentifier null ,
> > > EPMResourceUID uniqueidentifier null ,
> > > constraint PK_ProjectResource primary key (ProjectCode, RevisionNum,
> > >ResourceID, ResourceSiteURN)
> > >)
> > >
> > >You will see that the PK constraint contains the ProjectCode, RevisionNum,
> > >ResourceID, and ResourceSiteURN columns. I've been given a request to add a
> > >new index on the table where it contains the following columns in the order
> > >they are given: ProjectCode, RevisionNum, ResourceID, ResourceSiteURN,
> > >PrimaryFlag.
> > >
> > >What are the pros and cons of creating this new index?
> > >
> > >Thanks in advance,
> > >Dee
> >
Index question
I have the following index:
CREATE UNIQUE INDEX [MYINDEX1] ON [dbo].[LOGIN_TABLE]([Loginid],
logindate]) ON [PRIMARY]
GO
Do I still need to create the following index ?
CREATE INDEX [MYINDEX2] ON [dbo].[LOGIN_TABLE]([Loginid]) ON [PRIMARY]
GO
Thanks for any feedback........DXC,
SQL Server stores statistics for the more left column in the key. This index
could be used for logic expressions referencing [Loginid] or ([Loginid] and
[logindate]). If you create the second index, may be SQL Server can decide to
use it because the key is shorter than the first one, so more rows can fit in
a page and less IO operations will be required.
Try some "select" statements with just the first index. If you are ok with
the response time and execution plan selected by SQL Server then do not
create the second one. Remember, indexes help sql server to find the data
faster, but also put more load for insert, delete, and update operations.
AMB
"DXC" wrote:
> I have the following index:
> CREATE UNIQUE INDEX [MYINDEX1] ON [dbo].[LOGIN_TABLE]([Loginid],
> logindate]) ON [PRIMARY]
> GO
> Do I still need to create the following index ?
>
> CREATE INDEX [MYINDEX2] ON [dbo].[LOGIN_TABLE]([Loginid]) ON [PRIMARY]
> GO
>
> Thanks for any feedback........|||No. MYINDEX2 is redundant. SQL Server can use MYINDEX1 if it needs to seek
on Loginid
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:34A7F75B-5178-4DD1-BD31-AFEE69C13458@.microsoft.com...
>I have the following index:
> CREATE UNIQUE INDEX [MYINDEX1] ON [dbo].[LOGIN_TABLE]([Loginid],
> logindate]) ON [PRIMARY]
> GO
> Do I still need to create the following index ?
>
> CREATE INDEX [MYINDEX2] ON [dbo].[LOGIN_TABLE]([Loginid]) ON [PRIMARY]
> GO
>
> Thanks for any feedback........|||That's what I thought............Thanks.
"Paul Wehland" wrote:
> No. MYINDEX2 is redundant. SQL Server can use MYINDEX1 if it needs to seek
> on Loginid
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:34A7F75B-5178-4DD1-BD31-AFEE69C13458@.microsoft.com...
> >I have the following index:
> >
> > CREATE UNIQUE INDEX [MYINDEX1] ON [dbo].[LOGIN_TABLE]([Loginid],
> > logindate]) ON [PRIMARY]
> > GO
> >
> > Do I still need to create the following index ?
> >
> >
> > CREATE INDEX [MYINDEX2] ON [dbo].[LOGIN_TABLE]([Loginid]) ON [PRIMARY]
> > GO
> >
> >
> > Thanks for any feedback........
>
>sql
CREATE UNIQUE INDEX [MYINDEX1] ON [dbo].[LOGIN_TABLE]([Loginid],
logindate]) ON [PRIMARY]
GO
Do I still need to create the following index ?
CREATE INDEX [MYINDEX2] ON [dbo].[LOGIN_TABLE]([Loginid]) ON [PRIMARY]
GO
Thanks for any feedback........DXC,
SQL Server stores statistics for the more left column in the key. This index
could be used for logic expressions referencing [Loginid] or ([Loginid] and
[logindate]). If you create the second index, may be SQL Server can decide to
use it because the key is shorter than the first one, so more rows can fit in
a page and less IO operations will be required.
Try some "select" statements with just the first index. If you are ok with
the response time and execution plan selected by SQL Server then do not
create the second one. Remember, indexes help sql server to find the data
faster, but also put more load for insert, delete, and update operations.
AMB
"DXC" wrote:
> I have the following index:
> CREATE UNIQUE INDEX [MYINDEX1] ON [dbo].[LOGIN_TABLE]([Loginid],
> logindate]) ON [PRIMARY]
> GO
> Do I still need to create the following index ?
>
> CREATE INDEX [MYINDEX2] ON [dbo].[LOGIN_TABLE]([Loginid]) ON [PRIMARY]
> GO
>
> Thanks for any feedback........|||No. MYINDEX2 is redundant. SQL Server can use MYINDEX1 if it needs to seek
on Loginid
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:34A7F75B-5178-4DD1-BD31-AFEE69C13458@.microsoft.com...
>I have the following index:
> CREATE UNIQUE INDEX [MYINDEX1] ON [dbo].[LOGIN_TABLE]([Loginid],
> logindate]) ON [PRIMARY]
> GO
> Do I still need to create the following index ?
>
> CREATE INDEX [MYINDEX2] ON [dbo].[LOGIN_TABLE]([Loginid]) ON [PRIMARY]
> GO
>
> Thanks for any feedback........|||That's what I thought............Thanks.
"Paul Wehland" wrote:
> No. MYINDEX2 is redundant. SQL Server can use MYINDEX1 if it needs to seek
> on Loginid
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:34A7F75B-5178-4DD1-BD31-AFEE69C13458@.microsoft.com...
> >I have the following index:
> >
> > CREATE UNIQUE INDEX [MYINDEX1] ON [dbo].[LOGIN_TABLE]([Loginid],
> > logindate]) ON [PRIMARY]
> > GO
> >
> > Do I still need to create the following index ?
> >
> >
> > CREATE INDEX [MYINDEX2] ON [dbo].[LOGIN_TABLE]([Loginid]) ON [PRIMARY]
> > GO
> >
> >
> > Thanks for any feedback........
>
>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.
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 properties
Hi group.
Where can i find in the system tables of the SQL 2000, all the propreties of
an index. For example, if a create an index unique but not constraint and
'Ignore duplicate key' checked?. Where the SQL 2000 store this information?
Thanks
Nuno Teixeira
PortugalLook at the system function INDEXPROPERTY in Books Online.
"Nuno Teixeira" <NunoTeixeira@.discussions.microsoft.com> wrote in message
news:0A1884EE-1B7D-460C-B6AC-6F0582A5B7E7@.microsoft.com...
> Hi group.
> Where can i find in the system tables of the SQL 2000, all the propreties
> of
> an index. For example, if a create an index unique but not constraint and
> 'Ignore duplicate key' checked?. Where the SQL 2000 store this
> information?
> Thanks
> Nuno Teixeira
> Portugal|||See if this helps:
http://www.microsoft.com/communitie...7d47&sloc=en-us
AMB
"Nuno Teixeira" wrote:
> Hi group.
> Where can i find in the system tables of the SQL 2000, all the propreties
of
> an index. For example, if a create an index unique but not constraint and
> 'Ignore duplicate key' checked?. Where the SQL 2000 store this information
?
> Thanks
> Nuno Teixeira
> Portugal|||Forget this.
AMB
"Alejandro Mesa" wrote:
> See if this helps:
> http://www.microsoft.com/communitie...7d47&sloc=en-us
>
> AMB
> "Nuno Teixeira" wrote:
>|||Maybe this can help:
http://milambda.blogspot.com/2005/0...-with-kick.html
ML
Where can i find in the system tables of the SQL 2000, all the propreties of
an index. For example, if a create an index unique but not constraint and
'Ignore duplicate key' checked?. Where the SQL 2000 store this information?
Thanks
Nuno Teixeira
PortugalLook at the system function INDEXPROPERTY in Books Online.
"Nuno Teixeira" <NunoTeixeira@.discussions.microsoft.com> wrote in message
news:0A1884EE-1B7D-460C-B6AC-6F0582A5B7E7@.microsoft.com...
> Hi group.
> Where can i find in the system tables of the SQL 2000, all the propreties
> of
> an index. For example, if a create an index unique but not constraint and
> 'Ignore duplicate key' checked?. Where the SQL 2000 store this
> information?
> Thanks
> Nuno Teixeira
> Portugal|||See if this helps:
http://www.microsoft.com/communitie...7d47&sloc=en-us
AMB
"Nuno Teixeira" wrote:
> Hi group.
> Where can i find in the system tables of the SQL 2000, all the propreties
of
> an index. For example, if a create an index unique but not constraint and
> 'Ignore duplicate key' checked?. Where the SQL 2000 store this information
?
> Thanks
> Nuno Teixeira
> Portugal|||Forget this.
AMB
"Alejandro Mesa" wrote:
> See if this helps:
> http://www.microsoft.com/communitie...7d47&sloc=en-us
>
> AMB
> "Nuno Teixeira" wrote:
>|||Maybe this can help:
http://milambda.blogspot.com/2005/0...-with-kick.html
ML
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?
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?
Index Problem
Greetings,
In a SQL Query window (2000) I can create a #temp table and then create an index on the # temp table with this code:
create clustered index IX_UpinNbr on #temp20 (UpinNbr)
go
However when I try to compile the same code within a Stored Procedure, I get the following error:
Msg 208, Level 16, State 0, Line 4
Invalid object name '#temp20'.
I realize that the #temp20 table is not a physical table in my database. Can you put an index on a #temp table within a stored procedure?
Thanks!
Tom
Can you post the exact code you are using?
I wonder if you are altering that temporary table after cteating it.
Code Snippet
use northwind
go
createprocedure dbo.p1
as
setnocounton
createtable #t (
Record# intnotnullunique,
DateStamp datetimenotnull
)
createuniqueclusteredindex #t_Record#_u_c_ix
on #t(Record#)
insertinto #t values(1,'6/14/2007 1:00')
insertinto #t values(2,'6/14/2007 2:00')
insertinto #t values(3,'6/14/2007 2:10')
insertinto #t values(4,'6/14/2007 5:10')
select*from #t
droptable #t
go
exec dbo.p1
go
dropprocedure dbo.p1
go
AMB
|||Here is the entire Stored Procedure script:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTERPROCEDURE [dbo].[proc_san_prov_hr_rpt] AS
-- Created 06/13/2007 by TLeach, CR# C7095, PDAT 1011 Sanctioned Provider Report - Hospital Roster.
-- Hospital Roster
Select a.temp_tracking_number,
a.TaxIDNumber,
a.Section1011IdentificationNbr,
a.LegalBusinesssName,
a.DBAName,
a.AuthorizedRepFirstName,
a.AuthorizedRepLastName,
a.PhysicalAddressLine1,
a.PhysicalAddressLine2,
a.PhysicalCity,
a.PhysicalState,
a.PhysicalZipCode,
b.UpinNbr,
b.PhysicianName
Into #temp20
From dbo.EnrollmentApplication a, dbo.HospitalRoster b
Where a.StatusID ='6'and
a.SecondaryStatus ='FA'and
a.ID = B.ApplicationID
go
createclusteredindex IX_UpinNbr on #temp20 (UpinNbr)
go
-- Now join #temp20 to the cumulative table to check for matches.
-- #temp21 will have PhysicianName and busname matches.
Select a.temp_tracking_number AS'PDAT Temptracknbr',
a.TaxIDNumber AS'PDAT TaxIDNumber',
a.Section1011IdentificationNbr AS'PDAT Section1011nbr',
b.SSN AS'CMS SSN',
a.LegalBusinesssName AS'PDAT LegalBusinessName',
a.DBAName AS'PDAT DBAName',
a.AuthorizedRepFirstName AS'PDAT AuthorizedRepFirstName',
a.AuthorizedRepLastName AS'PDAT AuthorizedRepLastName',
a.PhysicalAddressLine1 AS'PDAT PhysicalAddressLine1',
a.PhysicalAddressLine2 AS'PDAT PhysicalAddressLine2',
a.PhysicalCity AS'PDAT PhysicalCity',
a.PhysicalState AS'PDAT PhysicalState',
a.PhysicalZipCode AS'PDAT PhysicalZipCode',
a.UpinNbr AS'PDAT UPINNbr',
a.PhysicianName AS'PDAT PhysicianName',
'HR Physician Name'AS'Matched On'
Into #temp21
From #temp20 a,
dbo.[0406Cumulative] b
Where(a.PhysicianName = b.busname)
-- #temp22 will have upin matches. (11)
Select a.temp_tracking_number AS'PDAT Temptracknbr',
a.TaxIDNumber AS'PDAT TaxIDNumber',
a.Section1011IdentificationNbr AS'PDAT Section1011nbr',
b.SSN AS'CMS SSN',
a.LegalBusinesssName AS'PDAT LegalBusinessName',
a.DBAName AS'PDAT DBAName',
a.AuthorizedRepFirstName AS'PDAT AuthorizedRepFirstName',
a.AuthorizedRepLastName AS'PDAT AuthorizedRepLastName',
a.PhysicalAddressLine1 AS'PDAT PhysicalAddressLine1',
a.PhysicalAddressLine2 AS'PDAT PhysicalAddressLine2',
a.PhysicalCity AS'PDAT PhysicalCity',
a.PhysicalState AS'PDAT PhysicalState',
a.PhysicalZipCode AS'PDAT PhysicalZipCode',
a.UpinNbr AS'PDAT UPINNbr',
a.PhysicianName AS'PDAT PhysicianName',
'HR Upin'AS'Matched On'
Into #temp22
From #temp20 a,
dbo.[0406Cumulative] b
Where(a.UpinNbr = b.Upin)
Select*
Into #temp23
From #temp21
Union
Select*
From #temp22
Select*from #temp23
Here is the exact error message when I cry to create:
Msg 1906, Level 11, State 1, Line 2
Cannot create an index on '#temp20', because this table does not exist in database 'PDAT_1011'.
Msg 208, Level 16, State 0, Line 4
Invalid object name '#temp20'.
Thanks!
|||When the code is compiled, the temp table does not yet exist -since it is created by the SELECT...INTO.
IF you want to create the index, then CREATE the table first.
|||Thanks for the help.|||Why are you using "go" inside the sp?
...
Select a.temp_tracking_number,
a.TaxIDNumber,
a.Section1011IdentificationNbr,
a.LegalBusinesssName,
a.DBAName,
a.AuthorizedRepFirstName,
a.AuthorizedRepLastName,
a.PhysicalAddressLine1,
a.PhysicalAddressLine2,
a.PhysicalCity,
a.PhysicalState,
a.PhysicalZipCode,
b.UpinNbr,
b.PhysicianName
Into #temp20
From dbo.EnrollmentApplication a, dbo.HospitalRoster b
Where a.StatusID ='6'and
a.SecondaryStatus ='FA'and
a.ID = B.ApplicationID
createclusteredindex IX_UpinNbr on #temp20 (UpinNbr)
...
AMB
|||Hi Arnie,
Not really, see example.
createprocedure dbo.p1
as
selectcast(1 asint)as c1
into #t
createindex #t_c1_nu_nc_ix
on #t(c1)
select*from #t
droptable #t
go
exec dbo.p1
go
dropprocedure dbo.p1
go
The problem with his code is that he has a couple of "GO" in the script. The sp is altered an inmediately it is trying to create an index in a table that does not exists.
AMB
|||No reason, forgot to take it out. Started as a .sql script...|||I commented out the 'GO' statements and the Stored Procedure compiled cleanly.
Thanks for the help!
|||I took out the 'GO' statements and the stored procedure compiled and ran cleanly.
Thanks for the help!
|||Yes, I noticed the GO statements, THAT is why the #temp table is not created. He just created the sproc with the SELECT...INTO. Then when running the CREATE INDEX statement, the #temp table did not exist. -I think I said something about it not in existance.Wednesday, March 21, 2012
Index on Views ...
I know that we can create indexes on views (Indexed views) in sql 2k. I am
trying to understand what would be the reason for creating a index on a view
.
Anyway a view is a virtual table and if there is an index on those table
won't it be enough? Hope somebody could make me understand this.
Regards
PradeepHi
I think you missed the concept of INDEXED VIEWS
http://www.sql-server-performance.com/indexed_views.asp
In addition please take a look at Steve Kass's scipt to ensure that using
an indexe view SQL Server creates more efficient execution plan
create table T (
i int,
filler char(1000) default 'abc'
)
go
create view T_count with schemabinding as
select
cast(i as bit) as val,
count_big(*) T_count
from dbo.T group by cast(i as bit)
go
create unique clustered index T_count_uci on T_count(val)
go
insert into T(i)
select OrderID
from Northwind..[Order Details]
go
set statistics io on
select count(*) from T
go
select sum(T_count) from T_count with (noexpand)
go
set statistics io off
-- uses an efficient query plan on the materialized view
go
drop view T_count
drop table T
"SqlBeginner" <SqlBeginner@.discussions.microsoft.com> wrote in message
news:F47FC536-3817-4DCE-A1F5-8E1C077ACDCF@.microsoft.com...
>I know that we can create indexes on views (Indexed views) in sql 2k. I am
> trying to understand what would be the reason for creating a index on a
> view.
> Anyway a view is a virtual table and if there is an index on those table
> won't it be enough? Hope somebody could make me understand this.
> Regards
> Pradeep|||Thanks for ur response Uri. I would go through that link.
Regards
Pradeep
"Uri Dimant" wrote:
> Hi
> I think you missed the concept of INDEXED VIEWS
> http://www.sql-server-performance.com/indexed_views.asp
>
> In addition please take a look at Steve Kass's scipt to ensure that using
> an indexe view SQL Server creates more efficient execution plan
> create table T (
> i int,
> filler char(1000) default 'abc'
> )
> go
> create view T_count with schemabinding as
> select
> cast(i as bit) as val,
> count_big(*) T_count
> from dbo.T group by cast(i as bit)
> go
> create unique clustered index T_count_uci on T_count(val)
> go
> insert into T(i)
> select OrderID
> from Northwind..[Order Details]
> go
> set statistics io on
> select count(*) from T
> go
> select sum(T_count) from T_count with (noexpand)
> go
> set statistics io off
> -- uses an efficient query plan on the materialized view
> go
> drop view T_count
> drop table T
>
>
> "SqlBeginner" <SqlBeginner@.discussions.microsoft.com> wrote in message
> news:F47FC536-3817-4DCE-A1F5-8E1C077ACDCF@.microsoft.com...
>
>
trying to understand what would be the reason for creating a index on a view
.
Anyway a view is a virtual table and if there is an index on those table
won't it be enough? Hope somebody could make me understand this.
Regards
PradeepHi
I think you missed the concept of INDEXED VIEWS
http://www.sql-server-performance.com/indexed_views.asp
In addition please take a look at Steve Kass's scipt to ensure that using
an indexe view SQL Server creates more efficient execution plan
create table T (
i int,
filler char(1000) default 'abc'
)
go
create view T_count with schemabinding as
select
cast(i as bit) as val,
count_big(*) T_count
from dbo.T group by cast(i as bit)
go
create unique clustered index T_count_uci on T_count(val)
go
insert into T(i)
select OrderID
from Northwind..[Order Details]
go
set statistics io on
select count(*) from T
go
select sum(T_count) from T_count with (noexpand)
go
set statistics io off
-- uses an efficient query plan on the materialized view
go
drop view T_count
drop table T
"SqlBeginner" <SqlBeginner@.discussions.microsoft.com> wrote in message
news:F47FC536-3817-4DCE-A1F5-8E1C077ACDCF@.microsoft.com...
>I know that we can create indexes on views (Indexed views) in sql 2k. I am
> trying to understand what would be the reason for creating a index on a
> view.
> Anyway a view is a virtual table and if there is an index on those table
> won't it be enough? Hope somebody could make me understand this.
> Regards
> Pradeep|||Thanks for ur response Uri. I would go through that link.
Regards
Pradeep
"Uri Dimant" wrote:
> Hi
> I think you missed the concept of INDEXED VIEWS
> http://www.sql-server-performance.com/indexed_views.asp
>
> In addition please take a look at Steve Kass's scipt to ensure that using
> an indexe view SQL Server creates more efficient execution plan
> create table T (
> i int,
> filler char(1000) default 'abc'
> )
> go
> create view T_count with schemabinding as
> select
> cast(i as bit) as val,
> count_big(*) T_count
> from dbo.T group by cast(i as bit)
> go
> create unique clustered index T_count_uci on T_count(val)
> go
> insert into T(i)
> select OrderID
> from Northwind..[Order Details]
> go
> set statistics io on
> select count(*) from T
> go
> select sum(T_count) from T_count with (noexpand)
> go
> set statistics io off
> -- uses an efficient query plan on the materialized view
> go
> drop view T_count
> drop table T
>
>
> "SqlBeginner" <SqlBeginner@.discussions.microsoft.com> wrote in message
> news:F47FC536-3817-4DCE-A1F5-8E1C077ACDCF@.microsoft.com...
>
>
Index on View
Hi
I have a problem creating an index on a view. The view should return the record corresponding to the Maximum Obje_ID. This seems to work.
CREATE VIEW dbo.D_Object_View
WITH SCHEMABINDING
AS
SELECT
Policy_ID,
Obj_ID,
Environment_Code,
CoB,
Sub_CoB,
Policy_No,
Version_No,
Object_Type,
Item_Seq,
FROM dbo.D_Object
WHERE
(Obj_ID IN
(SELECT MAX(Obj_ID)
FROM dbo.d_object
GROUP BY Environment_Code, COB, Policy_No, SUB_COB, Object_Type, Item_Seq))
I create the index with the following statement :
CREATE UNIQUE CLUSTERED INDEX [IX_Object_ID] ON [dbo].[D_Object_View]([Obj_ID]) ON [PRIMARY]
but get the following error :
Cannot index the view 'DB.dbo.D_Object_View'. It contains one or more disallowed constructs.
I think it is because of the MAX statement but don't know of any other way to do it. :confused:Is Obj_ID part of an index in the parent tables? If so the index on the view may not buy you much performance improvement. How many rows are in each table and what's the execution plan look like for the view sql without the index?
Have you tried creating a non-unique index on the column?|||Yes Obj_ID is an index on the parent table and it cpontains aprox. 5 mil records but will increase as i need to add more data.
I have tried creating a non-unique one but get the following error :
Nonunique clustered index cannot be created on view 'D_Object_View' because only unique clustered indexes are allowed.|||Sorry, forgot about that I'm sure you've tried it but what about a non-clustered index? And does the optimizer utilize the existing index in the execution plan?|||A nonclustered gives me the following error:
Cannot create index on view 'D_Object_View'. It does not have a unique clustered index.
My knowledge of SQL is limited but if I understand correctly about the optimizer ... the estimated execution plan utilises a Index scan. This is good right ?
I have a problem creating an index on a view. The view should return the record corresponding to the Maximum Obje_ID. This seems to work.
CREATE VIEW dbo.D_Object_View
WITH SCHEMABINDING
AS
SELECT
Policy_ID,
Obj_ID,
Environment_Code,
CoB,
Sub_CoB,
Policy_No,
Version_No,
Object_Type,
Item_Seq,
FROM dbo.D_Object
WHERE
(Obj_ID IN
(SELECT MAX(Obj_ID)
FROM dbo.d_object
GROUP BY Environment_Code, COB, Policy_No, SUB_COB, Object_Type, Item_Seq))
I create the index with the following statement :
CREATE UNIQUE CLUSTERED INDEX [IX_Object_ID] ON [dbo].[D_Object_View]([Obj_ID]) ON [PRIMARY]
but get the following error :
Cannot index the view 'DB.dbo.D_Object_View'. It contains one or more disallowed constructs.
I think it is because of the MAX statement but don't know of any other way to do it. :confused:Is Obj_ID part of an index in the parent tables? If so the index on the view may not buy you much performance improvement. How many rows are in each table and what's the execution plan look like for the view sql without the index?
Have you tried creating a non-unique index on the column?|||Yes Obj_ID is an index on the parent table and it cpontains aprox. 5 mil records but will increase as i need to add more data.
I have tried creating a non-unique one but get the following error :
Nonunique clustered index cannot be created on view 'D_Object_View' because only unique clustered indexes are allowed.|||Sorry, forgot about that I'm sure you've tried it but what about a non-clustered index? And does the optimizer utilize the existing index in the execution plan?|||A nonclustered gives me the following error:
Cannot create index on view 'D_Object_View'. It does not have a unique clustered index.
My knowledge of SQL is limited but if I understand correctly about the optimizer ... the estimated execution plan utilises a Index scan. This is good right ?
Index on View
Is it possilbe to create an index on a view using a linked server table? I am using openquery and I know I can't use rowset functions if I want to index a view.
I just didn't know if there was any other way to access the linked server data in order to create an index.
thanks!!From the BOL:
The ANSI_NULLS and QUOTED_IDENTIFIER options must have been set to ON when the CREATE VIEW statement was executed. The OBJECTPROPERTY function reports this for views through the ExecIsAnsiNullsOn or ExecIsQuotedIdentOn properties.
The ANSI_NULLS option must have been set to ON for the execution of all CREATE TABLE statements that create tables referenced by the view.
The view must not reference any other views, only base tables.
All base tables referenced by the view must be in the same database as the view and have the same owner as the view.
My emphasis added. There are additional rules. Your mileage may vary. These actors are professionals; please do not try this at home. Warranty valid for a limited time only please consult your users manual for additional information and any other additional "fine print" as others may see fit to add.
Regards,
hmscott|||thanks for the quick reply and I apologize cause I am a rookie at this...here is what I have done per a previous post:
set to on:
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YEILDS_NULL
QUOTED_IDENTIFIERS
Set to off:
NUMERIC_ROUNDABORT
Then I ran my create one table view (from an mysql ODBC linked server) with SCHEMABINDING:
CREATE VIEW mysql_dnc with SCHEMABINDING
AS
select phone from openquery(mysql_dnc,
'select phonefrom DNC')
this doesn't work because of the following issue:
Server: Msg 1054, Level 15, State 3, Procedure mysql_dnc, Line 5
Syntax 'Openrowset' is not allowed in schema-bound objects.
my question is can I access the linked server without using openquery so I can then create an index on it?
thanks much!!!sql
I just didn't know if there was any other way to access the linked server data in order to create an index.
thanks!!From the BOL:
The ANSI_NULLS and QUOTED_IDENTIFIER options must have been set to ON when the CREATE VIEW statement was executed. The OBJECTPROPERTY function reports this for views through the ExecIsAnsiNullsOn or ExecIsQuotedIdentOn properties.
The ANSI_NULLS option must have been set to ON for the execution of all CREATE TABLE statements that create tables referenced by the view.
The view must not reference any other views, only base tables.
All base tables referenced by the view must be in the same database as the view and have the same owner as the view.
My emphasis added. There are additional rules. Your mileage may vary. These actors are professionals; please do not try this at home. Warranty valid for a limited time only please consult your users manual for additional information and any other additional "fine print" as others may see fit to add.
Regards,
hmscott|||thanks for the quick reply and I apologize cause I am a rookie at this...here is what I have done per a previous post:
set to on:
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YEILDS_NULL
QUOTED_IDENTIFIERS
Set to off:
NUMERIC_ROUNDABORT
Then I ran my create one table view (from an mysql ODBC linked server) with SCHEMABINDING:
CREATE VIEW mysql_dnc with SCHEMABINDING
AS
select phone from openquery(mysql_dnc,
'select phonefrom DNC')
this doesn't work because of the following issue:
Server: Msg 1054, Level 15, State 3, Procedure mysql_dnc, Line 5
Syntax 'Openrowset' is not allowed in schema-bound objects.
my question is can I access the linked server without using openquery so I can then create an index on it?
thanks much!!!sql
index on view
If an application uses a lot of views and the performance
is slow, should we create indexes on views? What kind of
indexes to create?
Thanks.
The following link has some useful information on improving performance using indexed views:
http://msdn.microsoft.com/library/de...exedviews1.asp
However, to be specific to your scenario, you might consider running a workload through Index Tuning Wizard to see whether it suggests that you create any.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine.
"Julia" <kqd02@.yahoo.com> wrote in message news:143df01c444cd$f6355010$a301280a@.phx.gbl...
If an application uses a lot of views and the performance
is slow, should we create indexes on views? What kind of
indexes to create?
Thanks.
|||You may consider indexed view, some people call it materialized view
too. Beware that there're alot limitations on indexed view, for detail,
read BOL.
You may also want to investigate how those views are constructed, are
they nested views? views joining another view? Based on my experience,
joining differnet views are bad idea, it may be easy to program, but
performance really sucks.
If that's not the case, run those views inside query analyzer to see if
there are any table scans, then create index accordingly.
Eric
Julia wrote:
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
Eric Li
SQL DBA
MCDBA
|||If performance is low, then you should start by adding the appropriate
indexes to the base tables that are used in the view. SQL-Server will
automatically take these into consideration.
If that doesn't help (enough), and you are running Enterprise Edition of
SQL-Server 2000, you could consider indexed views.
Hope this helps,
Gert-Jan
Julia wrote:
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
(Please reply only to the newsgroup)
|||Hi
Just to add to the other posts...
You may also want to consider if the view is being used appropriately!!!
e.g. It is not a great idea of using a view that joins half a dozen tables
when you only want data from a single base table.
John
"Julia" <kqd02@.yahoo.com> wrote in message
news:143df01c444cd$f6355010$a301280a@.phx.gbl...
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
|||Be very careful about creating indexed views. The cost of maintenance can be
very high... So exhaust all other possibilities prior to choosing indexed
views as a solution.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Julia" <kqd02@.yahoo.com> wrote in message
news:143df01c444cd$f6355010$a301280a@.phx.gbl...
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
is slow, should we create indexes on views? What kind of
indexes to create?
Thanks.
The following link has some useful information on improving performance using indexed views:
http://msdn.microsoft.com/library/de...exedviews1.asp
However, to be specific to your scenario, you might consider running a workload through Index Tuning Wizard to see whether it suggests that you create any.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine.
"Julia" <kqd02@.yahoo.com> wrote in message news:143df01c444cd$f6355010$a301280a@.phx.gbl...
If an application uses a lot of views and the performance
is slow, should we create indexes on views? What kind of
indexes to create?
Thanks.
|||You may consider indexed view, some people call it materialized view
too. Beware that there're alot limitations on indexed view, for detail,
read BOL.
You may also want to investigate how those views are constructed, are
they nested views? views joining another view? Based on my experience,
joining differnet views are bad idea, it may be easy to program, but
performance really sucks.
If that's not the case, run those views inside query analyzer to see if
there are any table scans, then create index accordingly.
Eric
Julia wrote:
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
Eric Li
SQL DBA
MCDBA
|||If performance is low, then you should start by adding the appropriate
indexes to the base tables that are used in the view. SQL-Server will
automatically take these into consideration.
If that doesn't help (enough), and you are running Enterprise Edition of
SQL-Server 2000, you could consider indexed views.
Hope this helps,
Gert-Jan
Julia wrote:
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
(Please reply only to the newsgroup)
|||Hi
Just to add to the other posts...
You may also want to consider if the view is being used appropriately!!!
e.g. It is not a great idea of using a view that joins half a dozen tables
when you only want data from a single base table.
John
"Julia" <kqd02@.yahoo.com> wrote in message
news:143df01c444cd$f6355010$a301280a@.phx.gbl...
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
|||Be very careful about creating indexed views. The cost of maintenance can be
very high... So exhaust all other possibilities prior to choosing indexed
views as a solution.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Julia" <kqd02@.yahoo.com> wrote in message
news:143df01c444cd$f6355010$a301280a@.phx.gbl...
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
index on view
If an application uses a lot of views and the performance
is slow, should we create indexes on views? What kind of
indexes to create?
Thanks.This is a multi-part message in MIME format.
--=_NextPart_000_0008_01C44498.B5850D10
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
The following link has some useful information on improving performance =using indexed views:
http://msdn.microsoft.com/library/default.asp?url=3D/library/en-us/dnsql2=
k/html/indexedviews1.asp
However, to be specific to your scenario, you might consider running a =workload through Index Tuning Wizard to see whether it suggests that you =create any.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine.
"Julia" <kqd02@.yahoo.com> wrote in message =news:143df01c444cd$f6355010$a301280a@.phx.gbl...
If an application uses a lot of views and the performance is slow, should we create indexes on views? What kind of indexes to create?
Thanks.
--=_NextPart_000_0008_01C44498.B5850D10
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
The following link has some useful information on =improving performance using indexed views:
http://msdn.microsoft.com/library/defau=lt.asp?url=3D/library/en-us/dnsql2k/html/indexedviews1.asp
However, to be specific to your scenario, you might =consider running a workload through Index Tuning Wizard to see whether it =suggests that you create any.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine.
"Julia" wrote in message news:143df01c444cd$=f6355010$a301280a@.phx.gbl...If an application uses a lot of views and the performance is slow, =should we create indexes on views? What kind of indexes to create?Thanks.
--=_NextPart_000_0008_01C44498.B5850D10--|||You may consider indexed view, some people call it materialized view
too. Beware that there're alot limitations on indexed view, for detail,
read BOL.
You may also want to investigate how those views are constructed, are
they nested views? views joining another view? Based on my experience,
joining differnet views are bad idea, it may be easy to program, but
performance really sucks.
If that's not the case, run those views inside query analyzer to see if
there are any table scans, then create index accordingly.
Eric
Julia wrote:
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
Eric Li
SQL DBA
MCDBA|||If performance is low, then you should start by adding the appropriate
indexes to the base tables that are used in the view. SQL-Server will
automatically take these into consideration.
If that doesn't help (enough), and you are running Enterprise Edition of
SQL-Server 2000, you could consider indexed views.
Hope this helps,
Gert-Jan
Julia wrote:
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
--
(Please reply only to the newsgroup)|||Hi
Just to add to the other posts...
You may also want to consider if the view is being used appropriately!!!
e.g. It is not a great idea of using a view that joins half a dozen tables
when you only want data from a single base table.
John
"Julia" <kqd02@.yahoo.com> wrote in message
news:143df01c444cd$f6355010$a301280a@.phx.gbl...
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.|||Be very careful about creating indexed views. The cost of maintenance can be
very high... So exhaust all other possibilities prior to choosing indexed
views as a solution.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Julia" <kqd02@.yahoo.com> wrote in message
news:143df01c444cd$f6355010$a301280a@.phx.gbl...
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
is slow, should we create indexes on views? What kind of
indexes to create?
Thanks.This is a multi-part message in MIME format.
--=_NextPart_000_0008_01C44498.B5850D10
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
The following link has some useful information on improving performance =using indexed views:
http://msdn.microsoft.com/library/default.asp?url=3D/library/en-us/dnsql2=
k/html/indexedviews1.asp
However, to be specific to your scenario, you might consider running a =workload through Index Tuning Wizard to see whether it suggests that you =create any.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine.
"Julia" <kqd02@.yahoo.com> wrote in message =news:143df01c444cd$f6355010$a301280a@.phx.gbl...
If an application uses a lot of views and the performance is slow, should we create indexes on views? What kind of indexes to create?
Thanks.
--=_NextPart_000_0008_01C44498.B5850D10
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
The following link has some useful information on =improving performance using indexed views:
http://msdn.microsoft.com/library/defau=lt.asp?url=3D/library/en-us/dnsql2k/html/indexedviews1.asp
However, to be specific to your scenario, you might =consider running a workload through Index Tuning Wizard to see whether it =suggests that you create any.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine.
"Julia"
--=_NextPart_000_0008_01C44498.B5850D10--|||You may consider indexed view, some people call it materialized view
too. Beware that there're alot limitations on indexed view, for detail,
read BOL.
You may also want to investigate how those views are constructed, are
they nested views? views joining another view? Based on my experience,
joining differnet views are bad idea, it may be easy to program, but
performance really sucks.
If that's not the case, run those views inside query analyzer to see if
there are any table scans, then create index accordingly.
Eric
Julia wrote:
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
Eric Li
SQL DBA
MCDBA|||If performance is low, then you should start by adding the appropriate
indexes to the base tables that are used in the view. SQL-Server will
automatically take these into consideration.
If that doesn't help (enough), and you are running Enterprise Edition of
SQL-Server 2000, you could consider indexed views.
Hope this helps,
Gert-Jan
Julia wrote:
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
--
(Please reply only to the newsgroup)|||Hi
Just to add to the other posts...
You may also want to consider if the view is being used appropriately!!!
e.g. It is not a great idea of using a view that joins half a dozen tables
when you only want data from a single base table.
John
"Julia" <kqd02@.yahoo.com> wrote in message
news:143df01c444cd$f6355010$a301280a@.phx.gbl...
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.|||Be very careful about creating indexed views. The cost of maintenance can be
very high... So exhaust all other possibilities prior to choosing indexed
views as a solution.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Julia" <kqd02@.yahoo.com> wrote in message
news:143df01c444cd$f6355010$a301280a@.phx.gbl...
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
Subscribe to:
Posts (Atom)