Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Index question Expand / Collapse
Author
Message
Posted Thursday, October 18, 2012 4:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 3:00 PM
Points: 298, Visits: 1,099
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
Posted Thursday, October 18, 2012 4:40 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 8:57 AM
Points: 3,138, Visits: 1,060
Refer to the following link

http://www.mssqltips.com/sqlservertip/1793/sql-server-indexing-basics/



Post #1374263
Posted Thursday, October 18, 2012 5:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:25 AM
Points: 42,761, Visits: 35,853
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
Posted Thursday, October 18, 2012 5:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 2:55 AM
Points: 1,101, Visits: 5,280
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
Posted Thursday, October 18, 2012 6:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 3:00 PM
Points: 298, Visits: 1,099
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
Posted Thursday, October 18, 2012 7:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:25 AM
Points: 42,761, Visits: 35,853
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
Posted Thursday, October 18, 2012 7:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 3:00 PM
Points: 298, Visits: 1,099
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
Posted Thursday, October 18, 2012 7:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 5,306, Visits: 9,686
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
Posted Friday, October 19, 2012 12:05 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:35 PM
Points: 259, Visits: 1,086
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
Posted Friday, October 19, 2012 6:30 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 3:26 PM
Points: 316, Visits: 1,497
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 »

Add to briefcase 123»»»

Permissions Expand / Collapse