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.

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

Index enquiry

hi all,
I have a table with 700K records with the primary key as cluster index.
TableA {
chrRef char(10), -- key
chrStatus char(2),
dtTrade datetime,
dtSettle datetime,
....
}
When query the table by filter records on non-primay key, the performance is
acceptable (less than 10K records)
e.g. select * from TableA where dtTrade = '20060101'
When records has been grown to 700K, the query is quite slow. I have added
an index (IX_dtTrade) on the column "dtTrade" in order to reduce the query
time. However, i found that SQL server did not use the index (IX_dtTrade) to
speed up the query. SQL server still using the cluster index to retrieve
records. From the help, i found that there was a method to force SQL server
to use the index. As a result, the query time reduce a lot.
e.g. select * from TableA with index (IX_dtTrade) where dtTrade = '20060101'
For this case,
1) Is there any setup so that the SQL server will use the index (IX_dtTrade)
automatically without explicit the cluase (with index ())?
2) When create index, what is the difference between single index and
compound index in SQL server? It seems that when an index is created on the
column "dtTrade" (IX_dtTrade) and "dtTrade, dtSettle" (IX_dtTrade_dtSettle),
the query time is same.
3) If there are many queries filter on columns "dtTrade", "dtSettle" and
"chrStatus", create an index on each column or a compound index on the three
columns?
3) Is it the only way to speed up the query time by creating index on target
column? (provided that no change on number of records)
Any suggestions? Thank in advance!!
Regards,
MartinCheck the execution plan to see how many rows are estimated to be returned. If SQL Server estimates
a large number of rows, it will consider a scan more efficient than using a non-clustered index.
This is because using a non-clustered index, SQL Server will navigate the index, and *for each row*
access the data page. Imagine if you return 10 000 rows, then you have 10 000 data page accesses,
even if the whole table perhaps fits on 5 000 pages. This is easier to explain with a white-board.
Assuming the estimate is off, we need to figure out why. It could be several reasons, for instance:
Bad statistics.
The query you showed us is not what you are running.
You use a stored procedure and the data you search for is a parameter.
The data you search for is a variable.
The condition for the data isn't expressed as in your example.
As for your questions:
> 1) Is there any setup so that the SQL server will use the index (IX_dtTrade) automatically without
> explicit the cluase (with index ())?
There's no "magic button" for this. See my above elaboration.
> 2) When create index, what is the difference between single index and compound index in SQL
> server? It seems that when an index is created on the column "dtTrade" (IX_dtTrade) and "dtTrade,
> dtSettle" (IX_dtTrade_dtSettle), the query time is same.
An index on several columns, say (a, b) , can be good for conditions like:
A = 2 AND B = 7
But not for:
B = 45
So you need to know your queries in order to create a good indexing strategy. If you are uncertain,
start by one index per column.
> 3) If there are many queries filter on columns "dtTrade", "dtSettle" and "chrStatus", create an
> index on each column or a compound index on the three columns?
See above.
> 3) Is it the only way to speed up the query time by creating index on target column? (provided
> that no change on number of records)
If you don't give SQL Server any way to limit which rows it need to look for in order to determine
which satisfies your condition, well, then SQL Server need to look at each row. An index does just
that.
Also, don't do SELECT *. Only return the columns you need. The main importance for this isn't
perhaps to reduce network bandwidth. It is that you probably lose the ability to cover your queries
with a non-clustered index. Such an index has all the columns that the query need in it and SQL
Server doesn't have to access the data page for each row, the answer is in the index page.
This is a big topic, so I suggest you start studying and reading a bit. Books Online has some good
sections which is a good start. Kalen Delaney's "Inside SQL Server" book is very good at describing
these constructs.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Atenza" <Atenza@.mail.hongkong.com> wrote in message news:%23q8OTNn9GHA.788@.TK2MSFTNGP05.phx.gbl...
> hi all,
> I have a table with 700K records with the primary key as cluster index.
> TableA {
> chrRef char(10), -- key
> chrStatus char(2),
> dtTrade datetime,
> dtSettle datetime,
> ....
> }
> When query the table by filter records on non-primay key, the performance is acceptable (less than
> 10K records)
> e.g. select * from TableA where dtTrade = '20060101'
> When records has been grown to 700K, the query is quite slow. I have added an index (IX_dtTrade)
> on the column "dtTrade" in order to reduce the query time. However, i found that SQL server did
> not use the index (IX_dtTrade) to speed up the query. SQL server still using the cluster index to
> retrieve records. From the help, i found that there was a method to force SQL server to use the
> index. As a result, the query time reduce a lot.
> e.g. select * from TableA with index (IX_dtTrade) where dtTrade = '20060101'
> For this case,
> 1) Is there any setup so that the SQL server will use the index (IX_dtTrade) automatically without
> explicit the cluase (with index ())?
> 2) When create index, what is the difference between single index and compound index in SQL
> server? It seems that when an index is created on the column "dtTrade" (IX_dtTrade) and "dtTrade,
> dtSettle" (IX_dtTrade_dtSettle), the query time is same.
> 3) If there are many queries filter on columns "dtTrade", "dtSettle" and "chrStatus", create an
> index on each column or a compound index on the three columns?
> 3) Is it the only way to speed up the query time by creating index on target column? (provided
> that no change on number of records)
> Any suggestions? Thank in advance!!
> Regards,
> Martin
>|||thank you for your suggestion!!! really useful!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23UbAQXn9GHA.3384@.TK2MSFTNGP05.phx.gbl...
> Check the execution plan to see how many rows are estimated to be
> returned. If SQL Server estimates a large number of rows, it will consider
> a scan more efficient than using a non-clustered index. This is because
> using a non-clustered index, SQL Server will navigate the index, and *for
> each row* access the data page. Imagine if you return 10 000 rows, then
> you have 10 000 data page accesses, even if the whole table perhaps fits
> on 5 000 pages. This is easier to explain with a white-board.
> Assuming the estimate is off, we need to figure out why. It could be
> several reasons, for instance:
> Bad statistics.
> The query you showed us is not what you are running.
> You use a stored procedure and the data you search for is a parameter.
> The data you search for is a variable.
> The condition for the data isn't expressed as in your example.
> As for your questions:
>> 1) Is there any setup so that the SQL server will use the index
>> (IX_dtTrade) automatically without explicit the cluase (with index ())?
> There's no "magic button" for this. See my above elaboration.
>
>> 2) When create index, what is the difference between single index and
>> compound index in SQL server? It seems that when an index is created on
>> the column "dtTrade" (IX_dtTrade) and "dtTrade, dtSettle"
>> (IX_dtTrade_dtSettle), the query time is same.
> An index on several columns, say (a, b) , can be good for conditions like:
> A = 2 AND B = 7
> But not for:
> B = 45
> So you need to know your queries in order to create a good indexing
> strategy. If you are uncertain, start by one index per column.
>
>> 3) If there are many queries filter on columns "dtTrade", "dtSettle" and
>> "chrStatus", create an index on each column or a compound index on the
>> three columns?
> See above.
>
>> 3) Is it the only way to speed up the query time by creating index on
>> target column? (provided that no change on number of records)
> If you don't give SQL Server any way to limit which rows it need to look
> for in order to determine which satisfies your condition, well, then SQL
> Server need to look at each row. An index does just that.
> Also, don't do SELECT *. Only return the columns you need. The main
> importance for this isn't perhaps to reduce network bandwidth. It is that
> you probably lose the ability to cover your queries with a non-clustered
> index. Such an index has all the columns that the query need in it and SQL
> Server doesn't have to access the data page for each row, the answer is in
> the index page.
> This is a big topic, so I suggest you start studying and reading a bit.
> Books Online has some good sections which is a good start. Kalen Delaney's
> "Inside SQL Server" book is very good at describing these constructs.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:%23q8OTNn9GHA.788@.TK2MSFTNGP05.phx.gbl...
>> hi all,
>> I have a table with 700K records with the primary key as cluster index.
>> TableA {
>> chrRef char(10), -- key
>> chrStatus char(2),
>> dtTrade datetime,
>> dtSettle datetime,
>> ....
>> }
>> When query the table by filter records on non-primay key, the performance
>> is acceptable (less than 10K records)
>> e.g. select * from TableA where dtTrade = '20060101'
>> When records has been grown to 700K, the query is quite slow. I have
>> added an index (IX_dtTrade) on the column "dtTrade" in order to reduce
>> the query time. However, i found that SQL server did not use the index
>> (IX_dtTrade) to speed up the query. SQL server still using the cluster
>> index to retrieve records. From the help, i found that there was a method
>> to force SQL server to use the index. As a result, the query time reduce
>> a lot.
>> e.g. select * from TableA with index (IX_dtTrade) where dtTrade =>> '20060101'
>> For this case,
>> 1) Is there any setup so that the SQL server will use the index
>> (IX_dtTrade) automatically without explicit the cluase (with index ())?
>> 2) When create index, what is the difference between single index and
>> compound index in SQL server? It seems that when an index is created on
>> the column "dtTrade" (IX_dtTrade) and "dtTrade, dtSettle"
>> (IX_dtTrade_dtSettle), the query time is same.
>> 3) If there are many queries filter on columns "dtTrade", "dtSettle" and
>> "chrStatus", create an index on each column or a compound index on the
>> three columns?
>> 3) Is it the only way to speed up the query time by creating index on
>> target column? (provided that no change on number of records)
>> Any suggestions? Thank in advance!!
>> Regards,
>> Martin
>

Index Cluster Vs. Index No Cluster

Hi,
it is good practices to have in a table (Hotels) Index not to cluster on the
HotelID column and Index to cluster on the foreign column HotelID in a table
details?
Thank you
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ering/200509/1
As always it depends.
Firstly what is the key, how is the key determined and what is the insert
activity?
An ascending integer key is fine to use as a clustered index but only if the
inserts are made in ascending order (1,2,3,4....)
If it's 1, 9999, 45, 77, 888, 4345,... then pages will have to split
eventually to make room for keys as the inserts proceed. SQL will put as
many keys as it can in a page (fill factors restrict this of course) so when
later on you insert a low key number then the page must be split. In this
instance it is better to have only a non-clustered index on the key. This
table is called a "heap".
If the insert activity is low then of course you may be better with a
clustered index after all.
If the inserts are 1, 3, 5, 6, 7, then each page fills up as the insertes
proceed and there is no need for splits because there are no gaps. This is
fine for a clustered index.
If the key is not a numeric but a Name or character data then do not use
this as the clustered index. This is because other indexes you create have
the primary key in their index as the target of the index so you duplicate a
long key for every additional index. In this case ceated a identifier column
for your clustered index and create a unique index on your long key.
Nik Marshall-Blank MCSD/MCDBA
"CYanez via droptable.com" <forum@.droptable.com> wrote in message
news:5467849175759@.droptable.com...
> Hi,
> it is good practices to have in a table (Hotels) Index not to cluster on
> the
> HotelID column and Index to cluster on the foreign column HotelID in a
> table
> details?
> Thank you
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...ering/200509/1
|||Thank you Nik
Nik Marshall-Blank wrote:[vbcol=seagreen]
>As always it depends.
>Firstly what is the key, how is the key determined and what is the insert
>activity?
>An ascending integer key is fine to use as a clustered index but only if the
>inserts are made in ascending order (1,2,3,4....)
>If it's 1, 9999, 45, 77, 888, 4345,... then pages will have to split
>eventually to make room for keys as the inserts proceed. SQL will put as
>many keys as it can in a page (fill factors restrict this of course) so when
>later on you insert a low key number then the page must be split. In this
>instance it is better to have only a non-clustered index on the key. This
>table is called a "heap".
>If the insert activity is low then of course you may be better with a
>clustered index after all.
>If the inserts are 1, 3, 5, 6, 7, then each page fills up as the insertes
>proceed and there is no need for splits because there are no gaps. This is
>fine for a clustered index.
>If the key is not a numeric but a Name or character data then do not use
>this as the clustered index. This is because other indexes you create have
>the primary key in their index as the target of the index so you duplicate a
>long key for every additional index. In this case ceated a identifier column
>for your clustered index and create a unique index on your long key.
>[quoted text clipped - 4 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ering/200509/1

INDEX CLUSTER QUESTION

Hi
USING SQL SERVER 2000
We have a clustered index in a Table with a 100 millions of records.
This table grows 1.000.000 of records per month.
We have a table of clients and another one of accounts.
Both using Clustered Index.
What index to use?
why?
We have performance problems!!!
help me !!
thanks
MacisuUnless you are using query hints, SQL Server's query optimizer decides which
index to use.
Query Hints:
http://msdn.microsoft.com/library/d...r />
_8upf.asp
Clustered Indexes:
http://msdn.microsoft.com/library/d...>
_05_5h6b.asp
Performance Tuning Guide for Date Warehouses:
http://www.microsoft.com/technet/pr...n/rdbmspft.mspx
"Macisu" <Macisu@.discussions.microsoft.com> wrote in message
news:A85C3AFA-1243-4F6F-BCD7-2C7A6FD13EAE@.microsoft.com...
> Hi
> USING SQL SERVER 2000
> We have a clustered index in a Table with a 100 millions of records.
> This table grows 1.000.000 of records per month.
> We have a table of clients and another one of accounts.
> Both using Clustered Index.
> What index to use?
> why?
> We have performance problems!!!
> help me !!
>
> thanks
> Macisu
>|||How are they currently indexed and what are you using for Primary Keys?
/*
-Paul Nielsen
www.SQLServerBible.com
www.SolidQualityLearning.com
*/
"Macisu" <Macisu@.discussions.microsoft.com> wrote in message
news:A85C3AFA-1243-4F6F-BCD7-2C7A6FD13EAE@.microsoft.com...
> Hi
> USING SQL SERVER 2000
> We have a clustered index in a Table with a 100 millions of records.
> This table grows 1.000.000 of records per month.
> We have a table of clients and another one of accounts.
> Both using Clustered Index.
> What index to use?
> why?
> We have performance problems!!!
> help me !!
>
> thanks
> Macisu
>

index cluster

Can u explain it to me pls?

2/ also what s 4th normal form ?

Thanks

hi,
i think u want tp know abt clustered index....its the way of physically arranging data on the disk for faster access of data...its arranged in the form of B-tree structure, where the leaf nodes contain the data (best way to explain in real life is how the data is arranged in telephone directory)...its explained well in sql server books online...

ao for 4th normal form...i'll assume that u know till 3rd normal... in 3rd normal form we try to keep only the fully functional fields rematin in the stable...still we may be left with multivalued dependencies...in the 4th NF we try to make these multivalued dependencies to functional dependency...
multivalued dependencies are of the type a-> b ->c , so we need to make them a->b and a->c
this is a gud link for normalization..
http://www.cs.jcu.edu.au/Subjects/cp1500/1998/Lecture_Notes/normalisation/1nf.html|||just a normal index then, right

Index Changes

I have a SQL Server 2000 database that I needed to changes the fields in the
cluster index. Before I was able to drop the cluster index I had to drop
several foreign keys constraints. I was able to add cluster index with the
correct fields.
Next I add back the foreign key constraints. Lastly I updated statistics on
the table with the cluster index change.
Should the update statistics be applied to tables that foreign key
constraints were dropped and added to complete the index change?
Thank You,No need to update statistics after you have created an index. The statistics are created with the
index create. Also, no need to change anything on the referencing tables either.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:530A150C-DAEB-4CB8-B537-A32BF28F04B6@.microsoft.com...
> I have a SQL Server 2000 database that I needed to changes the fields in the
> cluster index. Before I was able to drop the cluster index I had to drop
> several foreign keys constraints. I was able to add cluster index with the
> correct fields.
> Next I add back the foreign key constraints. Lastly I updated statistics on
> the table with the cluster index change.
> Should the update statistics be applied to tables that foreign key
> constraints were dropped and added to complete the index change?
> Thank You,

Index Changes

I have a SQL Server 2000 database that I needed to changes the fields in the
cluster index. Before I was able to drop the cluster index I had to drop
several foreign keys constraints. I was able to add cluster index with the
correct fields.
Next I add back the foreign key constraints. Lastly I updated statistics on
the table with the cluster index change.
Should the update statistics be applied to tables that foreign key
constraints were dropped and added to complete the index change?
Thank You,
No need to update statistics after you have created an index. The statistics are created with the
index create. Also, no need to change anything on the referencing tables either.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:530A150C-DAEB-4CB8-B537-A32BF28F04B6@.microsoft.com...
> I have a SQL Server 2000 database that I needed to changes the fields in the
> cluster index. Before I was able to drop the cluster index I had to drop
> several foreign keys constraints. I was able to add cluster index with the
> correct fields.
> Next I add back the foreign key constraints. Lastly I updated statistics on
> the table with the cluster index change.
> Should the update statistics be applied to tables that foreign key
> constraints were dropped and added to complete the index change?
> Thank You,

Index Changes

I have a SQL Server 2000 database that I needed to changes the fields in the
cluster index. Before I was able to drop the cluster index I had to drop
several foreign keys constraints. I was able to add cluster index with the
correct fields.
Next I add back the foreign key constraints. Lastly I updated statistics on
the table with the cluster index change.
Should the update statistics be applied to tables that foreign key
constraints were dropped and added to complete the index change?
Thank You,No need to update statistics after you have created an index. The statistics
are created with the
index create. Also, no need to change anything on the referencing tables eit
her.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:530A150C-DAEB-4CB8-B537-A32BF28F04B6@.microsoft.com...
> I have a SQL Server 2000 database that I needed to changes the fields in t
he
> cluster index. Before I was able to drop the cluster index I had to drop
> several foreign keys constraints. I was able to add cluster index with th
e
> correct fields.
> Next I add back the foreign key constraints. Lastly I updated statistics o
n
> the table with the cluster index change.
> Should the update statistics be applied to tables that foreign key
> constraints were dropped and added to complete the index change?
> Thank You,