SET

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 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 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.

Examples

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

Related commands:

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