70-461 question on CTE vs Window functions
jermovick
Registered Users Posts: 1 ■□□□□□□□□□
First of all- I am not the quickest learner. I have been working on the 70-461 for months now, and managed to make it to chapter 6. Working through the examples, I am having no end of trouble with the CTEs.
I do not understand their purpose. If the Window Functions are more efficient as far as Server side performance is concerned, should I bother to learn the CTEs? When I say CTEs, I mean the things that begin: WITH <name the thing> SELECT (columns from some table) AS, then create an inner query on it.
Is it just me or is this syntax compared to the Window functions of OVER(PARTITION BY ... just horrendous?
Any insight as to the reason for these horrible CTEs would be most appreciated as well as how important they are on the 70-461 exam.
I do not understand their purpose. If the Window Functions are more efficient as far as Server side performance is concerned, should I bother to learn the CTEs? When I say CTEs, I mean the things that begin: WITH <name the thing> SELECT (columns from some table) AS, then create an inner query on it.
Is it just me or is this syntax compared to the Window functions of OVER(PARTITION BY ... just horrendous?
Any insight as to the reason for these horrible CTEs would be most appreciated as well as how important they are on the 70-461 exam.
Comments
-
NotHackingYou
Member Posts: 1,460 ■■■■■■■■□□
They serve different purposes. Window functions are used to aggregate the results of a query in a repeatable way. A CTE is used to temporarily store the results of a query so you can work with it however you like.
Think of a CTE like a tupperware bowl - you're saving it for later, but you can only re-heat it once. Think of a window function like a ginsu knife - you want to chop up the same thing into pieces so you can observe them.When you go the extra mile, there's no traffic. -
NotHackingYou
Member Posts: 1,460 ■■■■■■■■□□
I wanted to add that I really struggled with the Window and Framing idea as it was described in the 70-461 book. I found this blog post very helpful in explaining both why windowing and framing is useful and the difference between the two:
https://www.simple-talk.com/sql/learn-sql-server/window-functions-in-sql-server-part-2-the-frame/
Hope this helps.When you go the extra mile, there's no traffic. -
N2IT
Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
Both are beautiful once you learn the concepts! They have saved my tail several times in reports. Temp tables don't play nicely in Excel, however CTE's sure do!
Carl my job would be impossible without Windowing. I am consistently using it to order data that sometimes doesn't have an order. Cross joins help a lot as well.
I find time sequencing and getting max and one less than max is a request I get in my environment. With CTE's I can build reports basically all in SQL and the Windowing feature is great when trying to aggregate data in data warehouse tables. (Denormalized Tables)