Post reply

Using an Index and not using an Index versus collation

  • May 25, 2012 at 3:17 am

    #150942

    All,

    Just want to run something past the experts.

    We have a simple sql statement that does a select from a table of over 2.5 million rows based on the primary key. e.g.

    select col1, col2, col3 from table where col1=N'12345'

    Table is defined with col1 as varchar(16), and col1 is primary key.

    Yes I know the value is cast as nvarchar, but I have no control over this as it is coming from an application.

    On most of our system this query uses the index on col1 to do an clustered index seek, however on one database it does a clustered index scan.

    The only difference I can see is that on the databases where it does an index seek the collation is Latin1_General_CI_AS and where it does the index scan it is SQL_Latin1_General_CP1_CI_AS.

    Could this collation difference be the cause of the index scan.

    Thanks

    Ronnie

  • May 25, 2012 at 4:51 am

    #1493045

    It's certainly possible that's the cause, but I'd be more concerned that the query is identical between both databases and, more importantly, that the statistics are updated in exactly the same way between both databases.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • May 25, 2012 at 5:16 am

    #1493062

    Yes, it is the cause.

    The seek is still not an efficient one, if you check the exec plan you'll see a seek predicate and a secondary predicate.

    If you can't change the app, i'd suggest considering changing the column to nvarchar

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 25, 2012 at 6:05 am

    #1493085

    Hi,

    I tried to replicate this issue on one of our internal servers so I created a new database with the collation set to SQL_LATAN1_GENERAL_CP1_CI_AS but could not replicate it.

    So I am a bit stumped now.

    Thanks

    Ronnie

  • May 25, 2012 at 6:20 am

    #1493097

    All,

    Just replicated on a SQL Server 2005 SP2 system, could not replicate it on SP4.

    Thanks

    Ronnie

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply