August 12, 2015 at 1:01 pm
#temp is much more useful than @temp
August 12, 2015 at 3:29 pm
caojunhe24 (8/12/2015)
#temp is much more useful than @temp
Care to share any of the reasons you think so? Without such a list, you comment is just an opinion.
--Jeff Moden
August 13, 2015 at 5:13 am
caojunhe24 (8/12/2015)
#temp is much more useful than @temp
Really? What about in the case of recompiles?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
August 13, 2015 at 6:25 am
Grant Fritchey (8/13/2015)
caojunhe24 (8/12/2015)
#temp is much more useful than @tempReally? What about in the case of recompiles?
I heard a rumor that statistics can impact query performance as well. 😉
My advice? Test, test and test some more.
August 13, 2015 at 10:37 am
Grant Fritchey (8/13/2015)
caojunhe24 (8/12/2015)
#temp is much more useful than @tempReally? What about in the case of recompiles?
Or a user-defined table type?
Better yet...
A user defined memory optimized table type?
What about in the case of a memory optimized table variable?
Table variables are at the crux of user-defined table types and they are very handy for receiving an array into a stored proc.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 13, 2015 at 11:33 am
SQLRNNR (8/13/2015)
Grant Fritchey (8/13/2015)
caojunhe24 (8/12/2015)
#temp is much more useful than @tempReally? What about in the case of recompiles?
Or a user-defined table type?
Better yet...
A user defined memory optimized table type?
What about in the case of a memory optimized table variable?
Table variables are at the crux of user-defined table types and they are very handy for receiving an array into a stored proc.
Yeah, especially love the memory-optimized table. That's great.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
August 13, 2015 at 12:14 pm
Generally speaking:
Table variables have scope limited to what one would expect with other types of variables, and it allow for fixed plans, which is a strong and compelling reason to stick with them when writing stored procedures for an OLTP scenario where data retreival and writes are low and maximum (sub-second) performance is required. Even the thing about use of table variable in DML operations resulting in non-parallel plans seems to fit well with an OLTP case usage.
Unlike a table variable, you can SELECT.. INTO.. a temp table. Also it supports statistics and non-clustered indexes, which are strong and compelling reason to stick with temp tables for stored procedures which perform heavy duty ETL or reporting type processing.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 13, 2015 at 2:20 pm
Eric M Russell (8/13/2015)
Also it supports statistics and non-clustered indexes...
Table variables can have nonclustered indexes. No stats, but they can have nonclustered indexes. You just have to define them in the initial table definition.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2015 at 2:44 pm
SQLRNNR (8/13/2015)
Grant Fritchey (8/13/2015)
caojunhe24 (8/12/2015)
#temp is much more useful than @tempReally? What about in the case of recompiles?
Or a user-defined table type?
Better yet...
A user defined memory optimized table type?
What about in the case of a memory optimized table variable?
Table variables are at the crux of user-defined table types and they are very handy for receiving an array into a stored proc.
Or perhaps an edge case where you might need to rollback a transaction but not have it affect the temp data. A table variable is excellent in that situation (although I have never needed that in the real world).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 13, 2015 at 2:57 pm
Sean Lange (8/13/2015)
SQLRNNR (8/13/2015)
Grant Fritchey (8/13/2015)
caojunhe24 (8/12/2015)
#temp is much more useful than @tempReally? What about in the case of recompiles?
Or a user-defined table type?
Better yet...
A user defined memory optimized table type?
What about in the case of a memory optimized table variable?
Table variables are at the crux of user-defined table types and they are very handy for receiving an array into a stored proc.
Or perhaps an edge case where you might need to rollback a transaction but not have it affect the temp data. A table variable is excellent in that situation (although I have never needed that in the real world).
I wish that temp tables and table variables were simply non-transacted. I've never had a need to rollback a temp table, although there have been many occasions where I've hit the cancel button on a query that was dumping too much data into a temp table and filling tempdb and transaction log.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 14, 2015 at 6:23 am
If you are manually running some code in SSMS, table variables have the additional advantage of being generated fresh on every run. Temp tables stay around, and you have to add code to either clear their contents, or delete and re-create them on every non-first run.
If you are creating something that will reside in a stored procedure, the main difference is as Grant says, that temp tables have statistics, and so are more appropriate for operations where you will be doing operations that involve multiple references to the table, like joins. The query optimizer can use those statistics to generate better query plans for operations on the temp tables.
Table variables are simple for short-term 'clipboard' use, where you maybe collect a subset of info, then do simple operations on that subset, like additional filtering, summing all results and such, where you need to scan the entire dataset anyway, and statistics won't speed up anything. In fact, they might slow the operation down, since creation of statistics takes some time. If they're never going to be used, it's pointless to generate them.
August 14, 2015 at 8:31 am
GilaMonster (8/13/2015)
Eric M Russell (8/13/2015)
Also it supports statistics and non-clustered indexes...Table variables can have nonclustered indexes. No stats, but they can have nonclustered indexes. You just have to define them in the initial table definition.
Table variables can have nonclustered indexes, but I thought they had to be tied to a PRIMARY KEY or UNIQUE constraint. Can you define a nonclustered index on a non-unique set of columns in a table variable?
August 14, 2015 at 8:44 am
pdanes (8/14/2015)
If you are manually running some code in SSMS, table variables have the additional advantage of being generated fresh on every run. Temp tables stay around, and you have to add code to either clear their contents, or delete and re-create them on every non-first run.
That can also be a disadvantage if you're troubleshooting and need to see the content or it needs to persist so you can troubleshoot a section after the "table" creation without having to rerun all the preceding code.
--Jeff Moden
August 14, 2015 at 8:49 am
Eric M Russell (8/13/2015)
I wish that temp tables and table variables were simply non-transacted.
I'll strongly second that wish. I also wish that there were a way to mark certain things within transactions as exceptions that should not be rolled back to make on the fly logging a whole lot easier.
--Jeff Moden
August 14, 2015 at 9:39 am
Jeff Moden (8/14/2015)
Eric M Russell (8/13/2015)
I wish that temp tables and table variables were simply non-transacted.I'll strongly second that wish. I also wish that there were a way to mark certain things within transactions as exceptions that should not be rolled back to make on the fly logging a whole lot easier.
Are you talking about support for autonomous transactions, perhaps similar to Oracle's AUTONOMOUS_TRANSACTION pragma?
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/autonotransaction_pragma.htm
When it comes to things like ETL or heavy duty aggregate reporting; ACID can become an unnecessary overhead, and SQL Server could benefit from something like a SET AUTONOMOUS_TRANSACTION ON; option for use in stored procedures or ad-hoc querying.
I recall a few years back about how to simulate that in SQL Server using a loopback linked server. However, I'm not sure I'd want to do down that path, unless it were for a one-off process that really needed it.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply