ANSI_WARNINGS

SET ANSI_WARNINGS { ON | OFF }

• When set to 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 set to OFF, no warning is issued.

• When set to ON, the divide-by-zero and arithmetic overflow errors cause the statement to be rolled back and an error message is generated. 
• When set to OFF, the divide-by-zero and arithmetic overflow errors cause null values to be returned.

Notes:

1. SET ANSI_WARNINGS must be ON when you are creating or manipulating indexes on computed columns or indexed views.
2. ANSI_WARNINGS should be set to ON for executing distributed queries.

http://msdn.microsoft.com/en-us/library/ms190368.aspx

ANSI_NULLS

SET ANSI_NULLS { ON | OFF }

ANSI_NULLS is a definition that deal with the Equals (=) and Not Equal To (<>) comparison operators when they are used with null values.

• When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.
• When SET ANSI_NULLS is OFF, a SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns the rows that have nonnull values in the column. Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL.

ANSI_NULLS definition was created in order to follow the ISO / SQL-92 rules.

http://msdn.microsoft.com/en-us/library/ms188048.aspx

failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'

Error message:

DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

Solution:

Change definition of QUOTED_IDENTIFIER (ON<-->OFF).

Read about QUOTED_IDENTIFIER in:

http://copypastenet.blogspot.com/2011/05/quotedidentifier.html

QUOTED_IDENTIFIER

SET QUOTED_IDENTIFIER { ON | OFF }

QUOTED_IDENTIFIER is a definition that deal with quotation mark delimiting identifiers.

• When SET QUOTED_IDENTIFIER is ON (default), identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. 
• When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers. Literals can be delimited by either single or double quotation marks.

Acording to the T-SQL ruels, literals must be delimited by single quotation marks:

Select "something"  -- error

Select 'something'  -- ok

, like when QUOTED_IDENTIFIER is ON.

But, in order to follow the ISO / SQL-92 rules regarding quotation mark delimiting identifiers and literal strings, we can set QUOTED_IDENTIFIER to OFF, and then:

Select "something"  -- ok

Select 'something'  -- ok

In the most of the cases, we will work according to the T-SQL ruels (QUOTED_IDENTIFIER ON).

We will set it to OFF when we will work agginst other systems, something that QUOTED_IDENTIFIER ON does not support or when we will replace existing systems to SQL Server, etc.

http://msdn.microsoft.com/en-us/library/ms174393.aspx

Unicode casting language errors

There are some errors that we get when casting unicode string to not-unicode, or vice versa.

Examples:

• Casting from nvarchar to varchar, char to nchar, etc.
• Filter unicode text (string in any not-English language) in not-unicode columns.

In most of the cases, to fix those errors it's enough to add N before the string (in order to make it unicode) or to fix in the stored code the casting (if in the code a parameter is define as varchar when the relevant column is nvarchar - fix the parameter declaration).

TYPE_NAME

TYPE_NAME(type_id)

Returns the unqualified type name of a specified type ID.

http://msdn.microsoft.com/en-us/library/ms189750.aspx

CHARINDEX()

CHARINDEX ( expression1 ,expression2 [ , start_location ] )

• expression1 = the character expression that contains the sequence to be found. (varchar)
• expression2 = the character expression to be searched. (varchar)
• start_location = Is an expression at which the search starts. (integer/bigint, optional)

CHARINDEX searches expression2 for expression1 and returns its starting position if found. 

If expression1 is not found within expression2, CHARINDEX returns 0.

The search starts at start_location (if specified).

CHARINDEX is used to check if one string exists in another string.

Notes:

1. expression1 is limited to 8000 characters.
2. If start_location is not specified, is a negative number, or is 0, the search starts at the beginning of expression2.
3. If either expression1 or expression2 is of a Unicode data type (nvarchar or nchar) and the other is not, the other is converted to a Unicode data type.
4. If either expression1 or expression2 is NULL, CHARINDEX returns NULL when the database compatibility level is 70 or higher. If the database compatibility level is 65 or lower, CHARINDEX returns NULL only when both expression1 and expression2 are NULL.

http://msdn.microsoft.com/en-us/library/ms186323.aspx