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.
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.