Hey there, SQL developers and DBA’s I’ve always been captivated by Common Table Expressions (CTEs) in SQL Server. There’s something about streamlining complex queries and making them more readable that I find incredibly satisfying. Over the past 9 years, I’ve explored deep into CTEs—presenting at conferences, writing a book, and even hosting a few online sessions. And now, I’m thrilled …
Mastering Common Table Expressions (CTEs) in SQL Server Common Table Expressions (CTEs) are a powerful feature in SQL Server that can make your queries more readable, more expressive, and often more efficient. Today, we’ll demystify this tool and invite you to delve deeper through my specialized class on CTEs. What is a Common Table Expression? A Common Table Expression, or …
Mastering Common Table Expressions (CTEs) in SQL Server Read more »
Hey there, SQL developers and DBA’s I’ve always been captivated by Common Table Expressions (CTEs) in SQL Server. There’s something about streamlining complex queries and making them more readable that I find incredibly satisfying. Over the past 9 years, I’ve explored deep into CTEs—presenting at conferences, writing a book, and even hosting a few online sessions. And now, I’m thrilled …
Transcription of Video: Let’s take a look at another recursive CTE. That’s not looking at tables, but it’s just looking at math. And here, we could create a recursive function that’s called sum of parts, and select from the sum of parts. Basically, we’re creating a function that selects from itself. And what it does is it says give me …
Transcription of Video: One of the things I like play with is performance and pushing things to their limits. So what I want to do next is see how far can we push this sum of parts? Can we do the sum of parts for 40,000? With the option of Max recursion equaling 40,000? No, we cannot do that because …
Transcription of Video: So here’s an example of building a recursive common table expression, I’m gonna start out with the with statement, we’re gonna call it department CTE. And we’re going to label the columns that we’re going to return department ID, the department, the parent, and those are all things we’ve seen before earlier in the presentation. And then …
Transcription of Video: Every recursive CTE needs an anchor query. And what an anchor query is, is it defines the start of the recursion. This is the part that cannot reference the CTE itself, it has to start with some base starting point. So if you’re doing a hierarchy of departments, well, you’re starting the recursion may be giving me …
Transcription of Video: Now on to CTE’s in stored procedures, functions and views. When I first started doing this presentation several years ago, people would ask, “oh, can the CTE be used in a function or can be used in a view”? And the answer to that is yes. And as we saw above, we actually already put the CTE …
Transcription of Video: Here’s an example of a CTE that’s been created for data paging, where the select statement inside of the CTE that shown in blue is just grabbing some table names and column names from the SIS dot columns table. And there’s a lot of them in there. And then what it’s doing is it’s calculating the row …
Video transcription: Basically, what data Paging is, is when you’ve got a large large result set and what you want to do is display like the top 20, or top 50 results. So if you go to any search engine and do a searching on some topic, you you’ll see that you’ll get like the top 20 results out of …