4

Suppose I have a T-SQL command with multiple WHERE conditions like this:

SELECT * 
FROM TableName
WHERE Column1 NOT LIKE '%exclude%'
  AND Column2 > 10

Would the query exclude a row as soon as Column1 was not met or would it still go on to test the next condition for Column2?

I am asking because I want to see if it would be more efficient to swap my conditions around to first test if Column2 > 10 before I run a more time-consuming condition.

Edit: If it matters, Column1 is of type bigint and Column2 is of type ntext

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Bijan
  • 7,737
  • 18
  • 89
  • 149
  • I would also add any indexes that may be on the table into the question – Scott Selby Jun 18 '15 at 22:26
  • 2
    SQL is a declarative language, and not a procedural one. The query optimizer doesn't consider the order of the where clauses. Your answer might be here http://stackoverflow.com/questions/4035760/does-the-order-of-fields-in-a-where-clause-affect-performance-in-mysql – Ramie Jun 18 '15 at 22:27
  • 1
    `ntext`, `text`, and `image` data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use `nvarchar(max)`, `varchar(max)`, and `varbinary(max)` instead. [See details here](http://msdn.microsoft.com/en-us/library/ms187993.aspx) – marc_s Jun 19 '15 at 04:59

2 Answers2

2

Sql will devise a query plan based on available indexes and statistics. Sql doesn't necessarily have "short-circuit" expression evaluation per se because it is a procedural language but ultimately the query plan will perform short-circuit evaluation.

Swapping the expressions should not affect performance.

Marc Johnston
  • 1,276
  • 1
  • 7
  • 16
0

As Marc said, swapping columns in where clause will not make any change in performance. Instead, you could look for changing the data type NTEXT into nvarchar(X) where x represents some meaningful data length.

Ananda Kumar Jayaraman
  • 380
  • 1
  • 6
  • Unfortunately for my specific use, this will not work because I only have read-only access to the DB – Bijan Jun 19 '15 at 18:22