CODEWITHSUNDEEP
sqlt-sql
Marie
Marie

Reputation: 2217

Best practice for constant value in the WHERE clause?

I have a where clause that compares two columns to the following string. Does this concatenation run for every row? Should this string (and concatenation) be left in twice or should I create a variable to hold the result and use that in the WHERE clause?

CONCAT('%', @myVar, 'dr')

Upvotes: 0

Views: 908

Answers (2)

RBT
RBT

Reputation: 25937

I checked the Execution Plan for your expression in a test table present in my database. The table has two nvarchar(50) columns namely firstname and fullname .

enter image description here

I can clearly see that even for three AND conditions in where clause that I put deliberately, the SQL Server engine is showing 0% cost for both the compute scalar steps. It is clearly evident of the fact that irrespective of whether you create a separate variable for the concatenation expression or leave it in-line in your where clause it is not going to make any difference.

Upvotes: 1

Dheeraj Kumar
Dheeraj Kumar

Reputation: 4175

Create a variable to hold the result and use that in the WHERE clause

Upvotes: 0

Related Questions