Transactions

Question 1

What does ACID Stand for

Answer 1

Atomicity (all or nothing), Consistency (correct), Isolation (no interference iwht others), Durability (permanent)

Question 2

What are the 3 types of transactions

Answer 2

AutoCommit, Explicit and Implicit

Question 3

What is the default transaction mode for SQL Server

Answer 3

AutoCommit

Question 4

How does AutoCommit work

Answer 4

When you run a single UPDATE statement and implied transaction is added

Question 5

How could you check if a trasaction has an error

Answer 5

if @error != 0
 begin 
   rollback tran
   return
end

Question 6

Which types of commands cannot be inside of a transaction

Answer 6

ALTER TABLE, DROP DATABASE i.e. long running commands

Question 7

What does a savepoint in a transaction do?

Answer 7

sets a marker you can roll back to, but the remainder is commited

Question 8

How many active transactions can you have per user

Answer 8

one

Question 9

How can you tell how many transactions are open

Answer 9

SELECT @@trancount

Question 10

How do you turn on implicit transactions

Answer 10

SET IMPLICIT_TRANSACTIONS ON

Question 11

When would you likely use implicit transactions

Answer 11

When porting code from another db such as Oracle that uses implicit trans

Question 12

How is a batch denoted in SQL

Answer 12

; or GO (SSMS only not T-SQL)

Question 13

What happens if a transaction in a batch fails

Answer 13

The batch continues

Question 14

Can transactions span multiple batches

Answer 14

yes, but it’s a bad idea

Question 15

Best practice for stored procedures and tranactions

Answer 15

no nesting i.e. stored proc calling nested proce with tran

Question 16

What types of locks are there?

Answer 16

exclusive (data mod) shared or read

Question 17

What does the WITH (NO LOCK) hint do?

Answer 17

Performs “dirty” data with a “READ Uncommited” transaction isolation level

Question 18

What is the default tran isolation level for sql server

Answer 18

READ COMMITTED