Declared Variable has NULL as Default Value!

When we declare a variable, by default it has NULL as its default value. So, need to be careful when checking the variable's value.

-- similar result for CHAR
DECLARE @varString VARCHAR(20)	
SELECT @varString
-- result is NULL

-- similar result for DECIMAL, FLOAT and DATETIME
DECLARE @varNumber INTEGER
SELECT @varNumber
-- result is NULL

Dropping a Function if Exists in Database

IF EXISTS (
    SELECT * FROM sysobjects WHERE id = object_id(N'function_name') 
    AND xtype IN (N'FN', N'IF', N'TF')
)
    DROP FUNCTION function_name
GO

or

IF object_id(N'function_name', N'FN') IS NOT NULL
    DROP FUNCTION function_name
GO

NOT IN Clause and NULL Values

When ANSI_NULLS setting is ON, be careful when using NOT IN clause if any of the values listed for the clause has NULL value. The scripts below will explain:

create table Table1 (x integer)
insert into Table1 Values (1)
insert into Table1 Values (2)
insert into Table1 Values (3)
insert into Table1 Values (4)

create table Table2 (x integer)
insert into Table2 Values (1)
insert into Table2 Values (Null)
insert into Table2 Values (2) 
insert into Table2 Values (5)

create table Table3 (x integer)
insert into Table3 Values (Null)
insert into Table3 Values (Null)
insert into Table3 Values (Null)

-- These scripts will not return any result (it would if ANSI_NULLS is OFF):
select * from Table1
where x NOT IN (select x from Table2)

select * from Table1 where x NOT IN (select x from Table3)


-- However, these ones are fine:
select * from Table1
where x IN (select x from Table2)

select * from Table1
where x IN (select x from Table3)


-- This one is also fine:
select * from Table2
where x NOT IN (select x from Table1)
-- (returns '5', however if ANSI_NULLS is OFF this would return 'NULL' and '5')

SET ANSI_NULLS ON/OFF

When SET ANSI_NULLS is ON, any kind of comparisons against a null value evaluate to UNKNOWN. This is the ISO standard. In this case, any comparison against a NULL value must use IS NULL or IS NOT NULL to return TRUE/FALSE value.

When SET ANSI_NULLS is OFF, a NULL value can be compared against another NULL value with usual comparison operator ( '=' or '<>' ).

For a script to work as intended, regardless of the ANSI_NULLS database option or the setting of SET ANSI_NULLS, use IS NULL and IS NOT NULL in comparisons that might contain null values.

SET ANSI_NULLS must also be ON when you are creating or changing indexes on computed columns or indexed views. If SET ANSI_NULLS is OFF, any CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. Also, when you execute a SELECT statement, if SET ANSI_NULLS is OFF, SQL Server will ignore the index values on computed columns or views and resolve the select operation as if there were no such indexes on the tables or views.

When SET ANSI_DEFAULTS is ON, SET ANSI_NULLS is enabled.

-- Create table t1 and insert values.
CREATE TABLE t1 (a INT NULL)
INSERT INTO t1 values (NULL)
INSERT INTO t1 values (0)
INSERT INTO t1 values (1)
GO


-- SET ANSI_NULLS to ON and test.
PRINT 'Testing ANSI_NULLS ON'
SET ANSI_NULLS ON
GO
DECLARE @varname int
SELECT @varname = NULL

-- returns nothing
SELECT * FROM t1 
WHERE a = @varname

-- returns nothing
SELECT * FROM t1 
WHERE a <> @varname

-- returns a row (NULL)
SELECT * FROM t1 
WHERE a IS NULL
GO


-- SET ANSI_NULLS to OFF and test.
PRINT 'Testing SET ANSI_NULLS OFF'
SET ANSI_NULLS OFF
GO
DECLARE @varname int
SELECT @varname = NULL

-- returns a row (NULL)
SELECT * FROM t1 
WHERE a = @varname

-- returns rows (0,1)
SELECT * FROM t1 
WHERE a <> @varname

-- returns a row (NULL)
SELECT * FROM t1 
WHERE a IS NULL
GO


-- Drop table t1.
DROP TABLE t1

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

SET QUOTED_IDENTIFIER ON/OFF

When this is set to 'ON', any string enclosed with double quotes ( “ ) is treated as a T-SQL Identifier (such as table name, procedure name or column name) and the T-SQL rules for naming identifiers will not apply to it. To define a normal string literal, enclose it with single quotes ( ' ).

When this is set to 'OFF', any string enclosed with either single quotes or double quotes will be treated as a literal.

The default behavior is 'ON' in any database.

Example:

SET QUOTED_IDENTIFIER OFF
GO
-- An attempt to create a table with a reserved keyword as a name should fail.
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL);
GO

SET QUOTED_IDENTIFIER ON;
GO

-- Will succeed.
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL);
GO

SELECT "identity","order" 
FROM "select"
ORDER BY "order";
GO

Reference:
http://ranjithk.com/2010/01/10/understanding-set-quoted_identifier-onoff/

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