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 …
If you have multiple CTE’s in a query then you can do what’s called a nested CTE. This is what Steve will go over in this video. Transcription of Video: So next, if you’ve got multiple CTE’s, we can do what are considered nested CTE’s, where the previous multiples we looked at, it was just two CTE’s that were being …
Added to SQL Server in 2008 Steve explains and demonstrates how to set up row constructor. Transcription of video: So there’s this thing called a row constructor. And this was added in SQL 2008. But it’s a way of saying select x from, and you give it a list of values around f of x or a function like this, …
Row Constructor in SQL Server Demo and Explanation Read more »
Improve you query skills with Common Table Expressions. The Common Table Expression (CTE) is one of the more powerful and often overlooked features in Microsoft SQL Server. This book will show some of the more interesting things you can do with a CTE. You will learn how CTEs are a great alternative to derived table queries, how to do data …
There are 2 perspectives here to the DBA on vacation, typically in an organization with only a single DBA so there is no cross coverage when someone goes on vacation. The view as the DBA: I have done everything that I can to prepare the systems for vacation, but still I end up getting called for some regular maintenance job …
Let’s start slow with a simple CTE query demo for easy absorption. Transcription of video: So let’s take a look at how to write a simple CTE query. So what I have is a test database here that I’ve set up called CTE demo. And in that I’ve got a couple tables one’s called royalty that has a bunch of …
Today Microsoft released a new update for SQL Server. SQL Server 2014 CU 4 + GDR (KB 5014164) 6/14/2022 Here are the latest updates for each version of SQL Server. SQL Server 2019 RTM CU 16 + GDRDownload links: CU 16 + GDR (KB 5014353) SQL Server 2017 RTM CU 29 + GDRDownload links: CU 29 + GDR (KB …