Alter transaction settings and/or output formats for the current session.
Syntax
SET ANSI_DEFAULTS { ON | OFF }
SET ANSI_NULL_DFLT_OFF { ON | OFF }
SET ANSI_WARNINGS { ON | OFF }
SET ARITHABORT { ON | OFF }
SET ARITHIGNORE { ON | OFF }
SET CONTEXT_INFO { binary_str | @binary_var }
SET CURSOR_CLOSE_ON_COMMIT { ON | OFF }
SET DATEFIRST { number | @number_var }
SET DATEFORMAT { format | @format_var }
SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | <numeric-priority> | @deadlock_var | @deadlock_intvar }
SET FIPS_FLAGGER 'level'
SET FMTONLY { ON | OFF }
SET FORCEPLAN { ON | OFF }
SET IDENTITY_INSERT [database. [schema].]table { ON | c }
SET IMPLICIT_TRANSACTIONS { ON | OFF }
SET LANGUAGE { [ N ] 'language' | @language_var }
SET LOCK_TIMEOUT timeout_period_in_ms (-1 = forever)
SET NOCOUNT { ON | OFF } (off will boost performance)
SET NOEXEC { ON | OFF }
SET NUMERIC_ROUNDABORT { ON | OFF }
SET PARSEONLY { ON | OFF }
SET QUERY_GOVERNOR_COST_LIMIT value (default= 0 indefinite)
SET QUOTED_IDENTIFIER { ON | OFF }
SET ROWCOUNT { number | @number_var }
SET SHOWPLAN_ALL { ON | OFF }
SET SHOWPLAN_TEXT { ON | OFF }
SET SHOWPLAN_XML { ON | OFF }
SET STATISTICS IO { ON | OFF }
SET STATISTICS PROFILE { ON | OFF }
SET STATISTICS TIME { ON | OFF }
SET STATISTICS XML { ON | OFF }
SET TEXTSIZE { number }
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET XACT_ABORT { ON | OFF }
Key
number First day of week 1=Monday...7=Sunday (Default)
format Date format e.g. dmy, ymd, ydm, myd, dym, mdy(U.S. English default)
numeric-priority A whole number between -10 and 10
level SQL92 Compliance: ENTRY | FULL | INTERMEDIATE | OFF
Transation Isolation settings:
READ UNCOMMITTED - Statements can read uncommitted data that has been modified by other transactions.
READ COMMITTED (Default) - Statements cannot read uncommitted data that has been modified by other transactions.
REPEATABLE READ - Locks are placed on all data read by each statement in a transaction.
SNAPSHOT - The data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction.
SERIALIZABLE - Block other transactions from updating or inserting any rows that would qualify for any of the statements executed by the current transaction.
The 5 isolations levels above are set on a per transaction basis.
The database option READ_COMMITTED_SNAPSHOT will affect every session/transaction connected to the database. READ_COMMITTED_SNAPSHOT will use row versioning to present each statement with a transactionally consistent snapshot of the data (like the default behaviour in Oracle). The version data is stored in tempdb.
READ_COMMITTED_SNAPSHOT will apply to all SELECT statements even if they are not coded as part of a transaction. This makes it easier to port database applications between Oracle and SQL Server.
When READ_COMMITTED_SNAPSHOT is set to ON the isolation level is automatically set to READ COMMITTED (the default).
With READ_COMMITTED_SNAPSHOT set to OFF, database locks are used as in earlier versions of SQL Server, to block the statement from reading rows modified by other transactions.
SET ANSI_DEFAULTS ON SET LANGUAGE Italian GO DBCC USEROPTIONS GO SET LANGUAGE us_english
"Come on baby, light my fire, Try to set the night on fire” ~ The Doors
ALTER DATABASE Database SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE Database SET READ_COMMITTED_SNAPSHOT ON
.BeginTransaction(IsolationLevel.Serializable)
.BeginTransaction(IsolationLevel.ReadUncommitted)
.BeginTransaction(IsolationLevel.ReadCommitted)
sp_configure
sys.syslanguages