Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQLBoy14
Yak Posting Veteran
70 Posts |
 Posted - 2015-04-19 : 20:45:23
|
| The different between CTE (Common Table Expression) and TmpTbl |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2015-04-20 : 01:29:07
|
| Temporary tables were designed for storage and manipulation of temporal data.My general rule of thumb - is if the data set is greater tham 75000 rows and some manipulation is required , temporary tables are a good option. There are a number of features possible with temporary tables such as constraints, parallel execution, indexes , statistics.Read more on temporary tables http://www.sqlserver-dba.com/2011/12/sql-table-variable-and-temporary-table.htmlJack Vamvas--------------------http://www.sqlserver-dba.com |
 |
|
|
rocknpop
Posting Yak Master
201 Posts |
Posted - 2015-04-20 : 01:30:16
|
| Why would you us a Temp Table over a CTE:1. you need to re-use the temp table at multiple places in your code 2. you need to create an index on the temp tableWhy would you us a CTE over a Temp Table:1. you need to refer to the CTE only once immediately after the definition. That being said, it may not be the case that is optimal over a temp table. It depends and best is to check the execution plan for both2. you need to write a recursive query--------------------Rock n Roll with SQL |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2015-04-21 : 01:28:08
|
| If you're intending to return a large set of data with CTE, peform some response time testing .Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
|
|
Common Table Expression and Temp Table