SQL Server - Common Table Expressions

common table expressions or CT es this is one of my favourite topics in sequel server I wrote a book on it and it was published in May of 2013 and it covers probably everything you ever wanted to know plus more about common table expressions today we're gonna cover topics out of chapters one two three four six seven and nine out of the putt thing we're gonna cover is introduction to memory tables and CTS start with a simple CTE and I guarantee when we go through the simple CTE everyone's gonna look at that as wow this is kind of boring but that's okay it's just a foundation so we can build to get to the rest of it how to use a CTE instead of a drive table so when you've got those big nasty Drive table players that are four or five six pages long you can learn how to clean them up and make them cleaner code and more readable I will take a look at how to use multiple CTS in a query take a look at data paging which we've learned top in the past but will then take a look at some other ways to be able to page data without just using the top function and then how to use them CTS and stored procedures functions and views and then we'll take a look at what's my favorite part of the whole thing is how to do recursive comment able expressions so let's start out here with what is a CTE well in sequel server there's this concept of memory table so it's sort of this virtual table that gets built in memory you're running the query that result set is being stored somewhere so it can be used for instance with a sub query that I've shown here it's one way that there's this sort of in-memory table that's used the views are another way to do that where a view sort of stores this query and substitutes it in what you're running in order to be able to clean up your code a little bit in this example there's a view that just selects those columns and then we select from that view views are a really good way to clean up your code however they're hard to maintain over time when things change the database they oftentimes get overlooked they just sort of tend to rot over time so I try and stay away from views when I can unless there's a really really good reason to do it you've also got the concept of these temporary named result sets where you have a temp table or a table variable this is where it's not a table that's stored in the normal database area but it's stored in temp DB and you can create this table and shove information into it and it stores information there are temporary for you you can come back and use it later in your query another way of doing these kind of memory tables is to use common table expressions so what is a CTE or a common table expression it's not one of those things we can say well it's just like this it's just like this thing you all know your everyday life because there's nothing quite like it it's a type of a virtual table similar to the ease of a temporary table but it's sort of like a drive table like we talked about a minute ago and it's kind of like a temporary named result sets and it acts like a temporary view or a runtime view availability of cts cts they were introduced in sequel Server 2005 it's supported in all versions of sequel server since 2005 and all variations of sequel server including sequel asure now some of the really cool features that are available in sequel server are only available if you're using the Enterprise Edition are only available in a certain scenario but this is just part of the core of sequel server and it's available everywhere so why use this why use common table expressions it's another tool in your tool belt basically why would anybody want to use this when I know how to use a drive table or a sub-query well it's a great way to simplify your query like I mentioned earlier you can have a big party that might be 4 or 5 pages long and you look at and you think where the heck do I start to understand what's going on here well you can break your query up into more logical pieces using a common table expression you can use it for recursion now in computer science recursion is when a function calls itself or a function calls back to itself to do recursive type calculations in sequel server it's when a query calls itself I think well how do I do that I've never seen how a query can actually reference itself inside of it well that's what we can do with common table expressions it's a way to make drive tables queries more readable and it's an alternative to temp tables or table variables now keep in mind also it's just another tool in your tool belt so don't think that it's a reason to replace everything you ever do with Drive tables or it's a reason to replace everything you've ever done with temp tables there's great reasons to use temp tables there's great use reasons to use Drive table queries or sub queries but it's just another tool to use when it's appropriate so the way it works is if the syntax starts with the word width instead of select pretty much everything you run in sequel server that's going to return some type of data you start out with select but not in this case it can be a confusing if you're assuming that any query that it select data it would start with select the scope of the CTE is confined to a single query meaning when you build out a CTE and you run that select statement and you have that semicolon or the end of that select statement it's gone it's done at that point you can't use it again in the next select statement so the basic syntax starts out using the width statement and most of the time when you're writing T sequel the semicolon on the end of your statement is an optional thing however if you have multiple queries in a batch inside of T sequel with CT es if the previous statement is not terminated with a semicolon your CT or your wit statement will fail at this point so one habit I've got into is generally I'll put a semicolon at the beginning of the wit statement just in case it's being copied and pasted into somewhere where somebody forgot to put the semicolon at the end of the line our clean code standards generally dictate that we always want to use semicolons to in signal the intent that the query is complete however it gets missed occasionally so here its width and we give it a name and then you can define some columns here but they're completely optional there's certain cases where you have to do it but most of the time you don't need it and then you use the word as and just like in a sub query you're going to put sort of a sub query type query inside of parentheses and then we're going to select columns out of the same expression name that the CT was given up top here so here I'm going to use the same query training database I'm gonna drop it and then I'm gonna create a couple new tables the first one is called royalty and this includes British royalty it has a name an ID and a mother and a father to indicate indicate sort of a family tree type structure here and then I'm gonna create a departments table again not related to royalty but just good for the example here where we just have a department ID and a parent so it's a hierarchy as well I'm going to create a stored procedure which fills in a bunch of data into the depart table and I'm going to create another stored procedure called Fillmore departments which is going to put even more departments in in it later and then as I run through the query we're going to use the the data that gets put there a little bit later and we're going to change something we may change some of the data along the way so that's why the reload departments has been put into a stored procedure so we'll execute this we'll reload the departments it just fills in I think the first 16 departments and the royalty table has been built out completely at this point we can then select star from departments and see what we've got there IDs 1 through 1 through 17 and it shows who what the parent departments are so all the all of these here in this category are are under the Department campaign and etc there and then when I take a look at royalty and we've got George the King of England as we scroll all the way through here it's just a bunch of the British royal family and be really nice if you can see it some kind of a better structure than this it's simply although the Royal is back a few generations with who the mother and father are at each level that's the data we're gonna work with so let's start out with a simple CTE we're going to select 3 columns out of departments and again like running a sub query I'm just gonna run the inner one and test it you can see there we go we get ID Department and parent from departments we're going to wrap it with a common table expression using the width department CTE we declare the columns we're gonna get back now these this declaration just covers the names of the columns it doesn't give you any data types or anything like that we use the add statement and I'm going to select everything out of department CTE we've just done what's very similar to what could have been done with a drive table query not real exciting yet but hopefully everyone follows the syntax there so we get to the more exciting stuff earlier I mentioned you got to have a semicolon at the end of your statement and I usually put a semicolon in front of the with statement so let's look at what happens if you don't have the semicolon there because you'll probably end up doing this some time or another's here select star from departments no semicolon with with no semicolon in front of it when we try and run it this way we get this great error about if this is intended to be a common table expression you need to explicitly terminate the previous statement with a semi when you see that state that either you put a semicolon in right here or you can put one in front of your wit statement here is probably the best way to do it but with statements a good spot for safety that's the simple CTE is simply like a sub-query but you're referencing it isn't named almost like a name sub-query at that point keep in mind if the CTE is not the first statement in the batch it must be preceded with a semicolon so let's look at how we can take CTE to replace a drive table Drive tables for anyone who's used it much they just get ugly over time the drive table is a great way to simplify the query and allow for cleaner code it's a way to break it up so it makes much more sense in what you're what you're dealing with when you're looking at the query well you write that for page long query with 17 different Drive tables and six different chunks of coffee code it probably makes a lot of sense at the time you write it but if you come back a year or two later and look at it you're not gonna be able to make heads or tails out of it CTS are a great way to break that up so that makes more sense keep in mind though that using a CTE does not change the performance at all versus a drive table so if you've got a drive table sub query that's being run twice somewhere in your existing query there's so much work to make that happen twice if you're referencing that as a CTE twice it's still just the exact same amount of work so there's nothing being saved there the results aren't being saved when you use a CTD name it runs it a second time or a third time or a fourth time depending on how many times it's referenced you get more value out of it the bigger the query is that you're breaking up you eliminate accidents that are caused by duplicate code in your Drive table queries I don't know how many times I've looked at a drive table query it was really nested deep and I realize there's a chunk of code up at the top that needs to be changed but then you realize it's got to be changed at six more times all the way through you run the query it doesn't work you look back and realize oh we're supposed to be running seven times or fix seven times not six times and those are just a pain to maintain so let's take a look at how we can how we can do this to replace this example so highlighted in red or in bold if you're color blind is a drive table sub query that's replicated twice here and you can imagine that can be used for five ten times whatever in a bigger query but it's error-prone and the bigger the query gets the more error opponent gets as to go through this but we have to exactly the same queries that are highlighted in red you could put it in a view that would be one way to do it but views have other issues you've got to deal with you could have a function that returns that you couldn't go put it in a temp table but an easier way to do it is with the CT es we grab the red part here out of the original query we added the with syntax up here we gave it a name and in this case I'm going to partment CT we declared the columns here which in this case are optional but for clean code and being explicit I'm going to call them out and then Department CT got referenced twice down here and although we've got about the same amount of code on this slide as we did previously we don't have the duplicate code with the same kind of duplication problems that we would have before this is a great way to just clean up the code and if you were working with a much larger query that's on the inside of the CT or your drive table you would have saved a lot more space there so let's take a look at a demo of actually doing that so here's that drive table query that we looked at where we've got this select statement in this select statement repeated twice in the same query when we run it you're gonna see that we're getting all the parent departments and all the sub departments returned here so let's take a look at how we would break that up so what we could do is take this whole query and copy it we then take this inner part out here paste it up here we then say with Department CTE and I'm gonna skip the columns at this point simplify I can show an example as that broken out with parentheses I'm going to copy the CTE name put it in here we're where we copied from go replace the other sub query with that department CTE clean up with the formatting a little bit and depending on your standards you may do this in several different ways you know the formatting kind of the way I like it here and then we highlight the whole thing and run it and we get the very same results that we saw a moment ago but in this case we've eliminated the duplicate code so if we realize we have to go in and do some work and say well instead I wanted to return these in a different order from a different table we only have one place then to go fix that code rather than two places in that specific query well so most Drive tables can be easily converted into a CTE some of them that are really big and nasty may take a little more work to figure out where you copy and paste but it's mostly a copy and paste kind of a thing not an actual code issue you've got a deal with their copy and paste errors can be reduced with that CTE and using the CTE does not improve performance so if we jump back to the code even though we've only declared this select statement here once it's being referenced twice and each time it's referenced it gets executed of an another time so there's no performance increase over doing it that way other than it's just cleaner code and maintenance will be a lot easier with it on the multiple CTS in a query all you have to do to include multiple TTS is common separate them so here's an example we start with a semicolon with our first CTE and then inside of parenthesis we have our normal query you have a second one with a second different query here it would never make sense to put the exact same query in because you just choose the first one in that case and then we can select something from the first CTE and join to the second CTE on some column they might have in common they're so steps to add the second CTE here's an example where I throw in a simple query here that just selects John Mary and Bill and you unions them into a result set so we're in three rows returned by that I'm gonna call it first names I then add a comma I give it the name of the next CTE and the name of the columns being returned called name and here I'm going to select give Smith and Jones Union all of those and then I'm gonna do a cross join and if you're not familiar with the cross join what it does is it takes all of the data from the first table and joins it with all of the data from the second table and every possible combination that there can between all those so the result set from this would be John Smith John give Jon Jones Mary Smith give Jones Bill Smith give and Jones all the way through so you're able it's one that's pretty handy as a way to be able to build out lots of test data real quick some companies out there make apps that you that will fill up your tables with a bunch of test data I like to use this kind of an example with maybe a few more names if I want to fill out a table to see how it's gonna perform if there's thousands of rows in it and then you can just use the go statement and fill it out with blocks a lot lots and lots of data this way well take a look at multiple CTS and then we'll come back and take a look at how these can be nested so here we're looking at the sub-query version of it without CTS where we have a select statement that shows all of those with department ID of four and then here we're selecting everything independent of what the department ID is out of the department's table and when we run the non CTE version of it you can see the department ID of four is fitness and what we get back is all of the subcategories of fitness as running swimming and yoga the way we would do that as a common table expression is we break out the first one here call it department CTE top meaning the top level and then the Department CTE levels is all of them below that and then we use the exact same join statement but it's referencing one two different CTE names here and you can see when we run this we get the exact same result set zero performance change in any way whatsoever however when we look at the code it's a little bit more cleaner as to what we're doing here so let's take a look at something similar to what we saw earlier we'll take a look at using the Union statement to bring back three names John Mary and Bill and you can see we get a result set with three rows in it I'm going to build a CTE that just puts John Mary and Bill inside of it and then selects the first name F F dot name where F names is F and call it first name we're gonna run that and we get the same result not very exciting yet but here's where it starts to get more exciting with F names and that's the exact same name we looked at before for first names and L names for last names but we'll just run this one so you can see what the inner part looks like we got give Jones to Smith we then select first name and last name cross joining it and you can see here we get nine rows or three of one times three of another is nine different rows and the entire results that generated it out that way now if we want to do something similar and add a middle initial a B and C do as a another CTE we then cross join middle initials we run the query and we get 27 rows John a give and down here there'll be a John B give and a John C get for instance all the way through that way you can see if you copied out this a through C here and maybe built out through a through z you could really build out a results that real quick this way with your common table expression another way you can do this with newer versions of sequel server is to use what's called a row constructor instead of using the Union we can select from the values of all three of those as f of X we get the same thing and it's just a little bit different way to build out the same query not directly tied to C tes but it's just kind of a cool way to build out the same type of result set that we just saw there select first name last name and middle initial using a row constructor same thing I'm gonna skip over that in the keeping track of time here and jump into nest and see tes how many of you have seen these Russian nesting dolls they're known as Madras Madras keys now if you've got one of these and you look at the big doll and you try and fit it inside of the little doll that's not gonna work it just doesn't work that way physically you can't do it but you could take the tiniest doll up there and put it inside of any of the alook any of the larger dolls it would work just fine or you can put the second tiniest one and put it inside of any of the other ones because the way they all snap together nested see tes kind of work the same way in that you can only reference itself or reference ones that are declared earlier in the query so in this sense of the nesting dolls it's kind of like referencing the smaller one or the one that's earlier in the set well take a look at an example here with CTE zero I'm just gonna select one as a number not a very exciting query but then with a second CTE I'm going to reference the first CTE inside of the query and say with CTE one I'm referencing CT zero and adding one to it then I have CTE two which references CT one and adds one to it and I select star from CTE two here we have if we just select one is numb running out all we get back is just one we're gonna have this CTE that's been referenced by that one that's been referenced by this one and then we're gonna select star from it and we get the result of three back okay there's a lot of easier ways to add one plus one plus one but it kind of shows you how one CTE can be used to reference the previous one to reference the previous one but what you run into the problem is if he wanted from inside of CTE zero you cannot reference either these other two because they haven't really been instantiated or created as sequel servers going through the parsing of that statement the question in that case was conceived zero reference itself as CTE zero in this one technically it can and we'll come back to that in just a minute that's a great question I love those that lead into a further section alright so yes but it's not as obvious as just the way we've done it here so now we're going to jump into an example here of data paging data paging so this is one that a lot of the times people use the top statement if you're doing paging like think of Google search results you search on common table expressions and you get some of my website pages hopefully and then a million other results but Google only shows you the top 100 results or the top 25 depending on what your settings are one way you could write code to do that if you're writing that web page and say giving you the top 100 and then display that on the first page but then when someone clicks next to go to the second page you say give me the top 200 but let's just scan over the first 100 not the most efficient way to do it but I've seen it implemented that way in my career probably ten different times now data paging has generally when you're taking it from a database and bringing it into a webpage has always been an extreme pain for web programmers never fun until CTS and I will show you how easy this can be and not be a challenge sequel Server 2012 and newer has the offset and fetched keywords which are an easier way to do the data page in and CT es but we'll show the CTE way of doing it and then we'll show if you're using the newer version of sequel server another way that you could do it as well here's an example I think I was selecting from sis columns here we have row number 1 through 10 showing up on the screen then the second page in that dataset might be rows 11 through 20 and the third row and/or the third page might be 21 through 30 and so on now the way you could do that is we take this inner blue select statement here which is what would give me the entire set selecting from sis columns which is just you don't know what that is in sequel server it's basically a view that gives you a list of what all the columns are in your database and what table are associated with so I can put that whole thing inside of a CTE call it tables and columns select from that and say here I'm generating the row number because of the way that functions work in sequel server the row number can't be referenced in the where clause in the blue code but in the outside just similar to what you can do with the drive table though you can put it between and here's some map that if we had variables for page number and page size it would automatically count calculate the row number to give us the right page data the way you can do this with sequel Server 2012 and beyond is a little bit simpler but it's not available in 2008 or r2 or 2005 here you get you can use the offset and fetch next where here you're saying offset a certain range see it's the offset five rows fetch the next five rows only and it's just an alternate way of doing it we're going to be using that systems and I'll show you what it looks like show you what it looks like to look at the whole thing it's just pages and pages and pages of every column available in the sequel server at this point then I'm going to wrap that oh I also included the row number ordered by the object ID I'm going to wrap that in a CTE called tables and columns then I'm going to select from tables and columns where the row number is between and doing the math to get the right rows based off of page number of two and a page size of 10 so if we highlight this whole thing and run it you can see we get rows 11 through 20 at this point and only arose 11 through 20 and that's far more efficient than saying give me the top 20 and then just skip over the first 10 now the other thing we could do looking at this is we could put that into a store and procedure so I'm going to create a procedure called tables and columns pager it's gonna take two parameters one for page number and one for page size to run no count so we don't get row numbers being spit out or a number of rows affected being spit out we then put the same query in there and then when we execute it let me first just create it here and then it run time to execute it we just say give me tables and column pagers for page one two and three with a page size of ten and if we scroll this up we can see the first one covers rows one through ten second one returns rows eleven through twenty and the next one returns rows plenty one through thirty I don't know in my opinion much cleaner way than almost any other way I've seen of doing this I'd be able to do paging of data we can also look at how it compares to the sequel server 2012 way of doing it and newer so this is 2012 in 2014 where it's using the offset and the fetch next and what we're gonna do is we'll highlight the whole thing here this was set up for performance for wrapping order the performance the performance is very similar with these the performance really comes down to it depends on your data set your database how things are configured in some cases the offset and fetch make way maybe a faster way to get at your data by a little bit in other cases the CTE version might be just a little bit faster so just think of it as an option there on newer versions of sequel servers two different ways to try something for the patient work here purple so we run this you can see we get two result sets back they have the same stuff and I'm one of them using the CTE version and one of them using the offset to fetch now if you're working with sequel server 2012 and newer and you know your coats only ever gonna run on 2012 and newer I would say in this case go with the offset in batch but if you have to wrote write code it's portable to other versions of sequel server prior to 2012 the CTE version is much more reliable in that case next on to CTS and stored procedures functions and views now we saw an example that are putting it in a stored procedure I'm gonna create a procedure called show departments and in here I'm just gonna put a simple CTE inside of it and then I'm going to execute it so that's pretty similar to what we saw a moment ago when we looked at the previous stored procedure for the paging but you can see just calling the stored procedure returns three set that we can see here see tes work great inside his stored procedures just like a select statement there's no reason you shouldn't use it inside of a sort of procedure functions very similar here I'm creating a table value function and then I'm going to select from that table valued function and you can see we get a similar result set back so if I put it inside of a function and selecting from that function similar results and again see T's work great inside of functions and how about if we put multiple CTE is inside of it inside of a function here I'm going to say here's a department CTE we're going to insert into a table variable called results it's being returned and then we're gonna do a second CTE that inserts more into that table variable called results and the results of both of these will effectively be almost like your Union ating them or Union all into the same table so let's just created well with God in the result set is all of those where the parent ID is one which is these here and then all of the items where the parent ID is three which are these here very similar to what you could do with Union all or Union just a different way to do it alright so here's one where we've got a non table valued function or a scalar function it's only going to return an integer it can't return a table or result set we're gonna declare the results as an integer we're going to declare our CTE we're going to select count from that CTE into that variable and then return the variable that works that works great also but just keep in mind that if you're doing a scalar function that returns a single value you can't return the whole result set there has to be a single item that's being returned and then in views you can also put C tes inside of views just like you would do with the Select statement we'll create the view and the earlier question was how long are these things kept around well that view not I've created it has kept around until it actually gets dropped or deleted to the database select from that view there you go some more result set going back stored procedures functions both scalar and table valued function and view CTS work great inside of all those now on to the really fun stuff recursive CTS and now the reason I say this is really fun is the stuff you can do with recursive situ ease you can't do very easily any other way with sequel server yeah you could do a function that ends up somehow calling itself and it's really messy and really ugly but CTS give you this sort of elegant way of doing recursion it's considered recursive when back to the question we had earlier to see the reference itself it's considered recursive on the CTE references itself recursion stops when the query produces no more results so there's just nothing returned at that level of recursion or when max recursion is hit max recursion is a parameter that set up in sequel server the default is 100 you can extend it out to as deep as 32,768 I wouldn't recommend doing that unless you've got a really good reason for doing that great way to use this is for a hierarchical listing of categories recursive calculus calculations and much much more recursion what is recursion well if you were to sum the numbers from 1 to 10 without recursion it would effectively be saying 10 plus 9 plus 8 plus 7 6 5 4 3 2 1 all the way out equals 55 in this case that's sort of the non recursive way of doing it but with recursion it's kind of like saying give me the sum of the numbers that just take 10 and add it to the sum of the numbers 1 through 9 and then when the sum of numbers 1 through 9 is processed and then continuing out all the way as though you're doing each of these is a different function call to be able to work through that code you can do the same thing with common table expressions in ZTS where it all starts with recursion is what's called the anchor query this is where recursion starts and you can have one or more anchor queries and I'll show examples of that too they might think well why would I have two different anchor queries on recursion well we just like in code if you had two different recursive functions that mean we're being called in different areas the anchor query is two different ways of starting that recursion and then you have a recursive part of the query and this is the part that gets repeated for each level as its walking through a hierarchy or walking through the levels of data here and you can have one or more of these as well and then max recursion like I said a minute ago fault is 100 and this is really handy to keep your query from going out of control I've done some recursive queries that were designed to see how mean how evil they can be the sequel server and I've effectively been able to chew up all of the available memory on a sign test machine with only 16 gigs of RAM but I was able to use it all up in a single query by getting a bit wild with recursion max recursion you can set it to zero that implies there is no maximum so remember now if you're using numbers the maximum you can do is three to seven six eight for the your depth but zero will let you go beyond that meaning just keep going until your query finishes or until sequel server runs out of memory Danger Danger Danger start out you declare the CT in columns very similar to what we've done before but in this case we're gonna be I'm adding a different one in here called level I should have clean code made that spell that out effectively is level I think level is a keyword than sequel server so the syntax highlighted showed up weird which is why I've created it then I'm going to start out with a real basic query I'm just gonna say give me department ID parent and zero as level from the department's table where the parent is not okay so give me all the top-level departments at this point all right we're going to use the Union all in here to connect that anchor query which is in blue to the recursive query recursive query is highlighted in red where it's pretty similar we're going to say select this that and the other thing from departments but we're gonna inner join the department CT which is declared up here so inner join back to itself on the department ID equals D which is departments dot parent so it's going to take your top level and then find all the next level in the hierarchy and then this level variable which is how deep into the recursion of a be gone I'm just gonna take the previous one and add one to it and then I'm just gonna say select star from that entire query so here we're gonna look at departments again there we go we've got the same department table we've been looking at all day long here with the same type of stuff with the total of 17 rows and a number of things scattered out amongst the four top level departments which have no parent then we're gonna just say give me the top four I'm sorry give me the top level ones that don't have a parent there's the four of them so those are your main categories as you look at the store now how would we do this without a CTE well we could use a self join where we select departments and join it back to itself and show top level and child level ok so there's all the top levels there's all the child levels yeah no see no CTE use there all right we don't have a level so if we want to use a level that shows us how deep in the hierarchy we are we were we can use this case statement in here that says if the parents null then we're at level zero otherwise we're at level one so we run that and we get the level listed over here great if we got two levels but if you want to go three levels or four levels or five levels prior to using CTE s I saw some really really darn ugly queries where people did this out for four or five six seven eight levels and you just end up with horrible code to maintain but here's how we would do that with a CTE in a much cleaner way here I'm gonna select the top level and just say we're at level one actually let me correct something here that's actually level zero so the top level is level zero just to match the samples we had and then I'm going to throw that inside of a CD not very exciting because we've just got the top four top level here's top level categories here but that's the foundation for where we're going next next this piece here is the recursive CTE part of it and because it references the CTE we can't just highlight this and run it outside of the side of the CTE so we copy that chunk using Union all we paste it in here and we say select this query and then for every row returned from this inner join it into this result set we run that and we can see we get back to similar to what we saw a moment ago where we can see the top level and then all the second level stuff great not still not that exciting you could have done all that with a self join but what happens when you've got more departments so I've created the stored proc call the Fillmore departments it's gonna put in I think third fourth and fifth level into the hierarchy run that and we go back and look at the non CTE version of it right here that shows the levels it shows there's our top level and then everything else is at level one and there are a couple other top level once at that function inserted as well but that's not entirely true because if you look down here backpacking it has a parent of 19 which is two-person which is a parent of five which is tense so you're under this camping two-person or camping tents two person backpacking so you have this four levels of hierarchy but this query is lying to us because it says they're all at level one so we'll go down and try our CTE version of it again and see what we get and you can see at this point we have level zero all the top ones all the stuff at level one but now we can see what's at level two three and four and Beyond by default because we haven't specified anything at recursion this would handle a hundred levels of departments now in fact if you're gonna go open your camping and backpacking store 100 levels of depth is probably enough for whatever you might be selling but if it's not you can override that with the max recursion let's take a look at the whole sum of parts so sum of parts of one is sum all the numbers from one to one watch this one two is one plus two equals three three is one plus two plus three equals six and you guys get it real quick whole way out to level ten well what I'm going to do is I'm going to create a function and in that function here's how we can do it without a CTE I'm going to declare a results variable if the number is greater than zero meaning as we're going to start a big number and as we iterate through it stop at zero because we've gone all the way from ten nine eight seven six all the way down to zero and then I'm gonna if it's greater than zero I'm gonna call call the function itself so here's a recursive function nan CTE when we run that we can then run some apart to pen or sum of parts of three the other 55 or our six so yeah the maths looks like it's working correctly there but what if we want to do sum of parts of a hundred well here a maximum recursion you can do in procedures functions or triggers is 32 and most the time 32 is probably gonna be okay but if you want to go beyond that here's how we can do it so we build our anchor query to do our recursive CTE we select one as our account number and one is our grand total so that's our sort of priming the entire thing the sum of parts of one is one there's there's our answer for some of the parts of one but now we need to build it out further so we throw it inside of a CTE you guys can probably imagine what that's going to return here's our recursive query which I forgot to include the from on that part but here we can have our original anchor query Union in it with our recursive query and the recursive query effectively joins back to everything that's ever returned from the sum of the parts each level and adds one to it as we go through here and then I'm going to select everything out of that so when we run that query oh but what happens when we go too deep on recursion crash the maximum recursion of 100 has been exhausted before statement completion and technically the way that this recursive query was built it's dangerous because there's nothing that ever terminates it and no matter what level we set it to it would just keep going until we ran out of memory or decided to go home for the day I'd still be running when we came in the next day but what I'm going to do now is add in this where clause here that says where the count number is less than 10 so don't go any deeper than 10 in this case when we run that you can now see we've got a table returned that shows some aparts 4 7 is 28 so in parts for 10 is 55 and you get all the results that way now if we just wanted to look at the max for sum of parts to get similar to our function that we did earlier but we just want to know in 410 we can run it this way and we can now see the sum of parts of 10 is 55 take a look well what happens if we want to go further sum of parts of 150 anybody know that off the top of their head well it crashes the way we get around that crash is we put in this option of max recursion of 200 now you could say option max recursion of 0 which would mean go as deep as you want forever which would be pretty darn bad if your if you messed up up here but with this where clause you would be okay putting a 0 down I wouldn't recommend doing it because you could comment that out when you're testing or something that cause problems so I just threw in a number that was bigger than 150 and gave me plenty of room I could have used 151 there if I wanted and we run that we can see the sum of parts 11 325 what if we want to go to 4,000 or 40,000 what's the sum of parts of 40,000 crash wampum it doesn't matter because alright 800 million something so we crash here because the max recursion is gone between beyond 3 to 767 but you might think okay am I stuck can I just not do beyond 3 to 7 6 7 no way down here let's get a little bit dangerous take a walk on the wild side and we're gonna go with max recursion of 0 but I know that this count number up here is gonna terminate it on time we run that 8 million 800 million something rather there are times where it is appropriate to use a max recursion of 0 but you better be very certain that what you've done up here is gonna terminate it appropriately so we have this royal family tree that we built out and I had another 40 minutes I'd go through all of this but it starts out here we're going to select Prince William is our anchor query we're then going to select from Prince William his father his father's father and father's all the way up on his father's side of the family and then for this other recursive query it's going to select the mother and the mothers of everyone in the tree and let me just show you what happens when we run this here we're gonna replicate out with a dot in a few spaces to indent based off of our level and when we run the whole query jumping back to recursion here you can see there's a little bit more effective way so we can see Prince William is at the top here his father is there his mother is here his mother they weren't officially royalty I guess so they're not quite as much as far back there's much more on this side of the family so recursive CT ease there's a bunch of bonus material I preclude included at the end here out of the book Fibonacci sequence was with recursive CTE factorials some really cool string parsing stuff more on the royal tree stuff like that so on to the quiz part of the class we got a few minutes left here speak up as we go through these so a named result said the CTE is run only once even if it's referenced multiple times in the query false correct it is false it's run once for each time that it's referenced in the query KCTS are proprietary to microsoft sequel server I didn't mention this but anybody know false they're supported on several major platforms among them post grad post Greece sequel db2 Oracle and sequel server and there is part of the 99th sequel 99 spec however when you're working on these other platforms they're a little bit different so the syntax is slightly different and in fact in some cases they're even quite a bit different but you get the same concept out of them see T's this is an easy one they're a great way to create recursive hierarchical queries anyone true yes I love it with hierarchical queries this way I used to hate having to deal with hierarchies before this all write their own CTS are only supported on sequel server Enterprise 2008 r2 and newer to her false bolts they're only available they are available on 2005 and newer and on all versions including sequel sure CTS can be nested and 1ct can reference an earlier CTE true or false ooh awesome you guys got it indexing CTS if you want to make your CTE run faster to lose performance you can index it well false false is correct it's an inline kind of temporary thing that doesn't exist you can't index it if there's nothing kept around which performs better a non recursive CTE or a view exactly they are the same the big game is when the recursion with the recursive CTE which you can't really get with a view data paging CT is a great way to do data paging for result bread yeah it depends so it's equal server 2014 and 2012 have the new offset and fetch which it's easier but for older versions CTS are absolutely the best option CTS are similar to temp tables or temp variables in their use of temp TB I know I didn't cover this much but anyone know on pale pulse why is it false because they're more like code expressions than they are like table groups or something that's that's correct it's more like how a derived table is stored in the workspace inside a sequel server rather than in memory rather than going off to temp TV but temp table variables are stored in temp TV table variables and temp tables are both stored in table in temp t be all right for more information you can visit emergency recording com to find out if more about us and why we're doing this and you can visit Aaron on the web at Aaron boomer comm on Twitter as Aaron Z Booma or you can visit me on the web at Steve Steadman comm on Twitter as sequel EMT

Channel: Steve Stedman

Views: 22,600

Rating: 4.9230771 out of 5

Keywords: CTE, Common Table Expression, SQL, SQL SERVER, TSQL, t-SQL, recursion, derived table, 70-461

Id: 0nGiCZ_3CIE

Length: 45min 33sec (2733 seconds)

Published: Thu Feb 19 2015