Will SQL Server record the changes if we don't specify transaction keywords?

If we don't specify Begin Trans, Commit Trans, etc... and just use a simple SQL statement like this:

delete from myTable where id = 1 

inside the stored procedure,

1. will SQL Server write the changes to the transaction log? If yes then how can we use them while there is no Begin Trans keyword specified (we can't use RollBack).

2. When we write stored procedures is it a good practice to always use the transaction keywords or is it better to use them when they seem to be appropria开发者_开发问答te (for example when we need to rollback a change)?

edit: thank u guys. but how about the second part of my question 1?? how do i recover my data by using the transaction log??

By default SQL Server operates in auto commit mode meaning each statement is automatically in its own transaction and gets committed automatically and written to the transaction log (or rolled back automatically in case of error). Once a transaction is committed it cannot be rolled back and you would need to roll forward from the last backup to rectify any mistakes (subject to a last backup being available and recovery model).

You can alter this behaviour by using set implicit_transactions on after which nothing is committed until you issue a commit command.

With regard to your question about stored procedures if you have multiple statements that you need treated atomically you would need explicit transaction control statements otherwise it is optional.

SQL Server always and only works with the transaction log available and enabled, there is by design no way to tell SQL Server to do not use it.

this does not exclude that in some cases you have certain commands or options to commands which allow you to tell SQL Server to minimize the log writing, for example a TRUNCATE differs from a DELETE because data is removed and won't be logged in the log. In other cases you can specify the log level for some bulk commands to minimize the logging.

Said so, SQL Server does commit immediately if you execute a command in the Management Studio or from code and you do not specify any transaction (Begin, Commit or rollback).

I am not much a SQL guy but I know there is the concept of AutoCommit which could be also changed or set to a different level than the default one.

your delete above will apply the changes immediately and info will be written in the log.

In general in stored procedures we do not inject BEGIN and COMMIT or ROLLBACK within the stored procedure body itself but we use BeginTrans/Commit or Rollback from the C# DAL.

there are also other ways to do, if inside the same stored proc you touch multiple tables it could make sense to have a local transaction in there, but would also work to do it from code.