<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 Administration
»
Index question
25 posts, Page 1 of 3
1
2
3
»
»»
Index question
Rate Topic
Display Mode
Topic Options
Author
Message
sunny.tjk
sunny.tjk
Posted Thursday, October 18, 2012 4:21 AM
SSC Veteran
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 6:26 PM
Points: 263,
Visits: 866
I indexed a char datatype column but my query is still doing a table scan. So, indexes can be applied only on columns with integer values? This table has 50 million rows and the column has 4 distinct values.
Post #1374254
vyas
vyas
Posted Thursday, October 18, 2012 4:40 AM
Hall of Fame
Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:02 AM
Points: 3,131,
Visits: 1,056
Refer to the following link
http://www.mssqltips.com/sqlservertip/1793/sql-server-indexing-basics/
Post #1374263
GilaMonster
GilaMonster
Posted Thursday, October 18, 2012 5:02 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744,
Visits: 30,025
No, indexes can be applied to any column or set of columns under 900 bytes wide in total.
http://www.sqlservercentral.com/articles/Indexing/68439/
http://www.sqlservercentral.com/articles/Indexing/68563/
http://www.sqlservercentral.com/articles/Indexing/68636/
Your index likely isn't covering, hence it's more efficient to just scan the table.
http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1374272
Suresh B.
Suresh B.
Posted Thursday, October 18, 2012 5:44 AM
Ten Centuries
Group: General Forum Members
Last Login: Wednesday, May 22, 2013 4:49 AM
Points: 1,075,
Visits: 5,119
sunny.tjk (10/18/2012)
So, indexes can be applied only on columns with integer values?
If it were so, SQL Server would not have allowed you to create index on char type column.
Post #1374288
sunny.tjk
sunny.tjk
Posted Thursday, October 18, 2012 6:55 AM
SSC Veteran
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 6:26 PM
Points: 263,
Visits: 866
Suresh B. (10/18/2012)
sunny.tjk (10/18/2012)
So, indexes can be applied only on columns with integer values?
If it were so, SQL Server would not have allowed you to create index on char type column.
Actually I meant to ask if the indexes would be useful if created on char type columns.
Post #1374311
GilaMonster
GilaMonster
Posted Thursday, October 18, 2012 7:28 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744,
Visits: 30,025
Of course they are. Just as useful as on an int, date, decimal, numeric or any other data type that's legal for index key.
Please read the links I posted.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1374348
sunny.tjk
sunny.tjk
Posted Thursday, October 18, 2012 7:38 AM
SSC Veteran
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 6:26 PM
Points: 263,
Visits: 866
GilaMonster (10/18/2012)
Of course they are. Just as useful as on an int, date, decimal, numeric or any other data type that's legal for index key.
Please read the links I posted.
Got it, Gail. I'll go ahead and read them.
Thanks a lot.
Post #1374359
John Mitchell-245523
John Mitchell-245523
Posted Thursday, October 18, 2012 7:53 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 2:18 PM
Points: 4,434,
Visits: 7,219
Nobody has so far commented on the four distinct values in your column. In one of Gail's articles that you're about to read, it says this:
It usually works out that the number of rows where the optimiser decides that key/RID lookups are too expensive is somewhere around 0.5%-1% of the total rows in the table
Therefore your index isn't likely to be very useful, unless the four values are massively unevenly distributed, or unless the index can be used as a covering index.
John
Post #1374376
Gail Wanabee
Gail Wanabee
Posted Friday, October 19, 2012 12:05 PM
SSC Veteran
Group: General Forum Members
Last Login: Monday, May 06, 2013 2:26 PM
Points: 254,
Visits: 1,029
sunny.tjk (10/18/2012)
I indexed a char datatype column but my query is still doing a table scan. So, indexes can be applied only on columns with integer values? This table has 50 million rows and the column has 4 distinct values.
The cardinality of that non-clustered index containing only that one column is very low so it is not particularly useful. As someone pointed it, it would be best if it was part of an INCLUDE clause in another index to create a covered index. However, performing a full table scan on the clustered index can be really expensive. If that index is your only criterion for data selection, it would be more resource efficient if the Query Optimizer performed a full non-clustered index scan than a full clustered index scan.
You can force the Query Optimizer to use your index by using a table hint:
"SELECT <column names> FROM <table name> WITH (INDEX(<index name>))"
Try this and see if it reduces your query execution time.
Post #1374980
SpringTownDBA
SpringTownDBA
Posted Friday, October 19, 2012 6:30 PM
Old Hand
Group: General Forum Members
Last Login: 2 days ago @ 9:19 AM
Points: 316,
Visits: 1,484
If you're on enterprise edition, 4 filtered indexes (1 for each distinct value) might be your best bet.
Post #1375069
« Prev Topic
|
Next Topic »
25 posts, Page 1 of 3
1
2
3
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
