June 14, 2012 at 8:53 am
Hi,
I recently came across one situation where I struggled a lot to produce required data, one senior coworker advised using CTE and helped me do it. I could have done it fast had I understood the power of CTE. I read this
http://msdn.microsoft.com/en-us/library/ms190766%28v=sql.105%29.aspx
But I still have trouble identifying where to use it in future. Any scenarios/examples would be appreciated.
Thank you .
June 14, 2012 at 10:31 am
Just remember that a CTE is nothing but a derived table, a select statement acting as a table. With that in mind, if you think a select statement will allow you to define your data better than just a JOIN, you know you can use a derived table. If you need to reference it more than once within a single query, you can use a CTE. Also, because it makes for a more clean and easy to understand method, you can use it even if you're only referencing it once. One example, when I have to convert queries to XML and then reference them, I like to do that part in a CTE like this:
WITH RingBuffer
AS (SELECT CAST(record AS XML) AS xRecord,
DATEADD(ss,
(-1 * ((dosi.cpu_ticks
/ CONVERT (FLOAT, (dosi.cpu_ticks
/ dosi.ms_ticks)))
- dorb.timestamp) / 1000), GETDATE()) AS DateOccurred
FROM sys.dm_os_ring_buffers AS dorb
CROSS JOIN sys.dm_os_sys_info AS dosi
WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
)
SELECT TOP 1
CASE (xr.value('(ResourceMonitor/Notification)[1]', 'varchar(75)'))
WHEN 'RESOURCE_MEMPHYSICAL_LOW' THEN 1
ELSE 0
END AS LowMemAlert
FROM RingBuffer AS rb
CROSS APPLY rb.xRecord.nodes('Record') record (xr)
WHERE xr.value('(ResourceMonitor/Notification)[1]', 'varchar(75)') = 'RESOURCE_MEMPHYSICAL_LOW'
AND rb.DateOccurred > DATEADD(mm, -5, GETDATE())
ORDER BY rb.DateOccurred DESC;
Nothing here required a CTE, but it seperates out one set of logic from the other, making each a little easier to understand.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 14, 2012 at 6:56 pm
I can add a couple of points because I'm a great believer in using CTEs to improve code readability.
1. You can also think of a CTE as a single-use VIEW. So instead of creating a VIEW, you put that code into a CTE and this can ultimately have better performance results as SQL can now cache the query plan.
2. CTEs are useful in the query development process. For example, whenever you need to do multiple transformations to get your data from A to C, it is helpful to develop the SQL first for A to B, get it working, then move that up to a CTE. Now you work on the transformation from B to C and once working, move it up to a CTE. And so on.
Derived tables down to multiple levels can get really messy looking, really quickly, whereas the CTE approach looks much more elegant.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 15, 2012 at 3:53 am
Just don't union 2 x 1.3m row, 200 column wide CTE's in a view which is called very frequently. It bought tears to my eyes when I encountered this view on day 1...
:crazy:
June 15, 2012 at 7:12 am
Thank you sooooo much Grant, Dwain and Clive. This forum is awesome...
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply