Post reply

Table design and query performance

  • March 27, 2013 at 6:37 am

    #291585

    Hi All,

    I'm working on database where a table contains an ID that is prefixed with a 2 letter identifier type

    i.e

    AB123456

    AB345234

    AB423423

    XY456789

    XY234344

    Ideally i would like to remove the prefix completely and create a new column for this.

    However I don't have the capacity to do this now as a lot of changes would be required.

    My Question is, when querying the data, say i wanted to only get all XY records, would it be quicker if I created a new column storing these types. (AB,XY) and then filter where column = 'XY' while retaining the prefix on the id. Or create a bit column where XY is set to 1 and filter on this?

    Or since the data is already setup with the prefix is it quicker just to filter where left 2 is XY?

    Many thanks

  • March 27, 2013 at 6:42 am

    #1601111

    WHERE IDColumn LIKE 'XY%'

    Not LEFT, being a function on the column it would be non-SARGable. Like with a trailing wildcard can use indexes.

    Edit: LEFT, not like in the previous sentence.

    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

  • March 27, 2013 at 7:10 am

    #1601131

    GilaMonster (3/27/2013)

    WHERE IDColumn LIKE 'XY%'

    Not like, being a function on the column it would be non-SARGable. Like with a trailing wildcard can use indexes.

    Thanks Gila, had to look up sargable vs non-sargable, as usual SQL Server Cental is a fountain of knowledge 🙂

Viewing 3 posts - 1 through 2 (of 2 total)

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