Table design and query performance

• bugg

  SSCertifiable

  Points: 7567

  More actions

  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

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  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

• bugg

  SSCertifiable

  Points: 7567

  More actions

  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