You can use certain data definition language commands in transactions by setting the ddl in tran database option to true. If ddl in tran is true in a particular database, you can issue commands such as create table, grant, and alter table inside transactions in that database. If ddl in tran is true in the model database, you can issue the commands inside transactions in all databases created after ddl in tran was set to true in model. To check the current settings of ddl in tran, use sp_helpdb.
WARNING! Be careful when using data definition commands. The only scenario in which using data definition language commands inside transactions is justified is in create schema. Data definition language commands hold locks on system tables such as sysobjects. If you use data definition language commands inside transactions, keep the transactions short.
Avoid using data definition language commands on tempdb within transactions; doing so can slow performance to a halt. Always leave ddl in tran set to false in tempdb.
To set ddl in tran to true, enter:
sp_dboption database_name,"ddl in tran", true
Then execute the checkpoint command in that database.
The first parameter specifies the name of the database in which to set the option. You must be using the master database to execute sp_dboption. Any user can execute sp_dboption with no parameters to display the current option settings. To set options, however, you must be either a System Administrator or the Database Owner.
The following commands are allowed inside a transaction only if the ddl in tran option to sp_dboption is set to true:
alter table (you cannot issue the alter table command with a partition or unpartition clause within a user-defined transaction) |
create default create index create procedure create rule create schema create table create trigger create view |
drop default drop index drop procedure drop rule drop table drop trigger drop view |
grant revoke |
System procedures that change the master database or create temporary tables cannot be used inside transactions.
Never use the following commands inside a transaction:
alter database alter table...partition alter table...unpartition create database |
disk init dump database dump transaction drop database |
load transaction load database reconfigure |
select into update statistics truncate table |
