A list of widely used SQL settings Explained
- ANSI NULLS
- QUOTED_IDENTIFIER
- ARITHMABORT
- ANSI_DEFAULTS
- ANSI_WARNINGS
- DATEFIRST
- DATEFORMAT
- NOCOUNT
- NOEXEC
- IDENTITY_INSERT
- IMPLICITY_TRANSACTIONS
- LANGUAGE
ANSI NULLS
– Syntax
SET ANSI_NULLS {ON | OFF}
– Explanation
The SQL-92 standard requires that an equals (=) or not equal to (<>) comparison against a null value evaluates to FALSE. When SET ANSI_NULLS is ON, a SELECT statement using WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement using WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.
When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the SQL-92 standard. A SELECT statement using WHERE column_name = NULL returns the rows with null values in column_name. A SELECT statement using WHERE column_name <> NULL returns the rows with nonnull values in the column.
ARITHMABORT
– Syntax
SET ARITHABORT { ON | OFF }
– Explanation
If SET ARITHABORT is ON, these error conditions cause the query or batch to terminate. If the errors occur in a transaction, the transaction is rolled back. When an INSERT, DELETE or UPDATE statement encounters an arithmetic error SQL Server inserts or updates a NULL value.
ANSI_DEFAULTS
– Syntax
SET ANSI_DEFAULTS { ON | OFF }
– Explanation
When enabled (ON), this option enables the following SQL-92 settings:
ANSI_NULLS, CURSOR_CLOSE_ON_COMMIT, ANSI_NULL_DFLT_ON, IMPLICIT_TRANSACTIONS, ANSI_PADDING, QUOTED_IDENTIFIER, ANSI_WARNINGS.
ANSI_WARNINGS
– Syntax
SET ANSI_WARNINGS { ON | OFF }
– Explanation
When ON, if null values appear in aggregate functions (such as SUM, AVG, MAX, MIN, STDEV, STDEVP, VAR, VARP, or COUNT) a warning message is generated. When OFF, no warning is issued. When ON, divide-by-zero and arithmetic overflow errors cause the statement to be rolled back and an error message is generated. When OFF, divide-by-zero and arithmetic overflow errors cause null values to be returned.
DATEFIRST
– Syntax
SET DATEFIRST { number | @number_var }
– Explanation
Is an integer indicating the first day of the week, and can be one of these values.
| number | First day of the week is |
| 1 | Monday |
| 2 | Tuesday |
| 3 | Wednesday |
| 4 | Thursday |
| 5 | Friday |
| 6 | Saturday |
| 7 (default, U.S. English) | Sunday |
Use the @@DATEFIRST function to check the current setting of SET DATEFIRST.
The setting of SET DATEFIRST is set at execute or run time and not at parse time.
NOCOUNT
– Syntax
SET NOCOUNT { ON | OFF }
– Explanation
When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned.
NOEXEC
– Syntax
SET NOEXEC { ON | OFF }
– Explanation
When SET NOEXEC is ON, Microsoft® SQL Server™ compiles each batch of Transact-SQL statements but does not execute them. Used to check if script has misspelled errors.
DATEFORMAT
– Syntax
SET DATEFORMAT { format | @format_var }
– Explanation
Is the order of the dateparts.
This example uses different date formats to handle date strings in different formats.
SET DATEFORMAT mdy
GO
DECLARE @datevar datetime
SET @datevar = ’12/31/98′
SELECT @datevar
GO
IDENTITY_INSERT
– Syntax
SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }
– Explanation
When this is set ON you can insert manually values on fields with Auto Incrementation. If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
SET IDENTITY_INSERT products ON
GO
— will succeed only if IDENTITY_INSERT IS ON
INSERT INTO products (id, product) VALUES(3, ‘garden shovel’).
GO
IMPLICITY_TRANSACTIONS
– Syntax
SET IMPLICIT_TRANSACTIONS { ON | OFF }
– Explanation
When a connection is in implicit transaction mode and the connection is not currently in a transaction, executing any of the following statements starts a transaction:
alter table, fetch, revoke, create, grant, select, delete, insert, truncate table, drop, open, update.
LANGUAGE
– Syntax
SET LANGUAGE { [ N ] ‘language’ | @language_var }
[N]’language’ |@language_var
Is the name of the language as stored in syslanguages. This argument can be either Unicode or DBCS converted to Unicode. To specify a language in Unicode, use N’language’. If specified as a variable, the variable must be sysname.
– Explanation
Specifies the language environment for the session. The session language determines the datetime formats and system messages.