I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

SQL Server 2005 temporary tables bug, feature or expected behavior?

In my opinion these 2 batches should behave the same but they don't. the first fails and the second runs ok.

I've searched through Books Online for any clue but i haven't really found anything useful.

Does anyone have any clue about this?

Permissions and transaction isolation levels are not an issue here.

-- just to make sure it doesn't already exist IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL BEGIN SELECT 'DROP Temporary table' DROP TABLE #tempTable END IF OBJECT_ID('normalTable') IS NOT NULL BEGIN SELECT 'DROP Normal table' DROP TABLE normalTable END GO -- THIS FAILS WITH ERROR MESSAGE: Msg 2714, Level 16, State 1, Line 7 There is already an object named '#tempTable' in the database. SELECT 'Temporary table' SELECT * INTO #tempTable FROM [master]..spt_values SELECT * FROM #tempTable DROP TABLE #tempTable SELECT * INTO #tempTable FROM [master]..spt_values SELECT * FROM #tempTable DROP TABLE #tempTable GO -- THIS RUNS OK SELECT 'Normal table' SELECT * INTO normalTable FROM [master]..spt_values SELECT * FROM normalTable DROP TABLE normalTable SELECT * INTO normalTable FROM [master]..spt_values SELECT * FROM normalTable DROP TABLE normalTable

 

I've also tried this on SQL Server 2000 and it behaves the same. Haven't tried it on SQL Server 2008 though.

kick it on DotNetKicks.com

Print | posted on Thursday, August 21, 2008 4:56 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: SQL Server 2005 temporary tables bug, feature or expected behavior?

Well, the # table works if you separate the drop\create's by a GO. I am not sure why though. SQL may not immediately clean up temp drops.
8/21/2008 5:45 PM | Jason Massie
Gravatar

# re: SQL Server 2005 temporary tables bug, feature or expected behavior?

yes it works if you separate then with GO. but for example if you have this in a sproc GO is not an option. let's not dwell on why you'd have it in a sproc :))
8/21/2008 5:47 PM | Mladen
Gravatar

# re: SQL Server 2005 temporary tables bug, feature or expected behavior?

The error seems to be happening during the parse/compile phase. Notice that the SELECT 'Temporary table' is not returned, nor are any rows affected messages. This seems like a bug; I would submit a Connect item if I were you.
8/21/2008 6:40 PM | Adam Machanic
Gravatar

# re: SQL Server 2005 temporary tables bug, feature or expected behavior?

i will submit the connect item. thanx.
8/21/2008 6:47 PM | Mladen
Gravatar

# re: SQL Server 2005 temporary tables bug, feature or expected behavior?

That is interesting. It seems that the initial parse of the code is failing when it sees the temp table created twice in the same batch. This could be a restriction implemented by the engine on temp tables because of clean up issues.
If you put the statements into 2 batches it works fine.
If you add a WAITFOR instead of a batch separator it still won't execute and returns the error message.
8/21/2008 6:56 PM | Simon
Gravatar

# re: SQL Server 2005 temporary tables bug, feature or expected behavior?

Looking in BOL - I found this
"If more than one temporary table is created inside a single stored procedure or batch, they must have different names."

So I guess it's not a bug after all(?). Seems to be by design that temp tables cannot have the same name within a batch.
8/21/2008 7:11 PM | Simon
Gravatar

# re: SQL Server 2005 temporary tables bug, feature or expected behavior?

nice! care to tell me where you found this?
8/21/2008 7:13 PM | Mladen
Gravatar

# re: SQL Server 2005 temporary tables bug, feature or expected behavior?

excelent! thanx. i missed that completly.
8/21/2008 7:53 PM | Mladen
Gravatar

# re: SQL Server 2005 temporary tables bug, feature or expected behavior?

Thanks for sharing, Simon. Weird little restriction. Easy enough to work around, luckily!
8/21/2008 9:07 PM | Adam Machanic
Gravatar

# re: SQL Server 2005 temporary tables bug, feature or expected behavior?

Anything related to this issue?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=108889
8/22/2008 7:16 AM | Peso
Gravatar

# re: SQL Server 2005 temporary tables bug, feature or expected behavior?

You can simplify nd narrow down the sitution down to this

if 1= 1
create table #test (i int)
else
create table #test (j varchar(2))
8/22/2008 7:18 AM | Peso
Gravatar

# re: SQL Server 2005 temporary tables bug, feature or expected behavior?

yes that's true Peso,
but the thing that is weird for me is that the
drop table #tempTable
should make a difference.
oh well...
8/22/2008 11:43 AM | Mladen
Gravatar

# re: SQL Server 2005 temporary tables bug, feature or expected behavior?

I think the reason for normal tables arae working and temporary table are not working are due to scope and/or persistancy.
Local temporary tables are only accessible to you, and normal tables are accessible to all.
Have you tried running the code again, and use global temporary tables, ##Test?

I have a hunch that they will act as temporary tables even if they are accessible to all, but they are not persistent (as well as local temporary tables are not persistent).
8/22/2008 2:43 PM | Peso
Gravatar

# re: SQL Server 2005 temporary tables bug, feature or expected behavior?

global temp tables also error out.
8/22/2008 2:45 PM | Mladen
Gravatar

# re: SQL Server 2005 temporary tables bug, feature or expected behavior?

I agree, it's happening in the pre-compile stage.

Add a delay between the two temp code segments and its more apparent.
WAITFOR DELAY '00:00:05'

I've noticed similar issues in aspx, where javascript comments are looked at by the pre-compiler, which promptly complains about a non-issue, because it doesn't know they are comments.

I think in cases like this, it comes down to someones idea of a performance tweak, intended to imporove the "user experience", at the cost of leaving us scraching our heads, searching for anything remotly related, trying some test cases to make sense of it, and finally mumbling a few choice words once it does.


8/27/2008 3:27 AM | dbDan
Gravatar

# re: SQL Server 2005 temporary tables bug, feature or expected behavior?

I have had a similar issue in the past with 2000. Doing something like :

IF <condition>
SELECT * INTO #Temp
ELSE
SELECT * INTO #Temp

fails to compile with 'there already is an object ...'
8/27/2008 9:59 AM | Stewart
Gravatar

# re: SQL Server 2005 temporary tables bug, feature or expected behavior?

Not sure where the problem is.... I've run the following code on SQL Server 2005 64-bit, and it works correctly every time, whereas I would expect to see the error you encountered:

[code]
SELECT CAST(1 AS int) AS THE_INT
INTO #tempTable

IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL
BEGIN
SELECT 'DROP Temporary table'
DROP TABLE #tempTable
END
IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL
BEGIN
SELECT 'DROP Temporary table'
DROP TABLE #tempTable
END
ELSE
BEGIN
SELECT 'NO DROP NEEDED'
END
[/code]

Steve
(aka smunson)
:):):)
8/27/2008 2:52 PM | Steve
Gravatar

# re: SQL Server 2005 temporary tables bug, feature or expected behavior?

acctually steve there's no reason why your code wouldn't work. it does work just fine.
however i don't see the code's point in regard to my example.
or were you replying to someone else?
8/27/2008 2:56 PM | Mladen
Gravatar

# re: SQL Server 2005 temporary tables bug, feature or expected behavior?


Whenever you create a local temp table, even though you may call it something like "#tempTable", the entry in temdb..sysobjects is actually called something like "#tempTable_____________________________________________________________
_____________________________________________000000008475"
in the name field.

Try this:
USE tempdb
SELECT * FROM sysobjects
WHERE name LIKE '%#%'

By appending a long string of underscores and a numeric identifier, SQL Server can allow different sessions to appear to use the same name for a temp table. Sort of. The problem is in the translation between what *you* call the temp table and what SQL Server calls it in the name field of tempdb..sysobjects.

To workaround this limitation, within a session, you have to query and specify the full name of #tempTable to match the one just created in your session, then delete it.
9/3/2008 7:49 PM | ScrappyLaptop
Gravatar

# re: SQL Server 2005 temporary tables bug, feature or expected behavior?

From the SYBASE Transact-SQL Manual

"Manipulating temporary tables in stored procedures
Stored procedures can reference temporary tables that are created during the current session. Within a stored procedure, you cannot create a temporary table, drop it, and then create a new temporary table with the same name."

9/5/2008 12:38 PM | JohnHorb
Gravatar

# re: SQL Server 2005 temporary tables bug, feature or expected behavior?

I would guess its an optimisation that enables the whole procedure to be compiled at the start of the batch.

Because only you can change temp tables it can do this optimisation, unlike normal tables where it couldn't because someone else may have dropped/created the object during the execution of the procedure and so they have to defer checking until runtime.

Having the procedure compile at the start avoids painful recompilations during the procedure.
1/3/2009 9:55 AM | Simon Sabin
Gravatar

# re: SQL Server 2005 temporary tables bug, feature or expected behavior?

Think it is "just" the compiler, because the temp table is definitely not there at runtime...

To trick it, using global temptable, make it hard for the pre-compile part to complain and see what happens at runtime. For example the following works fine with all the expected messages indicating that the table is not there at runtime. So, it is a bug of sorts, and likely because it is not properly checking for the object (which it cannot do until runtime because it is a temp table and the real name is yet to be determined).

IF OBJECT_ID('tempdb..##tempTable') IS NOT NULL DROP TABLE ##tempTable

SELECT 1 as attempt,* INTO ##tempTable FROM [master]..spt_values
SELECT * FROM ##tempTable
DROP TABLE ##tempTable

IF OBJECT_ID('tempdb..##tempTable') IS NOT NULL SELECT 'Need to DROP' ELSE SELECT 'No Need to DROP'

exec ('SELECT 2 as attempt,* INTO ##tempTable FROM [master]..spt_values')
SELECT * FROM ##tempTable
DROP TABLE ##tempTable


4/27/2009 3:51 AM | Mark Wills
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET