1

I was wondering how a query like

SELECT * FROM A WHERE A.created_by = 1 AND (A.name LIKE %app% OR A.description LIKE %app%)

is being processed. I'm not if this is a valid query or not, but I think the idea is clear enough.

From my perspective, I'm not sure if the SQL Server processes every row through that condition and checks if it passes it and then returns all the rows that passed the condition, or if the SQL Server gathers a data set for each condition and then intersects/combines them to get the final data set (like created_by gathers a data set that is later intersected with the combined data sets gathered by the other two conditions), or it is done in other more abstract (at least for me) method.

I hope my question is specific enough. If I wasn't clear enough, please ask for further explanation that I will provide.

Andu
  • 65
  • 5
  • 2
    http://sqlmag.com/t-sql/inside-sql-server-parse-compile-and-optimize and http://rusanu.com/2013/08/01/understanding-how-sql-server-executes-a-query/ – Tim Schmelter Sep 22 '17 at 15:34
  • It really depends... The query optimizer can do things a bunch of different ways. You can view the Query Execution Plan by highlighting the query you want to run and pressing `CTRL + L` inside the SQL Server Management Studio. – EMUEVIL Sep 22 '17 at 15:37
  • I can say I wasn't expecting a simple answer, but that article (that I find pretty useful but a little too advanced for me maybe) makes me understand that both of my guesses could be used in processing the query, and maybe more other variants would be selected for query processing? Am I right? – Andu Sep 22 '17 at 15:41
  • If the article was to advanced then just thing about setting the proper indexes first that will work for you in the most cases. I can say that question is very advanced in SQL Server as it does so many different things underneath – Dmitrij Kultasev Sep 22 '17 at 15:42
  • 2
    Assuming there is a usable index, SQL Server will start by doing a SEEK operation on A.created_by. From those results, because leading wildcards aren't SARGable, it will do range scans on A.name & A.description to filter out rows where neither are like '%app%'. – Jason A. Long Sep 22 '17 at 15:43

2 Answers2

0

Evaluation order is not guaranteed in SQL and it will depend on the implementation by the RDBMS.

6.3.3.3 Rule evaluation order

[...]

Where the precedence is not determined by the Formats or by parentheses, effective evaluation of expressions is generally performed from left to right. However, it is implementation-dependent whether expressions are actually evaluated left to right, particularly when operands or operators might cause conditions to be raised or if the results of the expressions can be determined without completely evaluating all parts of the expression.

From here

Typically, a RDBMS will try to optimise a query as much as possible and to produce the best results it might end up reordering your query. Indexes can play a huge role. If you are using an indexed field in your WHERE clause this might be, irrespective of where it is written in your WHERE clause, one of the constraints first evaluated because of the advantage in speed gained from evaluating an indexed column.

If you are more curious on how exactly that query is processed, you could check if your RDBMS offers an option to show the query execution plan. There you can typically see in what order the clauses were evaluated.

Haris
  • 778
  • 2
  • 9
  • 20
0

SQL server can and will use different strategies even for the same SQL query.

SQL server looks into what kinds of conditions/joins/selected fields are used, and looks into involved tables, indexes, etc. to decide what to do.

For example. If there is clustered index that have all the fields used in query in right order, then SQL Server wont touch original table, but will just read data from index.

To know which case it is for Your query, you can only ask for real execution plan. You can find in in Management Studio. It's a button that when activated will cause SQL server to not only return any results of a query but also execution plan containing all the algorithms used by SQL server while executing that query.

PS In example from question SQL Server have to go throuh all rows in a table as string search with wild card at the beginning '%... can not be optimized.

przemo_li
  • 3,932
  • 4
  • 35
  • 60