Comparing Table Variables with Temporary Tables

Question

Comparing Table Variables with Temporary Tables

Viewing 15 posts - 91 through 105 (of 163 total)

You must be logged in to reply to this topic. Login to reply

Knight SSCommitted Points: 1856 More actions · Answer 1

Great article and in my experience with table variables its usually devs not implementing them properly in complex queries 😉 I shall ensure part of this article goes into our best practices just to hammer the point home 🙂

matt32 SSC Veteran Points: 264 More actions · Answer 2

Hi Wayne ,

great article and here some problems i encountered while working with

#temp for further articles with the same focus.

1. Creating a #temptable within a SP as select .. into #temptable will lock the tempdb for the time until the #temptable is created.(may take a while for large data amounts)

The same statement as TSql script will not lock tempdb.

2. Creating a #temptable as select expression,.. into #temptable will generate a not null constraint on the expression column.

regards

Matthias Kroll

WayneS SSC Guru Points: 95460 More actions · Answer 3

Gianluca Sartori (4/9/2010)
Thanks for clarifying Wayne. So, it works the same as permanent tables: I was fearing that a different behaviour could apply.
I wrote that because I use indexes on temp tables in some of my procedures ad I've never seen problems in concurrency, but that sentence in your article made me think.

Yes, it is misleading. I added this to the first post in this thread as an "Article errata". Thanks for pointing it out.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 4

Knight (4/9/2010)
Great article

Thanks

I shall ensure part of this article goes into our best practices just to hammer the point home 🙂

:blush: Thanks again!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Eddie-266113 SSC Enthusiast Points: 151 More actions · Answer 5

Are there any concerns when using a local temp table (#sometable) and connection pooling? Is it possible for a user to hop on a previously used connection and session and view data unrelated to that user?

Kindest Regards,

Eddie

WayneS SSC Guru Points: 95460 More actions · Answer 6

matt32 (4/9/2010)
Hi Wayne ,
great article and here some problems i encountered while working with
#temp for further articles with the same focus.
1. Creating a #temptable within a SP as select .. into #temptable will lock the tempdb for the time until the #temptable is created.(may take a while for large data amounts)
The same statement as TSql script will not lock tempdb.
2. Creating a #temptable as select expression,.. into #temptable will generate a not null constraint on the expression column.
regards
Matthias Kroll

Regarding #1, please see Paul Randall's blog about how he changed this in SQL 2005. Specifically see item #3.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Paul White SSC Guru Points: 150467 More actions · Answer 7

WayneS (4/9/2010)
matt32 (4/9/2010)
1. Creating a #temptable within a SP as select .. into #temptable will lock the tempdb for the time until the #temptable is created.(may take a while for large data amounts)
Regarding #1, please see Paul Randall's blog about how he changed this in SQL 2005. Specifically see item #3.

Actually, Matthias was referring to a SQL Server 6.5 behaviour which was fixed in SQL Server 7 :w00t:

See http://www.simple-talk.com/community/blogs/tony_davis/archive/2009/10/28/75834.aspx for the gory details.

BTW Wayne, did this article change much between publications? It seems more comprehensive than previously - or is my memory playing tricks again?

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

WayneS SSC Guru Points: 95460 More actions · Answer 8

Paul White NZ (4/9/2010)
BTW Wayne, did this article change much between publications? It seems more comprehensive than previously - or is my memory playing tricks again?
Paul

No changes at all. They say that your memory is the first thing to .... hey, what was I talking about again?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Paul White SSC Guru Points: 150467 More actions · Answer 9

WayneS (4/9/2010)
No changes at all. They say that your memory is the first thing to .... hey, what was I talking about again?

Oh :blush: 🙁 :crying:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

matt32 SSC Veteran Points: 264 More actions · Answer 10

Thx Wayne for the link to Paul Randall.

@paul-2

I had this issue some month ago with a customers 2005 db.

heavy data load using a SP with select .. into #temp. During this time no one could start any App using tempdb (eg activity monitor within SSMS also Toad for SQLServer cant get a connection).

the solution was :

1. create #temp before insert

2. create #temp not within a sp

and what wonder no more locks on tempdb ... so i think there is this days a issue with that

select .. into #temp and it's not really solved with 2005. (all SP's installed)

If you have the time and large data tables .. test it by your own.

regards Matthias

Robert Frasca SSCertifiable Points: 6026 More actions · Answer 11

Great article but I had one question. The reference about when temp tables can cause recompilations takes you to an article relevant to SQL Server 2000 and when stored procedures are recompiled. I thought that, as of SQL Server 2005, only individual statements within the proc were recompiled and not the entire stored procedure. Wouldn't that, theoretically, reduce the recompilation overhead? Also, what if the temp table is used in a join. Would that force a recompile of the statement performing the JOIN?

I apologize, I'd test this myself but I'm swamped so I'm hoping you (or someone else here) might have an answer off the top of your head.

"Beliefs" get in the way of learning.

WayneS SSC Guru Points: 95460 More actions · Answer 12

matt32 (4/9/2010)
2. Creating a #temptable as select expression,.. into #temptable will generate a not null constraint on the expression column.

Has anyone ever heard of this before?

I tried to duplicate it with this code, but I don't get an error.

USE AdventureWorks

GO

if OBJECT_ID('tempdb..#Test2') IS NOT NULL DROP TABLE #Test2

select top 50 *, TestCalc = PerAssemblyQty*50

into #Test2

from Production.BillOfMaterials

insert into #test2

select top 5 ProductAssemblyID, ComponentID, StartDate, EndDate, UnitMeasureCode,

BOMLevel, PerAssemblyQty, ModifiedDate, NULL

from Production.BillOfMaterials

select *

from #test2

Matthias, do you have some code that demonstrates this?

Edit: typo, added database

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

matt32 SSC Veteran Points: 264 More actions · Answer 13

Hi Wayne

select top 1

1 as nr,

name as colname into #tmptable

from sys.all_columns

select * from #tmptable

insert into

#tmptable

select null, 'what'

you will get a not null constraint violation ...

Paul White SSC Guru Points: 150467 More actions · Answer 14

matt32 (4/9/2010)
so i think there is this days a issue with that
select .. into #temp and it's not really solved with 2005.

It was the phrasing of your original statement that made me think you were referring to the SELECT INTO tempdb modern myth. I see now that English is not your first language, so I understand.

You will find lots of other details on Paul's blog about tempdb allocation contention. Creating several equally-sized tempdb files seems to cure all problems. Anyway, read about it 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

WayneS SSC Guru Points: 95460 More actions · Answer 15

Matthias, I've never seen this before. Very interesting.

If you're interested, I just found a way to work around this.

select top 1

CONVERT(int, 1) as nr,

name as colname into #tmptable

from sys.all_columns

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2