question

Raj More avatar image
Raj More asked

which side does the variable go on?

Seeder question

Select Col01, Col02
From TableName
Where @CustomerLastNameLowerCaseVariable = LOWER (CustomerLastName)

OR

Select Col01, Col02
From TableName
Where LOWER (CustomerLastName) = @CustomerLastNameLowerCaseVariable

On the face of it, there does not seem to be much of a difference. The execution plan says "Go for either one".

My question is more on the best practices side of things. As queries grow more complex which one of these is the better practice?

t-sqlseeder-question
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Brimstedt avatar image
Brimstedt answered

Personally I think keeping all columns on left side is better as it gives better readability. In some cases it will not be possible (when using LIKE operator for example)

This goes for both WHERE and ON clauses:

  SELECT ...
  FROM   customers cu 
  INNER JOIN orders o 
      ON  o.customer_id = cu.customer_id
      AND o.totalAmount > 0
  WHERE  cu.birthdate < '19770209'
  AND    cu.name LIKE @name

Writing this way keeps everything related to 'orders' in ON clause of orders, and everything related to 'customers' in WHERE.

People will tell you not to put things like 'o.totalAmount > 0' in ON clause but in WHERE, this is a matter of personal preference and does not affect query plan / result (as long as the joins are INNER and not OUTER, FULL, etc). For readability I think putting it in ON clause is much better.

Regarding your specific query, using functions (be them builtin or custom) in ON/WHERE gives bad performance because conversion needs to be done on every row while performing the join/scan.

I dont think using collate make a difference, but you could test:

  WHERE CustomerLastMame COLLATE Czech_CI_AS_KS = @CustomerLastNameLowerCaseVariable

Czech_CI_AS_KS -> Suitable case insensitive collation.

If performance is an issue, try one of the following:

  • Convert the column to a non case sensitive collation if possible (Do you really need it to be sensitive to case?)
  • Create a second column (test both computed and ordinary column set/updated by trigger) that contains the lower case version of the name (possibly with some other alteration needed like SOUNDEX)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jay Bonk avatar image
Jay Bonk answered

As mentioned, it shouldn't have an impact on the execution plan, so I would say it's more of a personal preference. For me, I always keep them on the right, with the column to the left.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scot Hauder avatar image
Scot Hauder answered

I agree with Jay, in addition I think a best practice is to use SQL keywords such as LOWER as they will not cause a compile-time error :)

3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 for a good spot :)
0 Likes 0 ·
+1 for the same reason as Matt
0 Likes 0 ·
I wasn't sure if variables participate in auto-parameterization or if it has to be a constant. Another non-SQL related question is for what reason would a C programmer want to put the constant first as in...if 1 == (expression) {}
0 Likes 0 ·
Peso avatar image
Peso answered

There can be a difference if the two datatypes are not the same. If that's the case, a implicit transaction must take place. But I cannot remember right now whether the left vs rigth place has presedence or the datatype has presedence.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I think you mean implicit conversion, and datatype has precedence. It will always move to the "highest" datatype. If using a varchar and an nvarchar the comparison will be done after converting to nvarchar, for instance.
1 Like 1 ·
Jeff Moden avatar image
Jeff Moden answered

Actually, both versions in the original question are a terrible practice because neither will ever be able to use an index seek because the entire CustomerLastName column must be converted to LOWER case before the join can be made. The best you can ever get out of such queries is an Index Scan. It's also one of the reasons why I deplore the use Case Sensitive Servers and Case Sensitive Columns.

Ah... in retrospect, I see that a part of Brimstedt answer also talks about this problem.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.