October 18, 2012 at 4:21 am
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.
October 18, 2012 at 4:40 am
Refer to the following link
http://www.mssqltips.com/sqlservertip/1793/sql-server-indexing-basics/
October 18, 2012 at 5:02 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 18, 2012 at 5:44 am
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.
October 18, 2012 at 6:55 am
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.
October 18, 2012 at 7:28 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 18, 2012 at 7:38 am
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.
October 18, 2012 at 7:53 am
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
October 19, 2012 at 12:05 pm
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.
October 19, 2012 at 6:30 pm
If you're on enterprise edition, 4 filtered indexes (1 for each distinct value) might be your best bet.
October 20, 2012 at 3:38 am
SpringTownDBA (10/19/2012)
If you're on enterprise edition, 4 filtered indexes (1 for each distinct value) might be your best bet.
Filtered indexes aren't an Enterprise-only feature.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 20, 2012 at 11:13 am
sunny.tjk,
Why don't you try both solution ideas:
1. One large, low cardinality index
2. Four filtered indexes
and let us know which performs the best.
I'd like to know.
October 20, 2012 at 1:17 pm
There's a third option: add included columns to this index, so that it becomes a covering index for the critical query. Then the optimiser sees that there are no lookup costs, so it won't rule this index out on cost of lookups grounds. Of course there is some storage penalty, as there always in when adding included columns.
Tom
October 20, 2012 at 1:19 pm
Lee Crain (10/20/2012)
sunny.tjk,Why don't you try both solution ideas:
1. One large, low cardinality index
2. Four filtered indexes
and let us know which performs the best.
Unless covering, SQL will ignore both options and scan the cluster. The query will return far too many rows for seek or scan of filtered index and then all the key lookups to be efficient.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 20, 2012 at 2:23 pm
Gail,
If the original poster creates an index on just the column with the 4 values, will this not force the Optimizer to use that index?
"SELECT <column names> FROM <table name> WITH (INDEX(<index name>))"
I've done it in production and it's prevented a clustered index scan.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply