Why is CREATE TABLE command not working in a user-defined transaction?

I apologise in advance for this stupid question, but I cannot figure why CREATE TABLE is not rolled back in the code shown below. I know that CREATE DATABASE, CREATE FULLTEXT CATALO开发者_StackOverflow中文版G, CREATE FULLTEXT INDEX cannot be specified witnin user-defined transaction. Please note that Tables folder within SSMS gets locked whilst executing this code.

BEGIN TRANSACTION T1
CREATE TABLE temp
(
chisla char(1)
)

SELECT count(chisla) AS Count, chisla AS My_Numbers
FROM temp
--GROUP BY chisla
ORDER BY chisla

drop table temp
COMMIT TRANSACTION T1
GO

---

You didn't tell it to rollback. JNK shows how the try Catch shoudl be done to rollback a transacation in case of a trappable error. However in this case with incorrect SQL, it wouldn't rollback in any event because it is a non-trappable error. You must have correct syntax in the SQl for transactions to work correctly which is one reason why dynamic SQl can be very dangerous as it is impossible to fully test.

It is a bad practice to be creating tables on the fly like this anyway. If you want something temporaily, use a temp table or table variable, do not create a permanent table that you expect to rollback the creating of if the action fails.

---

I think you want a TRY...CATCH Block. There's a very nice explanation here on msdn.

For a quick example, though:

BEGIN TRY
   BEGIN TRANSACTION
   ...your code...
   COMMIT TRANSACTION
END TRY
BEGIN CATCH
   ...error reporting code here...
   ROLLBACK TRANSACTION
END CATCH;