What is CTE ( Common table expression) in SQL Server ( Doubt solving sessions)?
Articles,  Blog

What is CTE ( Common table expression) in SQL Server ( Doubt solving sessions)?

Hello welcome to doubt sessions of questpond so we have in this session you know will clarify the doubt about CTE or common table expression so here’s a email we have got from one of our users I want to clarify common table expression in SQL I have ask many times but did not respond to me please clarify okay so what I will do is let me just quickly go to first to MSDN and let’s just talk about definition and then with then will do some demonstration okay you can see that a common table expression nothing but it’s a temporary result set it’s a temporary table okay and you know on this temporary table right you know we can go and do insert update and delete but within that scope so in other words I cannot use this common table expressions if let say I have create a common table expression in stored procedure right in one stored procedure I cannot use in other stored procedure so in short you know what we can says that you know common table expression is like derive table it’s temporary result set okay so let me just show you demo here so you can see that I have a very simple table here called as TBL customer and it has like you know some three or four five fills here okay now let say that we don’t want to see all of these fills you know we just want must be just the customer code and customer name that’s it so we want to create very simple temporary result set you know which will have customer code and customer name so let me just go back and flip to MSDN here so if you see the syntax of the common table expression the way they write it says that with the common table expression name and then what fills you want okay so let me just go and create a common table expression here so in other words you know what we like to do here is you know this five fill table what we have we like to create a very simple temporary table which has only two fills that is customer code and customer name and we will like to use that temporary table you know for a short duration while the select queries getting executed and then we don’t want it so the way to write a CTE you can see the syntax here with CTE name and how many fills you want so currently let say that we just want customer code and customer name so the way will write the CTE is the syntax is with so CTE name I will say name as customer light and what do I want I want the customer code and customer name right and you can see that it’s followed you know web with a round bracket so as when we have round bracket first round bracket and second round bracket and will say that select only customer code , customer name you know from tbl _customer okay right now you can see that we have created a CTE with the name called as customer light okay so in other words now if I go and select customer light here so now if you do select here if I say select star from customer light you can see their customer light has now popped up as common table expression CTE here and if execute this i can only see now two columns as compare five columns in the previous table so as said in MSDN CTE is nothing but it can be thought as temporary result set and you know on this temporary result set always go and do selects you can do insert updates and deletes okay and if you see further right what is use of CTE because many people confuse CTE with temp tables right so I will not discuss you know those things at this moment in this doubt solving session here must be I can create a separate video you know what’s difference between CTE and temp tables and views okay but you know very shortly if you see what is MSDN says is that a CTE can be use to create recursive query in other words I can create CTE and call a recursively second you know it’s a substitution also for the views if you see views are nothing but it’s it’s again like temporary table right but you know when sometimes you don’t want store that meta data like in view we actually create view right and then view actually get stored in to SQL server so some time you don’t want to create meta data you want to just create something on run time like this temporary table use it and then finish it of so at that time agin you know CTEs you know very useful and you know finally if you see right CTE also offers advantages improving your SQL readability right so I hope that you know this doubt solving session has clarified what is a use of CTE and you know what exactly it us Thank You thank you Very Much…..


Leave a Reply

Your email address will not be published. Required fields are marked *