Derived tables and common table expressions in sql server Part 48

hello welcome to premium technologies I am venket this is part 48 of sequel server in this session we'll learn about derived tables in common table expressions we'll compare these with other constructs that are available in sequel server like views table variables local and global temporary tables let's start with an example I have TBL department table here which is called department ID and Department name columns TBL employee table here which has got ID name gender and department ID columns now let's say I want you to join these two tables and produce the output that you can see on the right-hand side here I want the department name and the total number of employees within that department but then your list should only include those departments which has got two or more employees and obviously to achieve this output we have several ways but then we will see how to do that using views okay so we are creating a view here create view view name as we are selecting the department name department ID and count of star which would count the number of employees within that department and we are giving it an aleeah's called total employees from TBL employee join with TBL department and obviously between these two tables department ID is the column that's common so we are joining those two tables on that column and you will have to group by department name department ID because we want the count and we have spoken about joins and grew by in a very great detail in the previous sessions of this video series so please check that part and we have spoken about views as well ok so we are creating this view so once we have this view it's very easy to compute this result that you want so the columns that you want or the department name and total employees so obviously we have those columns in the view department name and total employees all you have to do is select those columns from that view and then select only those departments which has got total employees count greater than or equal to 2 which will give us this output ok but then let's say if we are using this view only within this query then it doesn't really make sense to create a view for that purpose that's why we have other constructs in sequel server which we'll be exploring next so views get saved in the database and can be available to other queries and stored procedures as well so if you are you know using this view in other stored procedures and and you know in other queries then that's fine but then if you are using this as a one-time you know just this one time in this query then then it doesn't really make sense to have this view created we can make use of other constructs that are available in sequel server like CDE derived tables temp tables table variables etc which we'll be talking now all right let's look at this in action so we have these two tables TBL employee TBL department you know the data okay so let's create this view execute command completed successfully so when we refresh this folder we should see the view there so views get physically stored in the database now if you want the columns from that view look at this we have all the columns from the view select star from if you say view it would return all the columns within the view we have got three columns Department name Department ID and total employees but then in our output we are only expecting department name and total employees so we are only selecting the required columns department name total employees from that view where total employees is greater than or equal to two which would return IT and HR departments so let's execute that query and we only get that all right but then if you are using this view just in this query then it doesn't really make sense to create a view all right let's see how to achieve the same thing using temporary tables so obviously the logic to calculate the department name and total employees it's exactly the same you know we have this query this query will not change it's just that we are going to use that with other with the constructs that are available in sequel server so here we are using temporary tables okay so the query is the same select department name department ID count of star as total employees we are giving it an aleeah's and we are selecting these columns into hash temp employee count this is the temporary table so you might be wondering where did you define the columns that this temporary table has you know when you use select into syntax what's going to happen behind the scenes sequel server will create those columns for us automatically you don't have to define the structure of the temporary table so we are selecting these columns into this temporary table you know the rest of the logic is the same we are joining TBL employee with TBL department on department ID column and grouping them by department name and department ID columns okay so this logic remains them it's just that you are selecting that data into this temporary table and then what you are doing it's pretty much same after that you want the department name TBL total employees instead of the view you are saying from the temporary table where total employees count is greater than or equal to two okay let's look at the set in action and remember since this is a temporary table it's a good practice to actually drop the table after you have finished using it and remember there are two types of temporary tables local temporary tables and global temporary tables depending on the type of temporary table you are creating the scope differs okay and temporary tables are stored in temp DB local temporary tables are visible only in the current session and can be shared between nested stored procedures meaning if you have created a stored procedure I mean a temporary table in stored procedure a and if stored procedure a calls stored procedure B then the temporary table that you have created in stored procedure a will be available in stored procedure B so local temporary tables are visible in the current session only in the transition and can be shared between nested stored procedure calls whereas global temporary tables are visible to all the sessions and a destroyed the last connection referencing the table is closed that global temporary table is basically closed let's look at this in action so we are selecting these columns Department name Department ID count of star into this temporary table so let's execute that so we should have the temporary table created now so obviously when we say select star from the temporary table you should see all the rows and columns so select star from the temporary table which here is temp employee count since we have a single pound sign it's a local temporary tables we have spoken about temporary tables in a great detail in the previous sessions of this video series so if you're new check those paths first so we have these columns so obviously if you want only those you know Department name and total employees columns you select those two columns from that temporary table where total employees is greater than or equal to 2 so when we execute this query we get the expected output so the output is exactly similar except that here we are making use of temporary tables instead of a view all right now let's look at how to do exactly the same thing using a table variable now when we created the temporary table we didn't define the structure the structure is automatically inferred based on the Select statement that you are using here because you're using select into but then when you are using table variable here we are defining the structure explicitly so declare a table variable I mean a variable and this variable is of type table obviously if it's a table a table will have columns and data types so you'll have to define them as well so this table is going to have department name department ID and total employees just like you know how we have this view department name department ID and total employees columns ok so next what we are doing we are inserting into that table variable department name depart this logic remains the same the query is exactly the same we are selecting department name department ID count of star from TBL employee joining that with TBL department on department ID columns and then finally grouping it by department name and department ID so now we have this table variable from the table variable you can select the required columns Department name and total employees where total employees is greater than or equal to 2 so it's exactly similar in the previous example we were using temporary table in this example we are using a table variable ok just like temporary tables a table variable is also created in the temp dB you know most of the people think table variable is actually created in memory which is not true both temporary tables and table variables are created in the temp TB the scope of a table variable is the batch or the stored procedure or a statement block in which it is declared but the advantage of using table variables is that you can pass them as parameters between procedure calls between one procedure to another procedure ok so let's look at this in action obviously it's a very good practice to drop temporary table so let's drop that table I mean even if we don't drop a local temporary table it gets automatically dropped in the session that has created it is closed so using table variable we are creating the table structure here declare the table variable specify the column names and the datatypes and then insert into that table variable and then select whatever columns you want from that table variable so let's execute this we should see the same output except that we are using the table variable and the advantage of using table variable is that you don't have to drop at like temporary tables but there are several other differences as well between table variables and temporary tables all right so we can also achieve the same thing using derived tables now look at this when we created a table variable we will have to define the columns and the data types for a table variable but whereas if you are using a derived table this table is derived so you don't really define a table as such you just give it an Aaliyah's so if you look at this this is the query that we have been looking at all this while select department name Department ID count of star s total employees from TBL employee joining that with TBL department the same query here now if you look at this this query is wrapped between these brackets and then you're seeing as employee count so this entire thing from this bracket to this bracket you know is considered as a table okay it derived table and you are giving that table a named employee count and then if you look at this the columns that are present in this table are nothing but the columns in the Select list here so you have department name department ID and total employees columns okay so now you treat this from here till here as a table so you treat this as a table employee count so select department name total employees from this employee count where total employees is greater than or equal to 2 so here you know this is a derived table this employee count is a derived table and derived tables are available only in the context of the current query here the Select statement if you have another select statement you know beneath that this derived table is not available for that select statement it's only available here within this context so let's look at this in action so if you look at this one if I execute this it gives us those columns Department name Department ID and total employees all those columns but then what we are essentially doing here is wrapping that inside this bracket and then giving it in name and this name can be treated as a table by itself which has got these three columns and what you are doing from these three columns you're selecting just Department name and total employees columns and then you're filtering on that column so where total employees is greater than or equal to two so obviously we only get those two rows where the employees count is greater than or equal to two which in this case is ID and H are just like other examples that we have seen and finally we will see how to achieve the same thing using something called CDE common table expression we will be talking about si te s in a great detail in the next session this is just an introduction to CDE so achieving the same output with using a CDE common table expression and CTS are introduced in sequel server 2005 so if you look at this one this is again very simple okay so the exact same query that we have been working with this query that you can see here now what we have done is we use this keyword vert and then you have given a name this is nothing but the common table expression name CTE name so with employee count is the name of your CTE and then you can specify the columns that your CTE returns okay this part is optional if this query is returning a unique column names then you don't have to define this column list okay but I have defined it here just to be clear so we this common table expression employee count is having three columns department name department ID total employees okay where are these columns coming from these columns are coming from the Select query so this select query look at this we have the brackets here so this is like a table what is the name of this table employee count and this is a CTE so we use the wit keyword so usually the syntax for CTE is in such a way that you use the width keyword with CTE name the columns that your CTE returns as and your select query your select query actually determines what columns are being returned by the CDE alright so we are done this is pretty much similar to derived table now if you look at this derived table this query you know returns I mean it's like a table you're just naming it as employee count along the same lines for a CTE this query and you're saying with CTE name and the columns as and then your query obviously then what you can do is since this is treated as a table now a common table expression and this common table expression has got three columns just like derive table and out of those three columns we just want the department name and total employees columns from that CD which is employee count here where total employee is greater than or equal to two so we get the same output let's look that in action so that's the CTE so let's execute this and we should get the same output all right a CTE can be thought of as a temporary result set it's a temporary table or you can also think of as I derived a table that is defined within the execution scope of a single select insert update delete or create view statement you can use this city even within a view okay but this is defined only within the execution scope of a single select insert update delete or create view statement of this is a little confusing what we mean by this we are going to talk about common table expressions in a great detail in the next session I will explain this definition clearly you know in the next video session and a CTE is a similar is similar to a derived table in that it is not stored as an object and lost only for the duration of the query so this is not being stored as a temporary table or a view okay it only lasts for the duration of this query outside the context of this query the city it doesn't really have any meaning we'll talk about CD is in a great detail in the next session of this video series on this slide you can find resources for asp.net c-sharp and sequel server interview questions that's it for today thank you for listening have a great day

Channel: kudvenkat

Views: 250,231

Rating: 4.9428573 out of 5

Keywords: derived tables, cte, common table expressions, sql server 2005, sql server 2008, cte vs temp table vs table variable, cte vs table variable, view vs temp table, view vs table variable, table variable vs temp table, table variable in sql server, table variable and temp table difference, table variable in sql

Id: FwcAkH8UyEA

Length: 17min 53sec (1073 seconds)

Published: Mon Sep 24 2012