July 29, 2011 at 11:30 am
I guess the difference is the first one creates a temporary table, while the second one uses a sub-query to get the same data.
I think my real question is: Is there a time when one method would be better than the other? What impact does this have on performance?
thank you!
first query uses WITH to create a temporary table
with MyTable(MyName, TodayIs)
as(select suser_sname(),
getdate() )
select MyName, TodayIs
from MyTable
second query runs a sub query in the FROM statement
select MyName, TodayIs
from (select suser_sname() as MyName,
getdate() as TodayIs) MyTable
July 29, 2011 at 11:39 am
Nether creates a temp table.
The difference is that you can reuse the cte without having to retype it in the query. But that now runs 2 twice.
The real difference in that case is readability (from a personal choice point of view).
I preffer the NON CTE because I can highlight only part of the code and run that. It gets harder to do with CTE on more complexe queries.
July 29, 2011 at 11:40 am
No difference at all. Look at the execution plans, they'll be exactly the same.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 29, 2011 at 11:43 am
GilaMonster (7/29/2011)
No difference at all. Look at the execution plans, they'll be exactly the same.
I was going down that route too, untill I only saw 1 operator per plan :hehe:.
Might be worth doing on a more complex query tho.
July 29, 2011 at 11:48 am
In this case a Constant Scan, cause that's all you're doing, reading a couple of constants.
If the first was using a temp table (which it's not) there would be some form of spool or insert visible in the plan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 29, 2011 at 1:34 pm
Ninja's_RGR'us (7/29/2011)
The difference is that you can reuse the cte without having to retype it in the query. But that now runs 2 twice.
I just wanted to point out that this is no different from using either 2 derived columns or even using a temp table and then using the temp table in the final query.
The only advantage to using a temp table in that scenario is that you could reduce the impact of reading the data twice because the temp table would have only the relevant data and statistics for that data.
You can even optimize the temp table usage by including indexes where that would help, but in most cases - the final query would not use the index anyways.
I see this comment on CTE's all the time and continue to wonder why people think it is any different than using derived tables or a temp table. Again, the advantage of the temp table is that you only have to read from the more complex query once - and then use the results from that which can improve performance.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 1, 2011 at 5:22 am
Thank you all for your feedback.
This was obviously a simple example. Perhaps too simple. The next time I need to use something like this in a view or stored procedure, I will try it both ways and look at the execution plan.
August 1, 2011 at 5:30 am
There's only 1 thing to keep in mind really.
If you use a CTE and then refference the CTE twice in the query's from clause then that query will be run twice under the covers.
In that case a temp table intermediate step might be in order. Otherwise it's all the same and just a matter of readability / personal choice.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply