Comparing Table Variables with Temporary Tables

Question

Post reply

Comparing Table Variables with Temporary Tables

Viewing 15 posts - 106 through 120 (of 163 total)

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

Paul White SSC Guru Points: 150457 More actions · Answer 1

matt32 (4/9/2010)

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

Just standard SELECT INTO behaviour.

A constant is clearly NOT NULL, so the created column becomes NOT NULL too. Any expression can be made NOT NULL by wrapping it in ISNULL, just like for computed columns.

This behaviour is by design, and not particular to temporary tables.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150457 More actions · Answer 2

Paul White

SSC Guru

Points: 150457

April 9, 2010 at 8:59 am

#1149196

WayneS (4/9/2010)

...I just found a way to work around this....

And...



SELECT  TOP (1)

        nr = ISNULL(CONVERT(INTEGER, 1), 123),

        colname = name

INTO    #tmptable

FROM    sys.all_columns;

...illustrates the anti-work around. 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

WayneS SSC Guru Points: 95405 More actions · Answer 3

matt32 (4/9/2010)

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

Paul Randall's blog (see above link) goes into this... under a heavy load you can still have some issues. The trace flag that the blog is about would also be a way to fix this.

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 4

Hi wayne ,

thx i've learned my lesson in the past 🙂

also

select top 1

(select 1) as nr,

name as colname into #tmptable

from sys.all_columns

is working... i've searched a long time for this error that i got until i found that

i got null in some cases of a process ...

@paul-2 .. i know my english is terrible .. learned it from conan the barbarian

regards Matthias

Paul White SSC Guru Points: 150457 More actions · Answer 5

Paul White

SSC Guru

Points: 150457

April 9, 2010 at 9:10 am

#1149204

matt32 (4/9/2010)

.. i know my english is terrible .. learned it from conan the barbarian

Better than my German :laugh:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Bill Kline-270970 SSC Eights! Points: 915 More actions · Answer 6

Bill Kline-270970

SSC Eights!

Points: 915

April 9, 2010 at 9:28 am

#1149217

Great article, good discussion.

T Norton SSC Enthusiast Points: 106 More actions · Answer 7

T Norton

SSC Enthusiast

Points: 106

April 9, 2010 at 9:36 am

#1149223

Very well done..!!!

This article clearly articulates and validates what I've been preaching to my team for years.

Thanks for doing this…! 😀

Cliff Jones SSChampion Points: 10547 More actions · Answer 8

I agree, great article and great discussion on this topic. I see developers get fooled into using a table variable over a temp table due to the fact that the Cost of the execution plan appears to be less for the one using a Table variable. I often have to demonstrate to them that the Cost of the execution plan with the table variable is accurate when there is 1 row and highly inaccurate as the table size grows. Show the estimated rows (1) versus actual rows in the execution plan.

Also explicit naming of indexes and such on a temporary table will come around to bite you, glad you covered that. Very nice job on this article.

peter-757102 SSCertifiable Points: 6877 More actions · Answer 9

Cliff Jones (4/9/2010)

Also explicit naming of indexes and such on a temporary table will come around to bite you, glad you covered that. Very nice job on this article.

I missed why this would be valid for constraints, or should be so.

To me, any declaration you do should be as well named as possible, including constraints. I do not see how such table scope declaration would interfere with different concurrent table creations (which in the background have each their own tablename in tempdb). And if indexes names are scoped to tables as well (i never checked and make them unique in a schema, then there should be no reason for them to be troublesome either.

Can you explain your commens a bit further, so I know why you made it?

Cliff Jones SSChampion Points: 10547 More actions · Answer 10

peter-757102 (4/9/2010)

Cliff Jones (4/9/2010)

Also explicit naming of indexes and such on a temporary table will come around to bite you, glad you covered that. Very nice job on this article.

I missed why this would be valid for constraints, or should be so.

To me, any declaration you do should be as well named as possible, including constraints. I do not see how such table scope declaration would interfere with different concurrent table creations (which in the background have each their own tablename in tempdb). And if indexes names are scoped to tables as well (i never checked and make them unique in a schema, then there should be no reason for them to be troublesome either.

Can you explain your commens a bit further, so I know why you made it?

As Wayne points out in his article, if you create a Temporary table (#TempTableA) and then add an exlicitly named index to that table (TmpIndexA), when 2 users access the same piece of code concurrently, the second will fail since this index already exists.

This is easily missed during testing but will most likely fail especially in a busy OLTP production environment.

peter-757102 SSCertifiable Points: 6877 More actions · Answer 11

Cliff Jones (4/9/2010)

peter-757102 (4/9/2010)

Cliff Jones (4/9/2010)

Also explicit naming of indexes and such on a temporary table will come around to bite you, glad you covered that. Very nice job on this article.

I missed why this would be valid for constraints, or should be so.

To me, any declaration you do should be as well named as possible, including constraints. I do not see how such table scope declaration would interfere with different concurrent table creations (which in the background have each their own tablename in tempdb). And if indexes names are scoped to tables as well (i never checked and make them unique in a schema, then there should be no reason for them to be troublesome either.

Can you explain your commens a bit further, so I know why you made it?

As Wayne points out in his article, if you create a Temporary table (#TempTableA) and then add an exlicitly named index to that table (TmpIndexA), when 2 users access the same piece of code concurrently, the second will fail since this index already exists.

This is easily missed during testing but will most likely fail especially in a busy OLTP production environment.

Sounds like a bug to me, as functional the tablename should be hidden and transparently mapped (thats what the # is for), along with all indexes and constrains that belong to it. In other words, there exist multiple #MyTempTable, none of which is really named #MyTempTable in the tempdb.

If it works any other way, temp tables do not really exist.

For example:

Open two SSMS tabs, both connecting to the same DB on teh same server.

in each, do the following:

create table #x( a int not null );

then do:

select * from tempdb.sys.tables

As tablenames you get (the suffix is likely different in your case):

#x__________________________________________________________________________________________________________________00000000009F

#x__________________________________________________________________________________________________________________0000000000A0

So here we have proof that it concers two different tables.

Then why would creating an index on two different tables concurrenly block eachother?

Lets go further:

Do in each window:

create nonclustered index ix_abc on #x ( a );

then do:

select * from tempdb.sys.indexes where name like 'ix_abc' order by name

you get two indexes with the same name but different Id in the meta tables, totally seperate from eachother.

Cliff Jones SSChampion Points: 10547 More actions · Answer 12

Cliff Jones

SSChampion

Points: 10547

April 9, 2010 at 10:24 am

#1149258

Yes, you are correct. What I was referring to was using named constraints such as explicitly naming the primary key on a temporary table.

create table #MyTemp (Column1 int not null)

ALTER TABLE #MyTemp ADD CONSTRAINT PkTemp PRIMARY KEY CLUSTERED (Column1)

Sorry for the confusion, not enough coffee yet.

peter-757102 SSCertifiable Points: 6877 More actions · Answer 13

Strange, indeed column defaults and primary keys seem not be properly mapped transparently when applied to temp tables.

This can be seen as either a design flaw or an implementation bug in the temp table handling, either way its wrong!

So don't beat developers that code well and are explicit in their naming, pad them on the back and say SQL Server has some issues :).

Personally I find this very disappointing from Microsoft, not to mention counter productive. I will have to check some of my code to see if it too suffers from this illness.

In case one wonders why I am being do resolute here, it is because the implementation breaks transparency.

A temporary table is for use only within the session or stored procedure scope that created it and should logically be hidden for all others (this implies all related constraints).

Then how can there be an existing constraint for a table that does not conceptually exist for other sessions but the one creating it?

Lets face it, it is a half finished implementation. They did implement auto table name remapping, but not constraint remapping and this is where the error comes from.

kajalchatterjee Newbie Points: 6 More actions · Answer 14

Comparing Table Variables with Temporary Tables

I ran the following piece of code in SQL-Server 2005 and i can not see any entry of table variable in tempdb database. I can only see temp table entry in tempdb database.

So it is not Myth that table variables are stored in Memory but it seems tobe tru. Please let me know if am i missing anything.

---------------------------------------

-- make a list of all of the user tables currently active in the

-- TempDB database

if object_id('tempdb..#tempTables') isnot null droptable #tempTables

select name into #tempTables from tempdb..sysobjects where type ='U'

-- prove that even this new temporary table is in the list.

-- Note the suffix at the end of it to uniquely identify the table across sessions.

select * from #tempTables where name like '#tempTables%'

GO

-- create a table variable

declare @MyTableVariable table (RowID int)

-- show all of the new user tables in the TempDB database.

select name from tempdb..sysobjects

where type ='U' and name not in (select name from #tempTables)

GO

-----------------------------------

SQLRNNR SSC Guru Points: 281324 More actions · Answer 15

SQLRNNR

SSC Guru

Points: 281324

April 9, 2010 at 3:40 pm

#1149400

Thanks Wayne. Nice article.

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