{"id":147,"date":"2014-08-11T10:44:39","date_gmt":"2014-08-11T10:44:39","guid":{"rendered":"http:\/\/mairwa.com\/wordpress\/?p=147"},"modified":"2014-08-11T10:44:39","modified_gmt":"2014-08-11T10:44:39","slug":"table-variable-cte-and-temporary-table-scope","status":"publish","type":"post","link":"http:\/\/mairwa.com\/wordpress\/?p=147","title":{"rendered":"Table variable , CTE and Temporary table scope"},"content":{"rendered":"<p><b>When to Use CTE:<\/b><\/p>\n<p>It stands for Common table expression.it was introduced on sql server 2005.<\/p>\n<p>Below is details for using a CTE:<\/p>\n<ul>\n<li>This is used to store result of a complex sub query for further use.<\/li>\n<li>It is \u00a0also used to create a recursive query.<\/li>\n<\/ul>\n<p><b>When to use Temporary Table:<\/b><\/p>\n<p>It is created at Run-time and behave same as normal table but created within session or application level not storage physically in database.<\/p>\n<p><span style=\"text-decoration:underline;\">There are two types of Temporary table:<\/span><\/p>\n<p>1)Local(#) temp table\u2014stored in tempdb table and available within session or connection. These are automatically deleted when the session that created the tables has been closed.<\/p>\n<p>2)Global (##)Temp table\u2014stored also in tempdb table and available within all sql server session and connections(i.e. all users).it is automatically deletd when all sql connections users have been closed.<\/p>\n<p><b>Choosing Between Temporary Tables and CTE<\/b><\/p>\n<ul>\n<li>Temp Tables are physically created in the Tempdb database. These tables act as the normal table and also can have constraints, index like normal tables.<\/li>\n<li>CTE is a named temporary result set which is used to manipulate the complex sub-queries data. This exists for the scope of statement. This is created in memory rather than Tempdb database. You cannot create any index on CTE.<\/li>\n<li>Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory.<\/li>\n<\/ul>\n<p><b>Choosing Between Temporary Tables and Table Variables<\/b><\/p>\n<p>We know there are situations that which demand the use of a temporary table.<\/p>\n<p>1)This in-cludes calling nested stored procedures which use the resultset, certain scenarios using dy-namic SQL, and cases where you need transaction rollback support.<\/p>\n<p>2) The size of the resultset will determine which solution to choose. If the table stores a resultset so large you require indexes to improve query performance, we\u2019ll need to stick to a temporary table. If the resultset is small, the table variable is always the optimum choice.<\/p>\n<p>3) Table variables can offer performance benefits and flexibility when compared to temporary tables, and let the server clean up afterwards.<\/p>\n<p>4) In case of large amount of data temp table is advised because sql server creates, maintains and uses the statistics of temp table while generating the execution plan. Besides this we can create index in tenp tables if that is needed.<\/p>\n<p>5) Table variable used where they are joined with small tables only.<\/p>\n<p>6) Table variable&#8217;s lifespan is only for the duration of the transaction that it runs in.<\/p>\n<p>7) In a user defined function if we want to access a temp table it is not possible, where we can access a table variable. For example: the following will work: the second example will not work, there will be compilation errors:<\/p>\n<p><b>Example1:<\/b><\/p>\n<p>CREATE FUNCTION dbo.example1<br \/>\n(<br \/>\n)<br \/>\nRETURNS INT<br \/>\nAS<br \/>\nBEGIN<br \/>\nDECLARE @t1 TABLE (i INT)<br \/>\nINSERT @t1 VALUES(1)<br \/>\nINSERT @t1 VALUES(2)<br \/>\nUPDATE @t1 SET i = i + 5<br \/>\nDELETE @t1 WHERE i &lt; 7<\/p>\n<p>DECLARE @max INT<br \/>\nSELECT @max = MAX(i) FROM @t1<br \/>\nRETURN @max<br \/>\nEND<br \/>\nGO<\/p>\n<p><b>Example 2:<\/b><\/p>\n<p>CREATE FUNCTION dbo.example2<br \/>\n(<br \/>\n)<br \/>\nRETURNS INT<br \/>\nAS<br \/>\nBEGIN<br \/>\nCREATE TABLE #t1 (i INT)<br \/>\nINSERT #t1 VALUES(1)<br \/>\nINSERT #t1 VALUES(2)<br \/>\nUPDATE #t1 SET i = i + 5<br \/>\nDELETE #t1 WHERE i &lt; 7<\/p>\n<p>DECLARE @max INT<br \/>\nSELECT @max = MAX(i) FROM #t1<br \/>\nRETURN @max<br \/>\nEND<\/p>\n<p><a href=\"http:\/\/mairwa.com\/wordpress\/wp-content\/uploads\/2014\/08\/Untitled6.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-148\" alt=\"Untitled\" src=\"http:\/\/mairwa.com\/wordpress\/wp-content\/uploads\/2014\/08\/Untitled6.png\" width=\"704\" height=\"496\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p class=\"excerpt\">When to Use CTE: It stands for Common table expression.it was introduced on sql server 2005. Below is details for using a CTE: This is used to store result of a complex sub query for further use. It is \u00a0also used to create a recursive query. When to use Temporary Table: It is created at Run-time and behave same as&hellip; <a href=\"http:\/\/mairwa.com\/wordpress\/?p=147\">Read more &rarr;<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14],"tags":[],"class_list":["post-147","post","type-post","status-publish","format-standard","hentry","category-sql-server","xfolkentry"],"_links":{"self":[{"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/147","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=147"}],"version-history":[{"count":0,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/147\/revisions"}],"wp:attachment":[{"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=147"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=147"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=147"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}