Ddl Commands Are Autocommit In Sql Server, What Does It Mean?

at most pages I have read that 'DDL commands have AutoCommit in SQL Server', if I am not wrong this statement simply means that we don't need explicit commit command for DDL comman

Solution 1:

Autocommit Transactions:

A connection to an instance of the Database Engine operates in autocommit mode until a BEGIN TRANSACTION statement starts...

So, your second example doesn't apply. Further down:

In autocommit mode, it sometimes appears as if an instance of the Database Engine has rolled back an entire batch instead of just one SQL statement. This happens if the error encountered is a compile error, not a run-time error. A compile error prevents the Database Engine from building an execution plan, so nothing in the batch is executed.

Which is what your first example deals with.

So, neither one is actually dealing with Autocommit transactions.

---

So, lets take a statement like:

AlterTABLE EMP Add Age INT;

If you have an open connection in Autocommit mode, execute the above, and it completes without errors, then you will find that this connection has no open transactions, and the changes are visible to any other connection immediately.

If you have an open connection in Implicit Transactions mode, execute the above, and it completes without errors, then you will find that this connection has an open transaction. Other connections will be blocked on any operations that require a schema lock on EMP, until you execute either COMMIT or ROLLBACK.

If you have an open connection, in which you have executed BEGIN TRANSACTION, execute the above, and it completes without errors - then you'll be in the same situation as for Implicit Transactions. However, having COMMITed or ROLLBACKed, your connection will revert to either Autocommit mode or Implicit Transactions mode (whichever was active before the call to BEGIN TRANSACTION).

Solution 2:

If you have a begin tran explicitly like in your second example, then you have to commit it. (you can roll back as well)

If you dont specify it explicity like in your first example then it is autocommit.

autocommit is the default mode in sql server, which can be turned off if requried

Solution 3:

If you add batch in your code it will work

AlterTABLE EMP Add Age INT;
go
UPDATE EMP SET Age=20;