<p>I'm looking at improving the performance of some SQL, currently CTEs are being used and referenced multiple times in the script. Would I get improvements using a table variable instead? (Can't use a temporary table as the code is within functions).</p>

[{'answer_id': 54194, 'author': 'Joel Coehoorn', 'author_id': 3043, 'author_profile': 'https://Stackoverflow.com/users/3043', 'pm_score': 2, 'selected': False, 'text': "<p>Probably not.  CTE's are especially good at querying data for tree structures.</p>\n"}, {'answer_id': 66519, 'author': 'Meff', 'author_id': 9647, 'author_profile': 'https://Stackoverflow.com/users/9647', 'pm_score': 4, 'selected': True, 'text': "<p>You'll really have to performance test - There is no Yes/No answer. As per Andy Living's post above links to, a CTE is just shorthand for a query or subquery. </p>\n\n<p>If you are calling it twice or more in the same function, you might get better performance if you fill a table variable and then join to/select from that. However, as table variables take up space somewhere, and don't have indexes/statistics (With the exception of any declared primary key on the table variable) there's no way of saying which will be faster.</p>\n\n<p>They both have costs and savings, and which is the best way depends on the data they pull in and what they do with it. I've been in your situation, and after testing for speed under various conditions - Some functions used CTEs, and others used table variables.</p>\n"}, {'answer_id': 62466214, 'author': 'Samim Hussain', 'author_id': 6223175, 'author_profile': 'https://Stackoverflow.com/users/6223175', 'pm_score': 0, 'selected': False, 'text': "<p><strong>Advantages of CTE</strong></p>\n\n<p>CTE can be termed as 'Temporary View' used as a good alternative for a View in some cases.\nThe main advantage over a view is usage of memory. As CTE's scope is limited only to its batch, the memory allocated for it is flushed as soon as its batch is crossed. But once a view is created, it is stored until user drops it. If the view is not used after creation then it's a mere waste of memory.\nCPU cost for CTE execution is lesser when compared to that of View.\nLike View, CTE doesn't store any metadata of its definition and provides better readability.\nA CTE can be referred for multiple times in a query.\nAs the scope is limited to the batch, multiple CTEs can have the same name which a view cannot have.\nIt can be made recursive.</p>\n\n<p><strong>Disadvantages of CTE</strong></p>\n\n<p>Though using CTE is advantageous, it does have some limitations to be kept in mind,\nWe knew that it is a substitute for a view but a CTE cannot be nested while Views can be nested.\nView once declared can be used for any number of times but CTE cannot be used. It should be declared every time you want to use it. For this scenario, CTE is not recommended to use as it is a tiring job for user to declare the batches again and again.\nBetween the anchor members there should be operators like UNION, UNION ALL or EXCEPT etc.\nIn Recursive CTEs, you can define many Anchor Members and Recursive Members but all the Anchor Members must be defined before the first Recursive Member. You cannot define an Anchor Member between two Recursive Member.\nThe number of columns, the data types used in Anchor and Recursive Members should be same.\nIn Recursive Member, aggregate functions like TOP, operator like DISTINCT, clause like HAVING and GROUP BY, Sub-queries, joins like Left Outer or Right Outer or Full Outer are not allowed. Regarding Joins, only Inner Join is allowed in Recursive Member.\nRecursion Limit is 32767, crossing which results in the crash of server due to infinite loop.</p>\n"}, {'answer_id': 73371261, 'author': 'Shnugo', 'author_id': 5089204, 'author_profile': 'https://Stackoverflow.com/users/5089204', 'pm_score': 3, 'selected': False, 'text': '<p>A <strong>CTE</strong> is not much more than <strong>syntactic sugar</strong>.<br />\nIt <strong>enhances the readability</strong> and <strong>allows to avoid repetition</strong>.</p>\n<p>Just think of it as a placeholder for the actual statement specified in the <code>WITH()</code>-clause. The engine will replace any occurance of the CTE\'s name in your query with this statement (quite similar to a view). This is the meaning of <em>inline</em>.</p>\n<p><strong>Compared to a previously filled table</strong> (delared or created) You\'ll find <strong>advantages</strong>:</p>\n<ul>\n<li>useable in <em>ad-hoc</em>-queries (functions, views)</li>\n<li>no unexpected side effects (most narrow scope)</li>\n</ul>\n<p>...and <strong>disadvantages</strong>:</p>\n<ul>\n<li>You cannot use the CTE\'s result in different statements</li>\n<li>You cannot use indexes, statistics to optimize your CTE\'s set (although it will implicitly use existing indexes and statistics of the targeted objects - if appropriate).</li>\n</ul>\n<p>In <strong>terms of performance</strong> a <em>persisted</em> set (declared or created table) can be (much!) better in some cases, but it forces you into procedural code. You will have to <em>race your horses</em> to find out which is better...</p>\n<p><strong>Example: Various approaches to do the same</strong></p>\n<p>The following simple (rather useless) example describes a set of user tables together with their columns. I use various different approaches to tell SQL-Server what I want:</p>\n<p>Try this with &quot;include actual execution plan&quot;</p>\n<pre><code>USE master; --in my case the master database has just 5 &quot;user tables&quot;, you can use any other DB of course\nGO\n    \n--simple join, first the small set joining to the large set\nSELECT o.name AS TableName\n      ,c.name AS ColumnName\nFROM sys.objects o\nINNER JOIN sys.columns c ON c.object_id=o.object_id \nWHERE o.type=\'U\';\nGO\n\n--simple join &quot;the other way round&quot; with the filter as part of the ON-clause\nSELECT o.name AS TableName\n      ,c.name AS ColumnName\nFROM sys.columns c\nINNER JOIN sys.objects o ON c.object_id=o.object_id AND o.type=\'U\';\nGO\n\n--join from the large set with a sub-query to the small set\nSELECT o.name AS TableName\n      ,c.name AS ColumnName\nFROM sys.columns c\nINNER JOIN (\n    SELECT o.* \n    FROM sys.objects o\n    WHERE o.type=\'U\' --user tables \n) o ON c.object_id=o.object_id;\nGO\n\n--join for large to small with a row-wise APPLY\nSELECT o.name AS TableName\n      ,c.name AS ColumnName\nFROM sys.columns c\nCROSS APPLY (\n    SELECT o.* \n    FROM sys.objects o\n    WHERE o.type=\'U\' --user tables \n    AND o.object_id=c.object_id\n) o;\nGO\n\n--use a CTE to &quot;pre-filter&quot; the small set\nWITH cte AS\n(\n    SELECT o.* \n    FROM sys.objects o\n    WHERE o.type=\'U\' --user tables \n)\nSELECT cte.name AS TableName\n      ,c.name AS ColumnName\nFROM sys.columns c\nINNER JOIN cte ON c.object_id=cte.object_id;\nGO\n</code></pre>\n<p>Now look at the result and at the execution plans:</p>\n<ul>\n<li>All queries return the same result.</li>\n<li>All queries produce the same execution plan</li>\n</ul>\n<p>Important hint: This might differ on your machine!</p>\n<p><strong>Why is this?</strong></p>\n<p>T-SQL is a <strong>declarative language</strong>. Your statement is a description of <strong>WHAT</strong> you want to retrieve. It is not your job to tell the engine <strong>HOW</strong> this is done.</p>\n<p>SQL-Server\'s extremely smart engine will find the best way to get the set you asked for. In the case above all result descriptions point to the same goal. The engine can <em>deduce</em> this from various statements and finds the same plan for all of them.</p>\n<p><strong>Well, is it just a matter of taste?</strong></p>\n<p>In a way...</p>\n<p>There are some important things to keep in mind:</p>\n<ul>\n<li>There is no reason for the engine to compute the CTE\'s result <em>before</em> the rest (although the statement might <em>look</em> so). Therefore it is <strong>wrong</strong> to describe a CTE as <em>something like a temp table</em>...</li>\n<li>In other words: The <strong>visible order</strong> of your statement does <strong>not predict the actual order of execution</strong>!</li>\n<li>The smart engine will reach its limits with complexity and nest level. Imagine various <code>VIEW</code>s, all using <code>CTE</code>s and calling each-other...</li>\n<li>There are cases where the engine really f**s up. I remember a case where a CTE did not much more than a <code>TRY_CAST</code>. The idea was to ensure valid values in the query below. But the engine thought &quot;Oh, just a CAST, not expensiv!&quot; and included the acutal CAST to the execution plan on a higher position. I remember another case where the engine performed an expensive operation against millions of rows (unnecessarily, the final result was filtered to a tiny set), just because the actual order of execution was not as expected.</li>\n</ul>\n<p><strong>Okay... So when should I use a CTE?</strong></p>\n<p>The following points are good reasons to use a CTE:</p>\n<ul>\n<li>A CTE can help you to avoid repeated sub queries.</li>\n<li>A CTE can be used multiple times within your statement, e.g. within a <code>JOIN</code> with a dynamic behavior depending on the actual row-count.</li>\n<li>You can use multiple CTEs within one statement and you can use the result of one CTE within a later CTE.</li>\n<li>There are recursive (or better <em>iterative</em>) CTEs.</li>\n<li>Sometimes I used <em>single-row</em>-CTEs to define / pre-compute <em>variables</em> later used in the query. Things you would do with declared variables in procedural T-SQL. You can use A <code>CROSS JOIN</code> to get them into your query easily.</li>\n<li>and also very nice: the <a href="https://stackoverflow.com/a/11562724/5089204">updatable CTE</a> allows for very easy-to-read statements, same applies <a href="https://stackoverflow.com/a/22762991/5089204">for <code>DELETE</code></a>.<br />\nAs above: Nothing one could not do without the CTE, but it is far better to read (I really like <em>speaking names</em>).</li>\n</ul>\n<p><strong>Final hints</strong></p>\n<p>Well, there are cases, where ugly code performs better :-)</p>\n<p>It is always good to have clean and readable code. A CTE will help you with this. So give it a try. If the performance is bad, get into depth, look at the execution plans and try to find a reason where the engine might decide wrong.</p>\n<p>In most cases it is a bad idea trying to outsmart the engine with hints such as <code>FORCE ORDER</code> (but in can help)</p>\n<p><strong>UPDATE</strong></p>\n<p>I was asked to point to advantages and disadvantages specifically:</p>\n<p>Uhm, <em>technically</em> there are <strong>no real advantages or disadvantages</strong>. Disregarding <em>recursive CTEs</em> there\'s nothing one couldn\'t solve without a CTE.</p>\n<p><em>Advantages</em><br />\nThe main <em>advantage</em> is <strong>readability and maintainabilty</strong>.<br />\nSometimes a CTE can save hundreds of lines of code. Instead of a repeating a huge sub-query one can use just a name as a <em>variable</em>. Corrections to the sub-query can be solved just in one place.<br />\nThe CTE can serve in ad-hoc queries and make your life easier.</p>\n<p><em>Disadvantages</em><br />\nOne <em>possible disadvantage</em> is that it\'s very easy, even for experienced developers, to mistake a CTE as a temp table, assume that the visible order of steps will be the same as the acutal order of execution and stumble into <strong>unexpected results or even errors</strong>.\nAnd - of course :-) - the strange <code>wrong syntax</code> error you\'ll see when you write a CTE after another statement without a separating <code>;</code>. That\'s why many people tend to use <code>;WITH</code>.</p>\n'}, {'answer_id': 73440461, 'author': 'SargeATM', 'author_id': 309960, 'author_profile': 'https://Stackoverflow.com/users/309960', 'pm_score': 1, 'selected': False, 'text': '<p>The information and quotes are from the following article on mssqltips.com &quot;Choose Between SQL Server Subquery T-SQL Code&quot; by Eric Blinn. <a href="https://www.mssqltips.com/sqlservertip/6618/sql-server-query-performance-cte-view-subquery-temp-table-table-variable/" rel="nofollow noreferrer">https://www.mssqltips.com/sqlservertip/6618/sql-server-query-performance-cte-view-subquery-temp-table-table-variable/</a></p>\n<h2>SQL Server 2019 CTEs, subqueries, and views</h2>\n<blockquote>\n<p>The SQL Server [2019] engine optimizes every query that is given to it.  When\nit encounters a CTE, traditional subquery, or view, it sees them all\nthe same way and optimizes them the same way.  This involves looking\nat the underlying tables, considering their statistics, and choosing\nthe best way to proceed.  In most cases they will return the same plan\nand therefore <strong>perform exactly the same</strong>.</p>\n</blockquote>\n<h3>TempDB table</h3>\n<blockquote>\n<p>For the query that inserted rows into the temporary table, the\noptimizer looked at the table statistics and chose the best way\nforward.  It actually made new table statistics for the temporary\ntable and then used them to run the second.  This <strong>brings about very\nsimilar performance</strong>.</p>\n</blockquote>\n<h3>Table variable</h3>\n<p>The table variable <strong>has poor performance</strong> in the example given in the article <strong>due to lack of table statistics</strong>.</p>\n<blockquote>\n<p>...the table variable does not have any table statistics generated for\nit like the TempDB table did.  This means the optimizer has to make a\nwild guess as to how to proceed.  In this example it made a very, very\npoor decision.</p>\n<p>This is not to write off table variables.  They surely have their\nplace as will be discussed later in the tip.</p>\n</blockquote>\n<h3>Temp table vs Table variable</h3>\n<blockquote>\n<p>A temporary table will be stored on disk and have statistics\ncalculated on it and a table variable will not.  Because of this\ndifference temporary tables are best when the expected row count\nis &gt;100 and the table variable for smaller expected row counts where the lack of statistics will be less likely to lead to a bad query plan.</p>\n</blockquote>\n'}]
['https://Stackoverflow.com/questions/54176', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/5556/']