which side does the variable go on?

Question

question

Raj More asked Apr 5, '10

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-sql seeder-question

10 |1200

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

Answer 1 · 2010-04-08T00:27:05Z

Brimstedt answered Apr 8, '10

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

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

Answer 2 · 2010-04-05T18:11:29Z

Jay Bonk answered Apr 5, '10

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

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

Answer 3 · 2010-04-05T18:13:48Z

Scot Hauder answered Apr 5, '10

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

10 |1200

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

Matt Whitfield ♦♦ commented · Apr 05, 2010 at 06:43 PM

+1 for a good spot :)

0 ·

CirqueDeSQLeil commented · Apr 05, 2010 at 06:53 PM

+1 for the same reason as Matt

0 ·

Scot Hauder commented · Apr 05, 2010 at 08:44 PM

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 ·

Answer 4 · 2010-04-06T08:53:14Z

Peso answered Apr 6, '10

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

10 |1200

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

TimothyAWiseman commented · Apr 06, 2010 at 01:17 PM

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 ·

Answer 5 · 2010-05-02T23:36:30Z

Jeff Moden answered May 2, '10

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

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

question