Alvin Santos Alvin Santos - 1 year ago 68
SQL Question

SQL Query - Are they the same?

I have two Queries here that will generate the same result.
Which do you think is better or maybe they are totally the same.

Query 1

ISNULL(col_1, col_2) AS col_1
FROM table_1
INNER JOIN table_2 ON table_2.col = ISNULL(col_1, col_2)

Or Query 2

WITH cte_table_1 AS
ISNULL(col_1, col_2) AS col_1
FROM table_1

FROM cte_table_1
INNER JOIN table_2 ON table_2.col = col_1

Answer Source

It is not easy to predict this.

In general: SQL-Server is not a procedural engine working down statements, one after the other, as you've set this in your code. You tell SQL-Server what you want, and the engine will decide, what is the best way to achieve this. And this is done pretty well! This may vary from one call to the next depending on statistics, parallel actions, usage of memory, CPU or HD.

Even if it looks like this, the CTE is not handled like a table. Queries with multiple CTEs look like Oh, pick this result, than fetch these, now let's call these rows and now go into the main query! But the engine might decide for a completely different order of execution.

In your simple case I'd assume, that the engine is smart enough to see, that your queries are the same structurally.

Check the execution plans to see further details.


You say: if they have the same EP result then we can say that its up to the user which is better to them as what is preferable to them?

Why should your users think about the internal structure of a query at all? It depends on so many factors you cannot control! You cannot even be sure, that the next call will be the same as the one before...

My strong advise is: Do not even try to out-smart the engine! Thousands of hours were spent into its algorithms... You should rather think about things like readability and maintenance...