Index statistics and a primary key

Hi

I have a question regarding updating statistics for a primary key.

Background: An update statistics with fullscan is sometimes taking 30 minutes - the table is 80 million rows, with only 4 columns. The table is truncated, and then 80 million rows inserted all in one go.

Now why the update stats is taking that long is another question (I have no idea - any thoughts?), but my question is; Since you can't disable the "not automatically recompute statistics" option for a primary key, and you would think it would be imperitive for the stats to be kept up to date for a PK for inserts.... does this mean the stats would be kept up to date? and an update stat with fullscan isn't required?

Hope someone can help

Thanks
James

If you have not changed anything statistics will be automatically updated by SQL Server after a number of modifications have been made to the table. SQL Server 2005 updates the counter that checks the number of modifications for BULK INSERT too. When autostats kicks in only a sample of the data is used to calculate them.

Doing a full scan requires a lot of I/O so with 80 million rows it might be slow if your disk subsystem is not fast enough..

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

sql

Index Seek (or) Index Scan in Execution Plan

Hi all,
I have one table. Where :
DonorID Int (Identity) Primary Key
FirstName Varchar(25)
LastName Varchar(25)
...
...
I have One nonclustred index on Lastname another nonclustred index on
(lastname, firstname).
Suppose I Execute the Query:
select * from TABLE where lastname like 'abott%' (This Query uses
Index Seek on the Compound Index)
But if I use the below Query:
select * from TABLE where lastname like 'smith%' (This Query uses
Index Scan)
But
select * from TABLE (index = ind_CMP_name) where lastname like 'smith%'
(But this Query uses the Index Seek)
NOTE: ind_CMP_name is the Compound Index.
Why there is the Difference, One Query uses Index Seek while other uses
Index Scan, even if both the query uses the same where condition on same
column?
Thanks
Prabhat
Hi all,
In Adition to Above Post / Question I have 2 More Questions:
1) Is the Index Seek is Faster or Index Scan? and Why?
2) How Can I Replace the Index = IndexName in the Above Post? (I Think the
Index = is used only for backward compatibility in SQL Server 2000)
Thanks in Advance for any Suggestion and help for these 2 posts...
Thanks
Prabhat
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:#J1NJu$vEHA.3096@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have one table. Where :
> DonorID Int (Identity) Primary Key
> FirstName Varchar(25)
> LastName Varchar(25)
> ...
> ...
> I have One nonclustred index on Lastname another nonclustred index on
> (lastname, firstname).
> Suppose I Execute the Query:
> select * from TABLE where lastname like 'abott%' (This Query
uses
> Index Seek on the Compound Index)
> But if I use the below Query:
> select * from TABLE where lastname like 'smith%' (This Query uses
> Index Scan)
> But
> select * from TABLE (index = ind_CMP_name) where lastname like 'smith%'
> (But this Query uses the Index Seek)
> NOTE: ind_CMP_name is the Compound Index.
> Why there is the Difference, One Query uses Index Seek while other uses
> Index Scan, even if both the query uses the same where condition on same
> column?
> Thanks
> Prabhat
>
|||The 2 queries in your list are not the same. They are searching for
different rows, and a different number of rows will be returned for each.
This is called selectivity - If a very small percentage of rows in the table
will be returned ( 3-5%) then the query is very selective. Index Seeks are
better for very selective queries and index scans or better for queries with
low selectivity. SQL Server's optimizer is smart enough to figure this out
and (generally) choose a good plan..
see inline
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
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:%23ByYLXAwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> In Adition to Above Post / Question I have 2 More Questions:
> 1) Is the Index Seek is Faster or Index Scan? and Why?
Index seek does a binary search from the root to the leaf level, a Scan
reads through part of all of the leaf level... So scans generally do more
IO than seeks.
> 2) How Can I Replace the Index = IndexName in the Above Post? (I Think the
> Index = is used only for backward compatibility in SQL Server 2000)
>
It is preferable to not use index hints, but if performance is killing
you...( update statistics first, then see if you get better response).
select yad yad from table WITH (index = whatever)
Be sure to use the with clause for compatilibility with SQL 2005
> Thanks in Advance for any Suggestion and help for these 2 posts...
> Thanks
> Prabhat
>
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:#J1NJu$vEHA.3096@.TK2MSFTNGP14.phx.gbl...
> uses
>
|||Hi Wayne,
Thanks for your Suggestions.
Reg the 2 Queries:
Yes They are searching for different Rows. And the 1st Query is Retrieving 2
Rows while the 2nd Query returns 5622 Rows.
So As you told SQL Server optimizer will Use Index Seek for 1st Query and
Index Scan for 2nd Query?
Then If I use "Index=" Keyword in the Second Query then that the 2nd Query
uses the Index Seek. Why is like that?
And Now If I write :
select * from TABLE with(index = ind_CMP_name) where lastname like 'smith%'
So This is Better then using Only "Index=" as this is Also Supported in
2005?
Thanks
Prabhat
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:e6A#GRBwEHA.1400@.TK2MSFTNGP11.phx.gbl...
> The 2 queries in your list are not the same. They are searching for
> different rows, and a different number of rows will be returned for each.
> This is called selectivity - If a very small percentage of rows in the
table
> will be returned ( 3-5%) then the query is very selective. Index Seeks are
> better for very selective queries and index scans or better for queries
with[vbcol=seagreen]
> low selectivity. SQL Server's optimizer is smart enough to figure this out
> and (generally) choose a good plan..
> see inline
> --
> 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
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:%23ByYLXAwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> Index seek does a binary search from the root to the leaf level, a Scan
> reads through part of all of the leaf level... So scans generally do more
> IO than seeks.
the[vbcol=seagreen]
> It is preferable to not use index hints, but if performance is killing
> you...( update statistics first, then see if you get better response).
> select yad yad from table WITH (index = whatever)
> Be sure to use the with clause for compatilibility with SQL 2005
Query[vbcol=seagreen]
uses[vbcol=seagreen]
'smith%'[vbcol=seagreen]
uses[vbcol=seagreen]
same
>
|||Prabhat
If you use the (INDEX = ..) hint you are FORCING SQL Server to use the index
you tell it to use, whether or not that is a good choice. If you measure the
peformance (perhaps SET STATISTICS IO ON) you will see that when you force
the index, the performance is worse than when you let SQL Server make its
own choice, and it chooses to do the scan.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:uuZ%23H4BwEHA.2944@.TK2MSFTNGP12.phx.gbl...
> Hi Wayne,
> Thanks for your Suggestions.
> Reg the 2 Queries:
> Yes They are searching for different Rows. And the 1st Query is Retrieving
> 2
> Rows while the 2nd Query returns 5622 Rows.
> So As you told SQL Server optimizer will Use Index Seek for 1st Query and
> Index Scan for 2nd Query?
> Then If I use "Index=" Keyword in the Second Query then that the 2nd Query
> uses the Index Seek. Why is like that?
> And Now If I write :
> select * from TABLE with(index = ind_CMP_name) where lastname like
> 'smith%'
> So This is Better then using Only "Index=" as this is Also Supported in
> 2005?
> Thanks
> Prabhat
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:e6A#GRBwEHA.1400@.TK2MSFTNGP11.phx.gbl...
> table
> with
> the
> Query
> uses
> 'smith%'
> uses
> same
>
|||Hi Kalen,
Thanks for reply.
I use the "Index =" mainly for 2 reasons.
1) My SQL Query uses 2 Conditions in where clause. And I can see that there
is a Index Scan Involve in that Query. So I prefer "Index =" which make
Index Seek.
2) In Some cases My output should be Order by Lastname, FirstName. And Also
the query will have the Where Clause as above. So Here also i can see some
time it uses Index Scan. And I use a Compound Index on Lastname, Firstname -
To get the order. So I use the Index= in this case also.
You can see the Example of Query in the Main (TOP / original Post).
Kindly suggest.
Thanks
Prabhat
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:#h9EUmCwEHA.1264@.TK2MSFTNGP12.phx.gbl...
> Prabhat
> If you use the (INDEX = ..) hint you are FORCING SQL Server to use the
index
> you tell it to use, whether or not that is a good choice. If you measure
the[vbcol=seagreen]
> peformance (perhaps SET STATISTICS IO ON) you will see that when you force
> the index, the performance is worse than when you let SQL Server make its
> own choice, and it chooses to do the scan.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:uuZ%23H4BwEHA.2944@.TK2MSFTNGP12.phx.gbl...
Retrieving[vbcol=seagreen]
and[vbcol=seagreen]
Query[vbcol=seagreen]
each.[vbcol=seagreen]
Think[vbcol=seagreen]
on
>
|||Prabhat wrote:
> Hi Kalen,
> Thanks for reply.
> I use the "Index =" mainly for 2 reasons.
> 1) My SQL Query uses 2 Conditions in where clause. And I can see that
> there is a Index Scan Involve in that Query. So I prefer "Index ="
> which make Index Seek.
> 2) In Some cases My output should be Order by Lastname, FirstName.
> And Also the query will have the Where Clause as above. So Here also
> i can see some time it uses Index Scan. And I use a Compound Index on
> Lastname, Firstname - To get the order. So I use the Index= in this
> case also.
> You can see the Example of Query in the Main (TOP / original Post).
> Kindly suggest.
> Thanks
> Prabhat
>
Yes, you are correct that using the hint forces SQL Server to use the
index. But what Kalen is trying to explain to you is that using a
table/clustered index scan operation on the table when many rows are
returned is usually more cost effective for SQL Server. Unless you
dealing with a covering index, SQL Server has to perform a bookmark
lookup for each matching row. And all these bookmark lookups are very
costly when you consider SQL Server has to perform 5,000+ of them. In
that case, SQL Server chose to use a scan operation instead because it
is easier and faster for it to scan the table.
Now SQL Server does not always make the right decision. That's why
having updated statistics in your tables is important. But to force SQL
Server to always use the index misses the point. You are trying to
outthink the SQL Server query optimizer and that's a tough battle to win
in the long run.
David Gugick
Imceda Software
www.imceda.com
|||Thanks David for your Suggestion. Can U please tell me what exactly a
Covering Index? And Does that Help in my case?
Thanks
Prabhat
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O3nP1vLwEHA.2944@.TK2MSFTNGP12.phx.gbl...
> Prabhat wrote:
> Yes, you are correct that using the hint forces SQL Server to use the
> index. But what Kalen is trying to explain to you is that using a
> table/clustered index scan operation on the table when many rows are
> returned is usually more cost effective for SQL Server. Unless you
> dealing with a covering index, SQL Server has to perform a bookmark
> lookup for each matching row. And all these bookmark lookups are very
> costly when you consider SQL Server has to perform 5,000+ of them. In
> that case, SQL Server chose to use a scan operation instead because it
> is easier and faster for it to scan the table.
> Now SQL Server does not always make the right decision. That's why
> having updated statistics in your tables is important. But to force SQL
> Server to always use the index misses the point. You are trying to
> outthink the SQL Server query optimizer and that's a tough battle to win
> in the long run.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Hi All,
Index Seek or Bookmark Lookup - Cost?
=============================
I have the below 2 Queries:
(1)
select top 100 donorid, firstname, lastname, state, zip, phonenum, country,
olddonorid, sourceid
from donor (index = ind_donor_name)
where lastname >= 'nath' and sourceid = 'flcc'
(2)
select top 100 donorid, firstname, lastname, state, zip, phonenum, country,
olddonorid, sourceid
from donor (index = ind_donor_name)
where lastname like 'nath%' and sourceid = 'flcc'
NOTE: ind_donor_name is the Compound Index on LastName, FirstName.
Even if Both the Queries are not Same in Where Condition, But Still refers
to the same Index. But I see a Different is Cost in Execution Plan.
that is:
the 1st Query Cost 1% in Index Seek and 99% in Bookmark Lookup.
But the 2nd Query Cost 51% in Index Seek and 49% in Bookmark Lookup.
[Note: Please refer the Discussions in this thread for more details...]
So As per the Above Cost Criteria which Plan is Best?
Thanks
Prabhat
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:#J1NJu$vEHA.3096@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have one table. Where :
> DonorID Int (Identity) Primary Key
> FirstName Varchar(25)
> LastName Varchar(25)
> ...
> ...
> I have One nonclustred index on Lastname another nonclustred index on
> (lastname, firstname).
> Suppose I Execute the Query:
> select * from TABLE where lastname like 'abott%' (This Query
uses
> Index Seek on the Compound Index)
> But if I use the below Query:
> select * from TABLE where lastname like 'smith%' (This Query uses
> Index Scan)
> But
> select * from TABLE (index = ind_CMP_name) where lastname like 'smith%'
> (But this Query uses the Index Seek)
> NOTE: ind_CMP_name is the Compound Index.
> Why there is the Difference, One Query uses Index Seek while other uses
> Index Scan, even if both the query uses the same where condition on same
> column?
> Thanks
> Prabhat
>
|||Prabhat wrote:

>Hi Steve,
>My requrement is to search for "nath" (I know both the queries are
>different). In 1st case i am listing All > nath and in 2nd case like nath.
>That Does not matter.
>
But that's why the estimated execution costs are different. You haven't
shown the plans, but the plans may be identical, and just have different
costs because of the difference in the estimated number of rows
returned. If the queries return the same results, it's possible that
the actual running times are the same. Have you run the queries with
set statistics io on to see if there's a difference?
Sorry, but I still don't understand why if you want to search for
"nath", you are comparing plans that do something else.
SK

>Suppose I have 2 same queries with 2 diferent approach with that 2 Execution
>Plan, Then Which Plan I should go for?
>Some Additional Hint:
>1st Query Cost 98.35% relative to the batch
>while the 2nd Query Cost 1.65% relative to the Bacth.
>Thanks
>Prabhat
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:eupyWfOwEHA.2624@.TK2MSFTNGP11.phx.gbl...
>
>country,
>
>country,
>
>refers
>
>
>

Index Seek (or) Index Scan in Execution Plan

Hi all,
I have one table. Where :
DonorID Int (Identity) Primary Key
FirstName Varchar(25)
LastName Varchar(25)
...
...
I have One nonclustred index on Lastname another nonclustred index on
(lastname, firstname).
Suppose I Execute the Query:
select * from TABLE where lastname like 'abott%' (This Query uses
Index Seek on the Compound Index)
But if I use the below Query:
select * from TABLE where lastname like 'smith%' (This Query uses
Index Scan)
But
select * from TABLE (index = ind_CMP_name) where lastname like 'smith%'
(But this Query uses the Index Seek)
NOTE: ind_CMP_name is the Compound Index.
Why there is the Difference, One Query uses Index Seek while other uses
Index Scan, even if both the query uses the same where condition on same
column?
Thanks
PrabhatHi all,
In Adition to Above Post / Question I have 2 More Questions:
1) Is the Index Seek is Faster or Index Scan? and Why?
2) How Can I Replace the Index = IndexName in the Above Post? (I Think the
Index = is used only for backward compatibility in SQL Server 2000)
Thanks in Advance for any Suggestion and help for these 2 posts...
Thanks
Prabhat
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:#J1NJu$vEHA.3096@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have one table. Where :
> DonorID Int (Identity) Primary Key
> FirstName Varchar(25)
> LastName Varchar(25)
> ...
> ...
> I have One nonclustred index on Lastname another nonclustred index on
> (lastname, firstname).
> Suppose I Execute the Query:
> select * from TABLE where lastname like 'abott%' (This Query
uses
> Index Seek on the Compound Index)
> But if I use the below Query:
> select * from TABLE where lastname like 'smith%' (This Query uses
> Index Scan)
> But
> select * from TABLE (index = ind_CMP_name) where lastname like 'smith%'
> (But this Query uses the Index Seek)
> NOTE: ind_CMP_name is the Compound Index.
> Why there is the Difference, One Query uses Index Seek while other uses
> Index Scan, even if both the query uses the same where condition on same
> column?
> Thanks
> Prabhat
>|||The 2 queries in your list are not the same. They are searching for
different rows, and a different number of rows will be returned for each.
This is called selectivity - If a very small percentage of rows in the table
will be returned ( 3-5%) then the query is very selective. Index Seeks are
better for very selective queries and index scans or better for queries with
low selectivity. SQL Server's optimizer is smart enough to figure this out
and (generally) choose a good plan..
see inline
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
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:%23ByYLXAwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> In Adition to Above Post / Question I have 2 More Questions:
> 1) Is the Index Seek is Faster or Index Scan? and Why?
Index seek does a binary search from the root to the leaf level, a Scan
reads through part of all of the leaf level... So scans generally do more
IO than seeks.
> 2) How Can I Replace the Index = IndexName in the Above Post? (I Think the
> Index = is used only for backward compatibility in SQL Server 2000)
>
It is preferable to not use index hints, but if performance is killing
you...( update statistics first, then see if you get better response).
select yad yad from table WITH (index = whatever)
Be sure to use the with clause for compatilibility with SQL 2005
> Thanks in Advance for any Suggestion and help for these 2 posts...
> Thanks
> Prabhat
>
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:#J1NJu$vEHA.3096@.TK2MSFTNGP14.phx.gbl...
> uses
>|||Hi Wayne,
Thanks for your Suggestions.
Reg the 2 Queries:
Yes They are searching for different Rows. And the 1st Query is Retrieving 2
Rows while the 2nd Query returns 5622 Rows.
So As you told SQL Server optimizer will Use Index Seek for 1st Query and
Index Scan for 2nd Query?
Then If I use "Index=" Keyword in the Second Query then that the 2nd Query
uses the Index Seek. Why is like that?
And Now If I write :
select * from TABLE with(index = ind_CMP_name) where lastname like 'smith%'
So This is Better then using Only "Index=" as this is Also Supported in
2005?
Thanks
Prabhat
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:e6A#GRBwEHA.1400@.TK2MSFTNGP11.phx.gbl...
> The 2 queries in your list are not the same. They are searching for
> different rows, and a different number of rows will be returned for each.
> This is called selectivity - If a very small percentage of rows in the
table
> will be returned ( 3-5%) then the query is very selective. Index Seeks are
> better for very selective queries and index scans or better for queries
with
> low selectivity. SQL Server's optimizer is smart enough to figure this out
> and (generally) choose a good plan..
> see inline
> --
> 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
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:%23ByYLXAwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> Index seek does a binary search from the root to the leaf level, a Scan
> reads through part of all of the leaf level... So scans generally do more
> IO than seeks.
the[vbcol=seagreen]
> It is preferable to not use index hints, but if performance is killing
> you...( update statistics first, then see if you get better response).
> select yad yad from table WITH (index = whatever)
> Be sure to use the with clause for compatilibility with SQL 2005
Query[vbcol=seagreen]
uses[vbcol=seagreen]
'smith%'[vbcol=seagreen]
uses[vbcol=seagreen]
same[vbcol=seagreen]
>|||Prabhat
If you use the (INDEX = ..) hint you are FORCING SQL Server to use the index
you tell it to use, whether or not that is a good choice. If you measure the
peformance (perhaps SET STATISTICS IO ON) you will see that when you force
the index, the performance is worse than when you let SQL Server make its
own choice, and it chooses to do the scan.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:uuZ%23H4BwEHA.2944@.TK2MSFTNGP12.phx.gbl...
> Hi Wayne,
> Thanks for your Suggestions.
> Reg the 2 Queries:
> Yes They are searching for different Rows. And the 1st Query is Retrieving
> 2
> Rows while the 2nd Query returns 5622 Rows.
> So As you told SQL Server optimizer will Use Index Seek for 1st Query and
> Index Scan for 2nd Query?
> Then If I use "Index=" Keyword in the Second Query then that the 2nd Query
> uses the Index Seek. Why is like that?
> And Now If I write :
> select * from TABLE with(index = ind_CMP_name) where lastname like
> 'smith%'
> So This is Better then using Only "Index=" as this is Also Supported in
> 2005?
> Thanks
> Prabhat
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:e6A#GRBwEHA.1400@.TK2MSFTNGP11.phx.gbl...
> table
> with
> the
> Query
> uses
> 'smith%'
> uses
> same
>|||Hi Kalen,
Thanks for reply.
I use the "Index =" mainly for 2 reasons.
1) My SQL Query uses 2 Conditions in where clause. And I can see that there
is a Index Scan Involve in that Query. So I prefer "Index =" which make
Index Seek.
2) In Some cases My output should be Order by Lastname, FirstName. And Also
the query will have the Where Clause as above. So Here also i can see some
time it uses Index Scan. And I use a Compound Index on Lastname, Firstname -
To get the order. So I use the Index= in this case also.
You can see the Example of Query in the Main (TOP / original Post).
Kindly suggest.
Thanks
Prabhat
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:#h9EUmCwEHA.1264@.TK2MSFTNGP12.phx.gbl...
> Prabhat
> If you use the (INDEX = ..) hint you are FORCING SQL Server to use the
index
> you tell it to use, whether or not that is a good choice. If you measure
the
> peformance (perhaps SET STATISTICS IO ON) you will see that when you force
> the index, the performance is worse than when you let SQL Server make its
> own choice, and it chooses to do the scan.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:uuZ%23H4BwEHA.2944@.TK2MSFTNGP12.phx.gbl...
Retrieving[vbcol=seagreen]
and[vbcol=seagreen]
Query[vbcol=seagreen]
each.[vbcol=seagreen]
Think[vbcol=seagreen]
on[vbcol=seagreen]
>|||Prabhat wrote:
> Hi Kalen,
> Thanks for reply.
> I use the "Index =" mainly for 2 reasons.
> 1) My SQL Query uses 2 Conditions in where clause. And I can see that
> there is a Index Scan Involve in that Query. So I prefer "Index ="
> which make Index Seek.
> 2) In Some cases My output should be Order by Lastname, FirstName.
> And Also the query will have the Where Clause as above. So Here also
> i can see some time it uses Index Scan. And I use a Compound Index on
> Lastname, Firstname - To get the order. So I use the Index= in this
> case also.
> You can see the Example of Query in the Main (TOP / original Post).
> Kindly suggest.
> Thanks
> Prabhat
>
Yes, you are correct that using the hint forces SQL Server to use the
index. But what Kalen is trying to explain to you is that using a
table/clustered index scan operation on the table when many rows are
returned is usually more cost effective for SQL Server. Unless you
dealing with a covering index, SQL Server has to perform a bookmark
lookup for each matching row. And all these bookmark lookups are very
costly when you consider SQL Server has to perform 5,000+ of them. In
that case, SQL Server chose to use a scan operation instead because it
is easier and faster for it to scan the table.
Now SQL Server does not always make the right decision. That's why
having updated statistics in your tables is important. But to force SQL
Server to always use the index misses the point. You are trying to
outthink the SQL Server query optimizer and that's a tough battle to win
in the long run.
David Gugick
Imceda Software
www.imceda.com|||Thanks David for your Suggestion. Can U please tell me what exactly a
Covering Index? And Does that Help in my case?
Thanks
Prabhat
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O3nP1vLwEHA.2944@.TK2MSFTNGP12.phx.gbl...
> Prabhat wrote:
> Yes, you are correct that using the hint forces SQL Server to use the
> index. But what Kalen is trying to explain to you is that using a
> table/clustered index scan operation on the table when many rows are
> returned is usually more cost effective for SQL Server. Unless you
> dealing with a covering index, SQL Server has to perform a bookmark
> lookup for each matching row. And all these bookmark lookups are very
> costly when you consider SQL Server has to perform 5,000+ of them. In
> that case, SQL Server chose to use a scan operation instead because it
> is easier and faster for it to scan the table.
> Now SQL Server does not always make the right decision. That's why
> having updated statistics in your tables is important. But to force SQL
> Server to always use the index misses the point. You are trying to
> outthink the SQL Server query optimizer and that's a tough battle to win
> in the long run.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Hi All,
Index Seek or Bookmark Lookup - Cost?
=============================
I have the below 2 Queries:
(1)
select top 100 donorid, firstname, lastname, state, zip, phonenum, country,
olddonorid, sourceid
from donor (index = ind_donor_name)
where lastname >= 'nath' and sourceid = 'flcc'
(2)
select top 100 donorid, firstname, lastname, state, zip, phonenum, country,
olddonorid, sourceid
from donor (index = ind_donor_name)
where lastname like 'nath%' and sourceid = 'flcc'
NOTE: ind_donor_name is the Compound Index on LastName, FirstName.
Even if Both the Queries are not Same in Where Condition, But Still refers
to the same Index. But I see a Different is Cost in Execution Plan.
that is:
the 1st Query Cost 1% in Index Seek and 99% in Bookmark Lookup.
But the 2nd Query Cost 51% in Index Seek and 49% in Bookmark Lookup.
[Note: Please refer the Discussions in this thread for more details...]
So As per the Above Cost Criteria which Plan is Best?
Thanks
Prabhat
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:#J1NJu$vEHA.3096@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have one table. Where :
> DonorID Int (Identity) Primary Key
> FirstName Varchar(25)
> LastName Varchar(25)
> ...
> ...
> I have One nonclustred index on Lastname another nonclustred index on
> (lastname, firstname).
> Suppose I Execute the Query:
> select * from TABLE where lastname like 'abott%' (This Query
uses
> Index Seek on the Compound Index)
> But if I use the below Query:
> select * from TABLE where lastname like 'smith%' (This Query uses
> Index Scan)
> But
> select * from TABLE (index = ind_CMP_name) where lastname like 'smith%'
> (But this Query uses the Index Seek)
> NOTE: ind_CMP_name is the Compound Index.
> Why there is the Difference, One Query uses Index Seek while other uses
> Index Scan, even if both the query uses the same where condition on same
> column?
> Thanks
> Prabhat
>|||Prabhat,
You are comparing apples to oranges. The queries are different, so
what do you mean "which plan is best"?
If I said "I can either pay for a new bicycle with a check or pay for
a new television with cash. Which is better?" -- well, it depends on
whether you need a bicycle or a television.
SK
Prabhat wrote:

>Hi All,
>Index Seek or Bookmark Lookup - Cost?
>=============================
>I have the below 2 Queries:
>(1)
>select top 100 donorid, firstname, lastname, state, zip, phonenum, country
,
>olddonorid, sourceid
>from donor (index = ind_donor_name)
>where lastname >= 'nath' and sourceid = 'flcc'
>(2)
>select top 100 donorid, firstname, lastname, state, zip, phonenum, country
,
>olddonorid, sourceid
>from donor (index = ind_donor_name)
>where lastname like 'nath%' and sourceid = 'flcc'
>NOTE: ind_donor_name is the Compound Index on LastName, FirstName.
>Even if Both the Queries are not Same in Where Condition, But Still refers
>to the same Index. But I see a Different is Cost in Execution Plan.
>that is:
>the 1st Query Cost 1% in Index Seek and 99% in Bookmark Lookup.
>But the 2nd Query Cost 51% in Index Seek and 49% in Bookmark Lookup.
>[Note: Please refer the Discussions in this thread for more details...]
>So As per the Above Cost Criteria which Plan is Best?
>Thanks
>Prabhat
>"Prabhat" <not_a_mail@.hotmail.com> wrote in message
>news:#J1NJu$vEHA.3096@.TK2MSFTNGP14.phx.gbl...
>
>uses
>
>
>

Index scans while using PreparedStatements

I have a query that does a 3-table join. The tables involved
are
1. ct_list_item(li_key int, li_code nvarchar(329))
li_key is the primary key
2. ct_list_item_lang(li_key int, lang_code varchar(5),
value nvarchar(64))
li_key and lang_code form a 2-part primary key.
3. ct_list_item_map(list_key int, li_key int, list_level int)
list_key and li_key form a 2-part primary key.
All of these tables have clustered indexes on their primary
keys.
Here's the query:
SELECT map.list_key, map.li_key, lil.value, li.li_code
FROM ct_list_item li
JOIN ct_list_item_map map on map.li_key = li.li_key
JOIN ct_list_item_lang lil on li.li_key = lil.li_key and
lil.lang_code='en'
WHERE map.list_key= 1011
I am finding that when I run the query using a JDBC
PreparedStatement with bind variables (on lang_code and
list_key), the query performs an index scan over the
clustered index on the ct_list_item_lang's primary key.
However if I run the query using a JDBC Statement without
bind variables, it does an clustered index seek. I am
puzzled as to why there is a difference.
Since we hard code values in the query statement, the SQL knows the values
before hand and can use Index seek. For
preparedstatement using parameters SQL has no knowledge about the value for
each
parameter during the preparation hence Index Scan is used. This results in
Preparedstatement running slower than regular Statement with hard coded
query.
If you need to use parameterized query in code, in stead of using ad hoc
query, you can create a stored proc and call it from Java code. This
should generate a plan using Index Seek which results in better performance.
sql

index replication from DB2

Has any body done a replication of indexes or Primary keys from DB2 to SQL 2005 tables. Is that possible and how pls?

Thank you very much.

I believe by default primary keys are replicated. Is it not working for you?

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

Gary

|||

is that stated in the msdn document u sent me?

or does it say so in any other document, so I can show it to the person who said it s not possible<

Thanks a lot for your help.

|||

If you take a look at sp_addarticle at its @.schema_option, there is option to set for primary key constraint.

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

If you look at the default schema option for all the log based article type 0x80 are enabled for all of them. I think this article is the closest I can think of. I'm pretty sure, it's on by default.

Also a quick test probably won't hurt, just create a simple table with two integer columns and make the first column the primary key, then replicate to db2. Seeing is believing :-)

Gary

|||actually we re replicating from DB2 to SQL and not the other way? is it the same?
sorry but i never done replication before. Thanks again for your help
|||

I misread your posting... I think that's the question for DB2 Replication forum.

Regards,

Gary

index replication from DB2

Has any body done a replication of indexes or Primary keys from DB2 to SQL 2005 tables. Is that possible and how pls?

Thank you very much.

I believe by default primary keys are replicated. Is it not working for you?

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

Gary

|||

is that stated in the msdn document u sent me?

or does it say so in any other document, so I can show it to the person who said it s not possible<

Thanks a lot for your help.

|||

If you take a look at sp_addarticle at its @.schema_option, there is option to set for primary key constraint.

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

If you look at the default schema option for all the log based article type 0x80 are enabled for all of them. I think this article is the closest I can think of. I'm pretty sure, it's on by default.

Also a quick test probably won't hurt, just create a simple table with two integer columns and make the first column the primary key, then replicate to db2. Seeing is believing :-)

Gary

|||actually we re replicating from DB2 to SQL and not the other way? is it the same?
sorry but i never done replication before. Thanks again for your help
|||

I misread your posting... I think that's the question for DB2 Replication forum.

Regards,

Gary

index replication from DB2

Has any body done a replication of indexes or Primary keys from DB2 to SQL 2005 tables. Is that possible and how pls?

Thank you very much.

I believe by default primary keys are replicated. Is it not working for you?

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

Gary

|||

is that stated in the msdn document u sent me?

or does it say so in any other document, so I can show it to the person who said it s not possible<

Thanks a lot for your help.

|||

If you take a look at sp_addarticle at its @.schema_option, there is option to set for primary key constraint.

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

If you look at the default schema option for all the log based article type 0x80 are enabled for all of them. I think this article is the closest I can think of. I'm pretty sure, it's on by default.

Also a quick test probably won't hurt, just create a simple table with two integer columns and make the first column the primary key, then replicate to db2. Seeing is believing :-)

Gary

|||actually we re replicating from DB2 to SQL and not the other way? is it the same?
sorry but i never done replication before. Thanks again for your help
|||

I misread your posting... I think that's the question for DB2 Replication forum.

Regards,

Gary

sql

Index related problems? Whats happening here?

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

index questions

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

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

Index Question

Hello,

In MSS 2k can Primary Key, Unique Constraints, Indexes and Foreign Keys be
disabled? If Indexes (PK's and UN Constraints) can be disabled what happens
if data is inserted while disable? Will the index be rebuilt when enabled?

Thanks,
Rob PanoshRob Panosh (rob_!!!NO!!!SPAM!!!_panosh@.asdsoftadfdware.com) writes:
> In MSS 2k can Primary Key, Unique Constraints, Indexes and Foreign Keys
> be disabled? If Indexes (PK's and UN Constraints) can be disabled what
> happens if data is inserted while disable? Will the index be rebuilt
> when enabled?

You can disable foreign-key constraints. When you re-enable them, SQL
Server verifies that the data comply to the constraint.

You cannot disable primary-key or unique constraints, nor indexes.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks ...

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns940E15C48E6CYazorman@.127.0.0.1...
> Rob Panosh (rob_!!!NO!!!SPAM!!!_panosh@.asdsoftadfdware.com) writes:
> > In MSS 2k can Primary Key, Unique Constraints, Indexes and Foreign Keys
> > be disabled? If Indexes (PK's and UN Constraints) can be disabled what
> > happens if data is inserted while disable? Will the index be rebuilt
> > when enabled?
> You can disable foreign-key constraints. When you re-enable them, SQL
> Server verifies that the data comply to the constraint.
> You cannot disable primary-key or unique constraints, nor indexes.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Unfortunately, SQL-Server does not automatically check the existing
table data after turning a constraint back on. IMO it is a Microsoft
mistake to allow a database to get corrupted this way.

Gert-Jan

Erland Sommarskog wrote:
> Rob Panosh (rob_!!!NO!!!SPAM!!!_panosh@.asdsoftadfdware.com) writes:
> > In MSS 2k can Primary Key, Unique Constraints, Indexes and Foreign Keys
> > be disabled? If Indexes (PK's and UN Constraints) can be disabled what
> > happens if data is inserted while disable? Will the index be rebuilt
> > when enabled?
> You can disable foreign-key constraints. When you re-enable them, SQL
> Server verifies that the data comply to the constraint.
> You cannot disable primary-key or unique constraints, nor indexes.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Gert-Jan Strik (sorry@.toomuchspamalready.nl) writes:
> Unfortunately, SQL-Server does not automatically check the existing
> table data after turning a constraint back on.

You're right, Gert-Jan. Thanks for correcting my mistake.

> IMO it is a Microsoft mistake to allow a database to get corrupted this
> way.

I can see situations where you may want this, but its deceivable that
the constraint is not rechecked. Not only it makes you think that you
have a sound table. If you use the column with a CHECK constraint in a
partitioned view, you will scratch your hair, trying to find out why
SQL Server accesses all tables after this operation.

It is possible to identify this situation though. The example is
augmented script from Books Online:

SET QUOTED_IDENTIFIER OFF
go
CREATE TABLE cnst_example
(id INT NOT NULL,
name VARCHAR(10) NOT NULL,
salary MONEY NOT NULL
CONSTRAINT salary_cap CHECK (salary < 100000)
)
go
-- Valid inserts
INSERT INTO cnst_example VALUES (1,"Joe Brown",65000)
INSERT INTO cnst_example VALUES (2,"Mary Smith",75000)
go
-- This insert violates the constraint.
INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)
go
-- Disable the constraint and try again.
ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap
INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)
go
-- Reenable the constraint and try another insert, will fail.
ALTER TABLE cnst_example CHECK CONSTRAINT salary_cap
INSERT INTO cnst_example VALUES (4,"Eric James",110000)
go
-- Returns 1, because constraint has been disabled.
select objectproperty(object_id('salary_cap'), 'CnstIsNotTrusted')

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"select objectproperty(object_id('salary_cap'), 'CnstIsNotTrusted')"

Interesting, I did not know this yet. Something for a standard script to
check the database...

Gert-Jan

Erland Sommarskog wrote:
> Gert-Jan Strik (sorry@.toomuchspamalready.nl) writes:
> > Unfortunately, SQL-Server does not automatically check the existing
> > table data after turning a constraint back on.
> You're right, Gert-Jan. Thanks for correcting my mistake.
> > IMO it is a Microsoft mistake to allow a database to get corrupted this
> > way.
> I can see situations where you may want this, but its deceivable that
> the constraint is not rechecked. Not only it makes you think that you
> have a sound table. If you use the column with a CHECK constraint in a
> partitioned view, you will scratch your hair, trying to find out why
> SQL Server accesses all tables after this operation.
> It is possible to identify this situation though. The example is
> augmented script from Books Online:
> SET QUOTED_IDENTIFIER OFF
> go
> CREATE TABLE cnst_example
> (id INT NOT NULL,
> name VARCHAR(10) NOT NULL,
> salary MONEY NOT NULL
> CONSTRAINT salary_cap CHECK (salary < 100000)
> )
> go
> -- Valid inserts
> INSERT INTO cnst_example VALUES (1,"Joe Brown",65000)
> INSERT INTO cnst_example VALUES (2,"Mary Smith",75000)
> go
> -- This insert violates the constraint.
> INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)
> go
> -- Disable the constraint and try again.
> ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap
> INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)
> go
> -- Reenable the constraint and try another insert, will fail.
> ALTER TABLE cnst_example CHECK CONSTRAINT salary_cap
> INSERT INTO cnst_example VALUES (4,"Eric James",110000)
> go
> -- Returns 1, because constraint has been disabled.
> select objectproperty(object_id('salary_cap'), 'CnstIsNotTrusted')
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Index Question

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

Index question

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

index question

Hi,
I have two tables -- A & B, which have primary key and
content lots of records respectively. B table has
foreign key refer to A table. If I create a index for
this foreign key in table B, will it improve performance
when I join these two tables in my query? Any suggestion
to improve the performance during join?
Thank you.
YulingYes It may improve a performance
Also look at join hints on BOL.
"Yuling" <ytu@.creativelabs.com> wrote in message
news:044601c35ade$51d3cb20$a601280a@.phx.gbl...
> Hi,
> I have two tables -- A & B, which have primary key and
> content lots of records respectively. B table has
> foreign key refer to A table. If I create a index for
> this foreign key in table B, will it improve performance
> when I join these two tables in my query? Any suggestion
> to improve the performance during join?
> Thank you.
> Yuling|||You should generally index all primary and foreign keys when doing joins...
If there are where clauses, you may see performance improvements if you
create non-clustered index on one of the highly selective where clause
criteria.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it community
of SQL Server professionals.
www.sqlpass.org
"Yuling" <ytu@.creativelabs.com> wrote in message
news:044601c35ade$51d3cb20$a601280a@.phx.gbl...
> Hi,
> I have two tables -- A & B, which have primary key and
> content lots of records respectively. B table has
> foreign key refer to A table. If I create a index for
> this foreign key in table B, will it improve performance
> when I join these two tables in my query? Any suggestion
> to improve the performance during join?
> Thank you.
> Yuling

Index Question

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

Index or not to index

I have a "Products" table, which currently only has the idProduct primary
key indexed. I now have a requirement to perform searches through an ASP
front end (ASP front end issuing SQL directly against SQL using SQL Server
OLE DB Provider) on
- a details text(16) field
- a description varchar(250) field
- a descriptionLong varchar(250) field
At any time, this table would have around 150-300 records, although records
do get added and deleted from time to time. I am considering Creating an
INDEX on details, description and descriptionLong field. However, I am
concerned
- whether creating an index on such a relatively small table would really
give performance gain or would it just add towards "inefficiency"
- if creating index is deemed a good idea, then what kind of index should I
use?
- Since records get added and deleted from time to time, would it be a good
idea to "re-index" the table (presumably, as part of the DB Maintenance
plan)?
Many thanks in advance!I think you shouldn't create an index because this index will be hardly used
but will decrease performance of DML commands execution
"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:OjTbwYXQEHA.620@.TK2MSFTNGP10.phx.gbl...
> I have a "Products" table, which currently only has the idProduct primary
> key indexed. I now have a requirement to perform searches through an ASP
> front end (ASP front end issuing SQL directly against SQL using SQL Server
> OLE DB Provider) on
> - a details text(16) field
> - a description varchar(250) field
> - a descriptionLong varchar(250) field
> At any time, this table would have around 150-300 records, although
records
> do get added and deleted from time to time. I am considering Creating an
> INDEX on details, description and descriptionLong field. However, I am
> concerned
> - whether creating an index on such a relatively small table would really
> give performance gain or would it just add towards "inefficiency"
> - if creating index is deemed a good idea, then what kind of index should
I
> use?
> - Since records get added and deleted from time to time, would it be a
good
> idea to "re-index" the table (presumably, as part of the DB Maintenance
> plan)?
> Many thanks in advance!
>|||Definitelly not to index. It is too small, index fields are too large...|||Hi Patrick,
Alex Cieszinski and Bojidar Alexandrov has give you their suggestions :) I
wanted to post a quick note to see if you would like additional assistance
or information regarding this particular issue.
We appreciate your patience and look forward to hearing from you!
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
---
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Are you Michael or Mingqing after all :)|||Hi Bojidar,
Michael is Mingqing :)
Thank you :D
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
---
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

index on PK

Hello, could someone please tell me why you would index a Primary Key column. By default is it not already indexed, and isn't a primary key unique? If so why would you index a unique column? I'm sure this is a dumb question and I probably don't understand the concept of indexes in SQL. Any insight is well appreciated. Thanks.BOL:

When you specify a PRIMARY KEY constraint for a table, Microsoft SQL Server 2000 enforces data uniqueness by creating a unique index for the primary key columns. This index also permits fast access to data when the primary key is used in queries.

Index on IsRowGuidCol ?

For merge replication I have only a few tables where the primary key spans
two columns. For the rest of the tables the primary key is one column and
is a uniqueidentifier.
For the fre tables where it spans two columns, I have to add an additional
uniqueidentifier column and mark it as the RowGuid Column for merge
replication.
My Question:
Will adding an index on this column speed merge replication?
--Pepto
Merge replication will add a unique index to the column that it uses for
merge replication. I believe in this case that it will create another
rowguid column to use.
Three conditions have to be met to precreate a rowguid column for merge
replication to use
1) it is called rowguid
2) its data type is uniqueidentifier ROWGUIDCOLUMN
3) it has a unique index on it.
If any of the three conditions are not met, merge replication will create
another column to use that meets these conditions although its name will be
slightly different - rowguidi where i increments.
"Pepto" <pepto@.hotmail.com> wrote in message
news:Ooh2BduPFHA.164@.TK2MSFTNGP12.phx.gbl...
> For merge replication I have only a few tables where the primary key spans
> two columns. For the rest of the tables the primary key is one column and
> is a uniqueidentifier.
> For the fre tables where it spans two columns, I have to add an additional
> uniqueidentifier column and mark it as the RowGuid Column for merge
> replication.
> My Question:
> Will adding an index on this column speed merge replication?
> --Pepto
>
>

index of multiple columns vs. mutliple index of 1 column?

If I have this hierarchy in my db, there are many Objects in a Room, and
there are many Rooms in a Warehouse:
Object
(
ObjectId uniqueidentifier primary key clustered index,
RoomId uniqueidnetifier not null foreign key references Room( RoomID )
WarehouseId uniqueidentifer not null foreign key references Warehouse(
WarehouseId )
many more columns here about the object...
)
Room
(
RoomId uniqueidentifier primary key clustered index,
WarehouseId uniqueidentifer not null foreign key references Warehouse(
WarehouseId )
)
if I frequently do these statements:
select *
from object
where RoomId = 'xyz'
select *
from object
where Warehouse = 'abc'
What would an experienced db designer create for index for the Object table?
Two indexes: one on RoomId, and one on WarehouseId; OR just on combo index
(WarehouseId, RoomId)?
Thank you very much.I would suggest 2 separate indexes since you can not take advantage of a
composite index when searching by the 2nd column alone.
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:ORPZTkkjFHA.2472@.TK2MSFTNGP15.phx.gbl...
> If I have this hierarchy in my db, there are many Objects in a Room, and
> there are many Rooms in a Warehouse:
> Object
> (
> ObjectId uniqueidentifier primary key clustered index,
> RoomId uniqueidnetifier not null foreign key references Room( RoomID )
> WarehouseId uniqueidentifer not null foreign key references Warehouse(
> WarehouseId )
> many more columns here about the object...
> )
> Room
> (
> RoomId uniqueidentifier primary key clustered index,
> WarehouseId uniqueidentifer not null foreign key references Warehouse(
> WarehouseId )
> )
> if I frequently do these statements:
> select *
> from object
> where RoomId = 'xyz'
> select *
> from object
> where Warehouse = 'abc'
> What would an experienced db designer create for index for the Object
> table?
> Two indexes: one on RoomId, and one on WarehouseId; OR just on combo index
> (WarehouseId, RoomId)?
> Thank you very much.|||On Thu, 21 Jul 2005 16:03:37 -0700, Zeng wrote:

>If I have this hierarchy in my db, there are many Objects in a Room, and
>there are many Rooms in a Warehouse:
>Object
>(
> ObjectId uniqueidentifier primary key clustered index,
> RoomId uniqueidnetifier not null foreign key references Room( RoomID )
> WarehouseId uniqueidentifer not null foreign key references Warehouse(
>WarehouseId )
> many more columns here about the object...
> )
>Room
>(
> RoomId uniqueidentifier primary key clustered index,
> WarehouseId uniqueidentifer not null foreign key references Warehouse(
>WarehouseId )
> )
>if I frequently do these statements:
>select *
>from object
>where RoomId = 'xyz'
>select *
>from object
>where Warehouse = 'abc'
>What would an experienced db designer create for index for the Object table
?
>Two indexes: one on RoomId, and one on WarehouseId; OR just on combo index
>(WarehouseId, RoomId)?
Hi Zeng,
An experienced DB designer would not create an index for the Object
table at all. Instead, an experienced DB designer would first eliminate
some major flaws from the existing design.
1. "If you have a watch, you always know exactly how late it is. If you
have two watches, you never know."
In other words: don't store redundant information. The WarehouseId is
functionally dependent on room, so it should be in the Rooms table only,
not in the Objects table (where it is not only redundant, but violating
third normal form as well!)
2. If you want (*) to use surrogate keys, then at least remember that
they are a surrogate for something. A table with only a surrogate key is
a sure way to get unwanted duplicates, and lots of problems when you
need to remove them. Always use the natural key in addition to the
surrogate, and don't forget to declare a UNIQUE constraint for it.
(*) Whether or not you should want to use surrogate keys is another
subject. Or rather: holy war. I'd rather not go there right now :-)
3. Also, if you need surrogate keys, IDENTITY is almost always the
better choice. UNIQUEIDENTIFIER should only be used in the cases where
it is really needed - and those are scarce!
4. Finally, an experienced DB designer would never ever use SELECT * in
production code. Always list the columns you need. It saves bandwidth,
and it gives the optimizer the possibility to consider alternative plans
if there are covering indexes.
Once you have corrected all the above, feel free to repost. My first
guess is that nonclustered indexes for the foreign key columns would
suffice for the queries you gave, but that's just a quick guess, since I
don't know how your revised schema and queries will look.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

index of multiple columns vs. mutliple index of 1 column?

If I have this hierarchy in my db, there are many Objects in a Room, and
there are many Rooms in a Warehouse:
Object
(
ObjectId uniqueidentifier primary key clustered index,
RoomId uniqueidnetifier not null foreign key references Room( RoomID )
WarehouseId uniqueidentifer not null foreign key references Warehouse(
WarehouseId )
many more columns here about the object...
)
Room
(
RoomId uniqueidentifier primary key clustered index,
WarehouseId uniqueidentifer not null foreign key references Warehouse(
WarehouseId )
)
if I frequently do these statements:
select *
from object
where RoomId = 'xyz'
select *
from object
where Warehouse = 'abc'
What would an experienced db designer create for index for the Object table?
Two indexes: one on RoomId, and one on WarehouseId; OR just on combo index
(WarehouseId, RoomId)?
Thank you very much.
I would suggest 2 separate indexes since you can not take advantage of a
composite index when searching by the 2nd column alone.
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:ORPZTkkjFHA.2472@.TK2MSFTNGP15.phx.gbl...
> If I have this hierarchy in my db, there are many Objects in a Room, and
> there are many Rooms in a Warehouse:
> Object
> (
> ObjectId uniqueidentifier primary key clustered index,
> RoomId uniqueidnetifier not null foreign key references Room( RoomID )
> WarehouseId uniqueidentifer not null foreign key references Warehouse(
> WarehouseId )
> many more columns here about the object...
> )
> Room
> (
> RoomId uniqueidentifier primary key clustered index,
> WarehouseId uniqueidentifer not null foreign key references Warehouse(
> WarehouseId )
> )
> if I frequently do these statements:
> select *
> from object
> where RoomId = 'xyz'
> select *
> from object
> where Warehouse = 'abc'
> What would an experienced db designer create for index for the Object
> table?
> Two indexes: one on RoomId, and one on WarehouseId; OR just on combo index
> (WarehouseId, RoomId)?
> Thank you very much.
|||On Thu, 21 Jul 2005 16:03:37 -0700, Zeng wrote:

>If I have this hierarchy in my db, there are many Objects in a Room, and
>there are many Rooms in a Warehouse:
>Object
>(
> ObjectId uniqueidentifier primary key clustered index,
> RoomId uniqueidnetifier not null foreign key references Room( RoomID )
> WarehouseId uniqueidentifer not null foreign key references Warehouse(
>WarehouseId )
> many more columns here about the object...
>)
>Room
>(
> RoomId uniqueidentifier primary key clustered index,
> WarehouseId uniqueidentifer not null foreign key references Warehouse(
>WarehouseId )
>)
>if I frequently do these statements:
>select *
>from object
>where RoomId = 'xyz'
>select *
>from object
>where Warehouse = 'abc'
>What would an experienced db designer create for index for the Object table?
>Two indexes: one on RoomId, and one on WarehouseId; OR just on combo index
>(WarehouseId, RoomId)?
Hi Zeng,
An experienced DB designer would not create an index for the Object
table at all. Instead, an experienced DB designer would first eliminate
some major flaws from the existing design.
1. "If you have a watch, you always know exactly how late it is. If you
have two watches, you never know."
In other words: don't store redundant information. The WarehouseId is
functionally dependent on room, so it should be in the Rooms table only,
not in the Objects table (where it is not only redundant, but violating
third normal form as well!)
2. If you want (*) to use surrogate keys, then at least remember that
they are a surrogate for something. A table with only a surrogate key is
a sure way to get unwanted duplicates, and lots of problems when you
need to remove them. Always use the natural key in addition to the
surrogate, and don't forget to declare a UNIQUE constraint for it.
(*) Whether or not you should want to use surrogate keys is another
subject. Or rather: holy war. I'd rather not go there right now :-)
3. Also, if you need surrogate keys, IDENTITY is almost always the
better choice. UNIQUEIDENTIFIER should only be used in the cases where
it is really needed - and those are scarce!
4. Finally, an experienced DB designer would never ever use SELECT * in
production code. Always list the columns you need. It saves bandwidth,
and it gives the optimizer the possibility to consider alternative plans
if there are covering indexes.
Once you have corrected all the above, feel free to repost. My first
guess is that nonclustered indexes for the foreign key columns would
suffice for the queries you gave, but that's just a quick guess, since I
don't know how your revised schema and queries will look.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)