Drop table, then cannot recreate table with the same name

Question

I first drop a table in SQL Server 2008 (after that it shows the message that the command was executed sucessfully).

I then tried to create a table with the same name, and it showed me an error.

After closing the SSMS window and re opening it it tried to create the table with the same name again and it succeeded.

What is going on?

May be some caching issuse. did you commited your transaction? — rekire, May 11 '12 at 11:31
It may be the possibility if any of the transaction is started and not commited. — Romil Kumar Jain, May 11 '12 at 11:40
You have to post your code for us to be able to help. As others already pointed out, did you start a transaction? Are you using a different schema? others? — Lieven Keersmaekers, May 11 '12 at 11:41

score 3 · Accepted Answer · answered May 11 '12 at 11:49

You can't drop and create the same table in the same batch in sql server see MSDN

Their examples use GO to break up the two commands. Semi colon might work,

Drop Table ...; Create Table ,,,;

as might

Begin Transaction
Drop Table...
Commit Transaction
Create Table

Or of course splitting it up into two commands, which is what GO does in SQL server manager's query window.

If you do split it up, it might be wise to check whether the table exists before trying to drop it, and that it doesn't before trying to create it.

1 Answers1