DDL in a transaction works in SQL Server: CREATE, ALTER and DROP roll back like any other change. This post proves it with a small demo, lists the statements that are refused inside a transaction, and shows which locks an open DDL transaction holds and who has to wait for them.
Dirty Read with NOLOCK – SQL in Sixty Seconds #110
Recently I noticed that my client has been using the WITH NOLOCK hint with their queries and expecting that it will improve the queries performance.
Error 41368: Memory-Optimized Tables and Transactions
Error 41368 appears when an explicit transaction reads a memory-optimized table at the read committed level. This post reproduces the error, fixes it with a table hint or a database option, and shows the two related errors that the fixes do not cure. It also answers what the option changes for other tables.
MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT: How to Check It Is On
MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT is a database option, so DBCC USEROPTIONS never shows it. This post reads the option from sys.databases and from DATABASEPROPERTYEX, turns it on in a demo database and finds every database that has it. It also shows a guarded script you can reuse in deployments.
SQL SERVER – Applying NOLOCK to Every Single Table in Select Statement – SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
READ UNCOMMITTED changes session read isolation without adding NOLOCK to every table. A workshop procedure made the distinction useful.
SQL SERVER – How to Know Transaction Isolation Level for Each Session?
An outsourced app had teams using different coding standards, and performance suffered. Here is how to find the transaction isolation level of each session.
SQL SERVER – Difference Between Read Committed Snapshot and Snapshot Isolation Level
Read Committed Snapshot and Snapshot Isolation level sound alike but work differently. I explain the difference and show the ALTER DATABASE command for each.