How To Write SQL Server Queries Correctly: Common Table Expressions

[@CollinReinking]

At the 6:30 mark i was really curious if you were going to show me a way to not have to write recursive CTE’s. I was relieved to find out I wasn’t crazy and that recursion might be the time when CTE’s are the right tool. Thoroughly enjoyed this one.

[@ErikDarlingData]

Ha ha, I wish there were an alternative! Alas, we are stuck with them here.

[@ScorpionJL]

I do happen to find CTE's more readable because I hate multiple levels of indentation. I also tell everyone to treat them as views which would also execute the SQL once for each reference. On that note, I also do only use them in specific scenarios... 1. Using a merge statement from a staging (multi-tenant)table to filter the rows needed in a destination table 2. When aggregating data and stacking the queries, such as getting dataset1, dataset2, then doing a full join to see what variances exist.

[@ErikDarlingData]

As long as their use is reasonable and informed, I don't mind.

[@Cahangir]

Subscribed. Will hopefully dive deep when the time comes.

[@ErikDarlingData]

Well thanks, and welcome aboard!

[@ScorpionJL]

Another question... at 23:50 did a Sleestak enter the room?

[@ErikDarlingData]

And I lived to tell about it!

[@caparn100]

11:20 Doesn't _row_number()_ start at 1? So saying where n.n = 0 will always be false.

[@ErikDarlingData]

Yes, that’s the entire point.

CTEs are terrible to debug, so I don't know how they are readable. Like 8+ chained CTEs inside a stored procedure where there is no reason not to use temp tables is my favourite.

[@ErikDarlingData]

Everyone looks at their own code and thinks it’s readable, even when it looks like a natural disaster.

[@chrismettler136]

yes, wish QUALIFY , MATERIALIZED as in postgresql/duckdb would be supported. Oracle is way better at optimizing/rewriting CTE. If MS thinks that theire AI is so great, why don't they use it to improve sql server ..

[@ErikDarlingData]

I don’t think they think it’s great, it’s just what everyone is getting memed into caring about at the moment. Eventually they’ll get over it and something new will take its place.

[@FlaggedStar]

Let me see if I've understood this: you're saying that CTEs aren't evil, but people wrongly think that they do good that they don't actually do?

[@ErikDarlingData]

Yes, like most other things, people believe meme nonsense they’ve heard and not what the reality is. Low information developers.

[@mattcargile]

Well recursive CTEs on parent and child relationship tables are more advanced.

[@ErikDarlingData]

More advanced than what?

[@mattcargile]

@ more advanced than a derived table I suppose. It was a comment from the beginning of the video. I see you discuss recursion at the end.

[@ErikDarlingData]

It had to happen eventually.