SET statements in SQL Server

SET DATEFIRST

Sets the first day of the week to a number from 1 through 7.

The following example displays the day of the week for a date value and shows the effects of changing the DATEFIRST setting.

============================================================================

-- SET DATEFIRST to U.S. English default value of 7.

SET DATEFIRST 7;

SELECT CAST('1999-1-1' AS datetime2) AS SelectDate

    ,DATEPART(dw, '1999-1-1') AS DayOfWeek;

-- January 1, 1999 is a Friday. Because the U.S. English default

-- specifies Sunday as the first day of the week, DATEPART of 1999-1-1

-- (Friday) yields a value of 6, because Friday is the sixth day of the

-- week when you start with Sunday as day 1.

SET DATEFIRST 3;

-- Because Wednesday is now considered the first day of the week,

-- DATEPART now shows that 1999-1-1 (a Friday) is the third day of the

-- week. The following DATEPART function should return a value of 3.

SELECT CAST('1999-1-1' AS datetime2) AS SelectDate

    ,DATEPART(dw, '1999-1-1') AS DayOfWeek;

GO

============================================================================

SET DATEFORMAT

Sets the order of the month, day, and year date parts for interpreting date, smalldatetime, datetime, datetime2 and datetimeoffset character strings.

The DATEFORMAT ydm is not supported for date, datetime2 and datetimeoffset data types.

The effect of the DATEFORMAT setting on the interpretation of character strings might be different for datetime and smalldatetime values than for date, datetime2 and datetimeoffset values, depending on the string format. This setting affects the interpretation of character strings as they are converted to date values for storage in the database. It does not affect the display of date data type values that are stored in the database or the storage format.

Some character strings formats, for example ISO 8601, are interpreted independently of the DATEFORMAT setting.

The setting of SET DATEFORMAT is set at execute or run time and not at parse time.

SET DATEFORMAT overrides the implicit date format setting of SET LANGUAGE.

============================================================================

-- Set date format to day/month/year.

SET DATEFORMAT dmy;

GO

DECLARE @datevar datetime2 = '31/12/2008 09:01:01.1234567';

SELECT @datevar;

GO

-- Result: 2008-12-31 09:01:01.123

SET DATEFORMAT dmy;

GO

DECLARE @datevar datetime2 = '12/31/2008 09:01:01.1234567';

SELECT @datevar;

GO

-- Result: Msg 241: Conversion failed when converting date and/or time -- from character string.

GO

============================================================================

SET CONCAT_NULL_YIELDS_NULL

Controls whether concatenation results are treated as null or empty string values.

When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result. For example, SELECT 'abc' + NULL yields NULL. When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself (the null value is treated as an empty string). For example, SELECT 'abc' + NULL yields abc.

If SET CONCAT_NULL_YIELDS is not specified, the setting of the CONCAT_NULL_YIELDS_NULL database option applies.

The setting of SET CONCAT_NULL_YIELDS_NULL is set at execute or run time and not at parse time.

SET CONCAT_NULL_YIELDS_NULL must be ON when you are creating or changing indexes on computed columns or indexed views. If SET CONCAT_NULL_YIELDS_NULL is OFF, any CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. For more information about required SET option settings with indexed views and indexes on computed columns, see "Considerations When You Use the SET Statements" in SET Statements (Transact-SQL).

When CONCAT_NULL_YIELDS_NULL is set to OFF, string concatenation across server boundaries cannot occur.

==================================================================

PRINT 'Setting CONCAT_NULL_YIELDS_NULL ON';

GO

-- SET CONCAT_NULL_YIELDS_NULL ON and testing.

SET CONCAT_NULL_YIELDS_NULL ON;

GO

SELECT 'abc' + NULL ;

GO

-- SET CONCAT_NULL_YIELDS_NULL OFF and testing.

SET CONCAT_NULL_YIELDS_NULL OFF;

GO

SELECT 'abc' + NULL;

GO

==================================================================

SET CURSOR_CLOSE_ON_COMMIT 

Controls the behavior of the Transact-SQL COMMIT TRANSACTION statement. The default value for this setting is OFF. This means that the server will not close cursors when you commit a transaction.

When SET CURSOR_CLOSE_ON_COMMIT is OFF, a ROLLBACK statement closes only open asynchronous cursors that are not fully populated. STATIC or INSENSITIVE cursors that were opened after modifications were made will no longer reflect the state of the data if the modifications are rolled back.

SET CURSOR_CLOSE_ON_COMMIT controls the same behavior as the CURSOR_CLOSE_ON_COMMIT database option. If CURSOR_CLOSE_ON_COMMIT is set to ON or OFF, that setting is used on the connection. If SET CURSOR_CLOSE_ON_COMMIT has not been specified, the value in the is_cursor_close_on_commit_on column in the sys.databases catalog view applies.

The SQL Server Native Client OLE DB Provider for SQL Server and the SQL Server Native Client ODBC driver both set CURSOR_CLOSE_ON_COMMIT to OFF when they connect. DB-Library does not automatically set the CURSOR_CLOSE_ON_COMMIT value.

When SET ANSI_DEFAULTS is ON, SET CURSOR_CLOSE_ON_COMMIT is enabled.

The setting of SET CURSOR_CLOSE_ON_COMMIT is set at execute or run time and not at parse time.

===================================================================

-- SET CURSOR_CLOSE_ON_COMMIT

-------------------------------------------------------------------------------

SET NOCOUNT ON

CREATE TABLE t1 (

   a INT

)

GO

INSERT INTO t1

VALUES (1)

INSERT INTO t1

VALUES (2)

GO

PRINT '-- SET CURSOR_CLOSE_ON_COMMIT ON'

GO

SET CURSOR_CLOSE_ON_COMMIT ON

GO

PRINT '-- BEGIN TRAN'

BEGIN TRAN

PRINT '-- Declare and open cursor'

DECLARE testcursor CURSOR FOR

SELECT a

FROM t1

OPEN testcursor

PRINT '-- Commit tran'

COMMIT TRAN

PRINT '-- Try to use cursor'

FETCH NEXT FROM testcursor

CLOSE testcursor

DEALLOCATE testcursor

GO

PRINT '-- SET CURSOR_CLOSE_ON_COMMIT OFF'

GO

SET CURSOR_CLOSE_ON_COMMIT OFF

GO

PRINT '-- BEGIN TRAN'

BEGIN TRAN

PRINT '-- Declare and open cursor'

DECLARE testcursor CURSOR FOR

SELECT a

FROM t1

OPEN testcursor

PRINT '-- Commit tran'

COMMIT TRAN

PRINT '-- Try to use cursor'

FETCH NEXT FROM testcursor

CLOSE testcursor

DEALLOCATE testcursor

GO

DROP TABLE t1;

GO

==================================================================

SET IDENTITY_INSERT 

Allows explicit values to be inserted into the identity column of a table.

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, SQL Server returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

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.

The setting of SET IDENTITY_INSERT is set at execute or run time and not at parse time.

==================================================================

USE AdventureWorks2012;

GO

-- Create tool table.

CREATE TABLE dbo.Tool(

   ID INT IDENTITY NOT NULL PRIMARY KEY,

   Name VARCHAR(40) NOT NULL

)

GO

-- Inserting values into products table.

INSERT INTO dbo.Tool(Name) VALUES ('Screwdriver')

INSERT INTO dbo.Tool(Name) VALUES ('Hammer')

INSERT INTO dbo.Tool(Name) VALUES ('Saw')

INSERT INTO dbo.Tool(Name) VALUES ('Shovel')

GO

-- Create a gap in the identity values.

DELETE dbo.Tool

WHERE Name = 'Saw'

GO

SELECT *

FROM dbo.Tool

GO

-- Try to insert an explicit ID value of 3;

-- should return a warning.

INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel')

GO

-- SET IDENTITY_INSERT to ON.

SET IDENTITY_INSERT dbo.Tool ON

GO

-- Try to insert an explicit ID value of 3.

INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel')

GO

SELECT *

FROM dbo.Tool

GO

-- Drop products table.

DROP TABLE dbo.Tool

GO

============================================================================

SET ANSI_DEFAULTS

Controls a group of SQL Server settings that collectively specify some ISO standard behavior.

SET ANSI_DEFAULTS is a server-side setting that the client does not

modify. The client manages its own settings. By default, these settings are the

opposite of the server setting. Users should not modify the server setting. To

change client the behavior, users should use the SQL_COPT_SS_PRESERVE_CURSORS.

For more information, see SQLSetConnectAttr.

When enabled (ON), this option enables the following ISO settings:

SET ANSI_NULLS	SET CURSOR_CLOSE_ON_COMMIT
SET ANSI_NULL_DFLT_ON	SET IMPLICIT_TRANSACTIONS
SET ANSI_PADDING	SET QUOTED_IDENTIFIER
SET ANSI_WARNINGS

Together, these ISO standard SET options define the query processing

environment for the duration of the work session of the user, a running

trigger, or a stored procedure. However, these SET options do not include all

the options required to comply with the ISO standard.

When dealing with indexes on computed columns and indexed views, four of

these defaults (ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, and QUOTED_IDENTIFIER)

must be set to ON. These defaults are among seven SET options that must be

assigned the required values when you are creating and changing indexes on

computed columns and indexed views. The other SET options are ARITHABORT (ON),

CONCAT_NULL_YIELDS_NULL (ON), and NUMERIC_ROUNDABORT (OFF). For more

information about the required SET option settings with indexed views and

indexes on computed columns, see "Considerations When You Use the SET

Statements" in SET Statements (Transact-SQL).

The SQL Server Native Client ODBC driver and SQL Server Native

Client OLE DB Provider for SQL Server automatically set ANSI_DEFAULTS to ON

when connecting. The driver and Provider then set CURSOR_CLOSE_ON_COMMIT and

IMPLICIT_TRANSACTIONS to OFF. The OFF settings for SET CURSOR_CLOSE_ON_COMMIT

and SET IMPLICIT_TRANSACTIONS can be configured in ODBC data sources, in ODBC

connection attributes, or in OLE DB connection properties that are set in the

application before connecting to SQL Server. The default for SET ANSI_DEFAULTS

is OFF for connections from DB-Library applications.

When SET ANSI_DEFAULTS is issued, SET QUOTED_IDENTIFIER is set at parse

time, and the following options are set at execute time:

SET ANSI_NULLS	SET ANSI_WARNINGS
SET ANSI_NULL_DFLT_ON	SET CURSOR_CLOSE_ON_COMMIT
SET ANSI_PADDING	SET IMPLICIT_TRANSACTIONS

=================================================================

-- SET ANSI_DEFAULTS ON.

SET ANSI_DEFAULTS ON

GO

-- Display the current settings.

DBCC USEROPTIONS

GO

-- SET ANSI_DEFAULTS OFF.

SET ANSI_DEFAULTS OFF

GO

==========================================================================

SET ANSI_NULLS

Specifies ISO compliant behavior of the Equals (=) and Not Equal To (<>) comparison operators when they are used with null values in SQL Server 2012.

In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

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, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values incolumn_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.

When SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN. When SET ANSI_NULLS is OFF, comparisons of all data against a null value evaluate to TRUE if the data value is NULL. If SET ANSI_NULLS is not specified, the setting of the ANSI_NULLS option of the current database applies. For more information about the ANSI_NULLS database option, see ALTER DATABASE (Transact-SQL).

SET ANSI_NULLS ON affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal NULL. If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison.

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 should be set to ON for executing distributed queries.

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. SQL Server will return an error that lists all SET options that violate the required values. 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.

ANSI_NULLS is one of seven SET options that must be set to required values when dealing with indexes on computed columns or indexed views. The options ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, and CONCAT_NULL_YIELDS_NULL must also be set to ON, and NUMERIC_ROUNDABORT must be set to OFF.

The SQL Server Native Client ODBC driver and SQL Server Native Client OLE DB Provider for SQL Server automatically set ANSI_NULLS to ON when connecting. This setting can be configured in ODBC data sources, in ODBC connection attributes, or in OLE DB connection properties that are set in the application before connecting to an instance of SQL Server. The default for SET ANSI_NULLS is OFF.

When SET ANSI_DEFAULTS is ON, SET ANSI_NULLS is enabled.

The setting of SET ANSI_NULLS is set at execute or run time and not at parse time.

================================================================

-- Create table t1 and insert values.

CREATE TABLE dbo.t1 (a INT NULL);

INSERT INTO dbo.t1 values (NULL),(0),(1);

GO

-- Print message and perform SELECT statements.

PRINT 'Testing default setting';

DECLARE @varname int;

SET @varname = NULL;

SELECT a

FROM t1

WHERE a = @varname;

SELECT a

FROM t1

WHERE a <> @varname;

SELECT a

FROM t1

WHERE a IS NULL;

GO

-- SET ANSI_NULLS to ON and test.

PRINT 'Testing ANSI_NULLS ON';

SET ANSI_NULLS ON;

GO

DECLARE @varname int;

SET @varname = NULL

SELECT a

FROM t1

WHERE a = @varname;

SELECT a

FROM t1

WHERE a <> @varname;

SELECT a

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;

SET @varname = NULL;

SELECT a

FROM t1

WHERE a = @varname;

SELECT a

FROM t1

WHERE a <> @varname;

SELECT a

FROM t1

WHERE a IS NULL;

GO

-- Drop table t1.

DROP TABLE dbo.t1;

================================================================

SET ANSI_PADDING

Controls the way the column stores values shorter than the defined size of the column, and the way the column stores values that have trailing blanks in char, varchar, binary, and varbinary data.

In a future version of Microsoft SQL Server ANSI_PADDING will always be ON and any applications that explicitly set the option to OFF will produce an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Columns defined with char, varchar, binary,and varbinary data types have a defined size.

This setting affects only the definition of new columns. After the

column is created, SQL Server stores the values based on the setting when the

column was created. Existing columns are not affected by a later change to this

setting.

Note

We recommend that ANSI_PADDING always be set to ON.

The following table shows the effects of the SET ANSI_PADDING setting

when values are inserted into columns with char, varchar, binary,

and varbinary data types.

Setting	char(n) NOT NULL or binary(n) NOT NULL	char(n) NULL or binary(n) NULL	varchar(n) or varbinary(n)
ON	Pad original value (with trailing blanks for char columns and with trailing zeros for binary columns) to the length of the   column.	Follows same rules as for char(n) orbinary(n) NOT NULL when SET ANSI_PADDING is ON.	Trailing blanks in character values inserted into varchar columns are not trimmed. Trailing zeros in binary values inserted into varbinary columns are not trimmed. Values are not padded to the length of the column.
OFF	Pad original value (with trailing blanks for char columns and with trailing zeros for binary columns) to the length of the   column.	Follows same rules as for varchar orvarbinary when SET ANSI_PADDING is OFF.	Trailing blanks in character values inserted into a varchar column are trimmed. Trailing zeros in binary values inserted into a varbinary column are trimmed.
Note
When padded, char columns are padded with blanks, and binary columns are padded with zeros. When trimmed, char columns have the trailing blanks trimmed, and binary columns have the trailing zeros trimmed.

SET ANSI_PADDING must be ON when you are creating or changing indexes on

computed columns or indexed views. For more information about required SET

option settings with indexed views and indexes on computed columns, see

"Considerations When You Use the SET Statements" in SET Statements (Transact-SQL).

The default for SET ANSI_PADDING is ON. The SQL Server Native

Client ODBC driver and SQL Server Native Client OLE DB Provider for SQL

Server automatically set ANSI_PADDING to ON when connecting. This can be

configured in ODBC data sources, in ODBC connection attributes, or OLE DB

connection properties set in the application before connecting. The default for

SET ANSI_PADDING is OFF for connections from DB-Library applications.

The SET ANSI_PADDING setting does not affect the nchar, nvarchar, ntext, text, image,

and large value. They always display the SET ANSI_PADDING ON behavior. This

means trailing spaces and zeros are not trimmed.

When SET ANSI_DEFAULTS is ON, SET ANSI_PADDING is enabled.

The setting of SET ANSI_PADDING is set at execute or run time and not at

parse time.

==============================================

PRINT 'Testing with ANSI_PADDING ON'

SET ANSI_PADDING ON;

GO

CREATE TABLE t1 (

   charcol CHAR(16) NULL,

   varcharcol VARCHAR(16) NULL,

   varbinarycol VARBINARY(8)

);

GO

INSERT INTO t1 VALUES ('No blanks', 'No blanks', 0x00ee);

INSERT INTO t1 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00);

SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',

   varbinarycol

FROM t1;

GO

PRINT 'Testing with ANSI_PADDING OFF';

SET ANSI_PADDING OFF;

GO

CREATE TABLE t2 (

   charcol CHAR(16) NULL,

   varcharcol VARCHAR(16) NULL,

   varbinarycol VARBINARY(8)

);

GO

INSERT INTO t2 VALUES ('No blanks', 'No blanks', 0x00ee);

INSERT INTO t2 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00);

SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',

   varbinarycol

FROM t2;

GO

DROP TABLE t1

DROP TABLE t2

=

SET QUOTED_IDENTIFIER

Causes SQL Server to follow the ISO rules regarding quotation mark delimiting identifiers and literal strings. Identifiers delimited by double quotation marks can be either Transact-SQL reserved keywords or can contain characters not generally allowed by the Transact-SQL syntax rules for identifiers.

When SET QUOTED_IDENTIFIER is ON, 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. For more information, see Database Identifiers. Literals can be delimited by either single or double quotation marks.

When SET QUOTED_IDENTIFIER is ON (default), all strings delimited by double quotation marks are interpreted as object identifiers. Therefore, quoted identifiers do not have to follow the Transact-SQL rules for identifiers. They can be reserved keywords and can include characters not generally allowed in Transact-SQL identifiers. Double quotation marks cannot be used to delimit literal string expressions; single quotation marks must be used to enclose literal strings. If a single quotation mark (') is part of the literal string, it can be represented by two single quotation marks ("). SET QUOTED_IDENTIFIER must be ON when reserved keywords are used for object names in the database.

When SET QUOTED_IDENTIFIER is OFF, literal strings in expressions can be delimited by single or double quotation marks. If a literal string is delimited by double quotation marks, the string can contain embedded single quotation marks, such as apostrophes.

SET QUOTED_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. For more information about required SET option settings with indexed views and indexes on computed columns, see "Considerations When You Use the SET Statements" in SET Statements (Transact-SQL).

The SQL Server Native Client ODBC driver and SQL Server Native Client OLE DB Provider for SQL Server automatically set QUOTED_IDENTIFIER to ON when connecting. This can be configured in ODBC data sources, in ODBC connection attributes, or OLE DB connection properties. The default for SET QUOTED_IDENTIFIER is OFF for connections from DB-Library applications.

When a table is created, the QUOTED IDENTIFIER option is always stored as ON in the table's metadata even if the option is set to OFF when the table is created.

When a stored procedure is created, the SET QUOTED_IDENTIFIER and SET ANSI_NULLS settings are captured and used for subsequent invocations of that stored procedure.

When executed inside a stored procedure, the setting of SET QUOTED_IDENTIFIER is not changed.

When SET ANSI_DEFAULTS is ON, SET QUOTED_IDENTIFIER is enabled.

SET QUOTED_IDENTIFIER also corresponds to the QUOTED_IDENTIFIER setting of ALTER DATABASE. For more information about database settings, see ALTER DATABASE (Transact-SQL).

SET QUOTED_IDENTIFIER is set at parse time. Setting at parse time means that if the SET statement is present in the batch or stored procedure, it takes effect, regardless of whether code execution actually reaches that point; and the SET statement takes effect before any statements are executed.

Using brackets, [ and ], to delimit identifiers is not affected by the QUOTED_IDENTIFIER setting.

A. Using the quoted identifier setting and reserved word object names

The following example shows that the SET QUOTED_IDENTIFIER setting must be ON, and the keywords in table names must be in double quotation marks to create and use objects that have reserved keyword names.

==================================================

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

DROP TABLE "SELECT";
GO

SET QUOTED_IDENTIFIER OFF;
GO

==================================================

B. Using the quoted identifier setting with single and double quotation marks

The following example shows the way single and double quotation marks are used in string expressions with SET QUOTED_IDENTIFIER set to ON and OFF.

==================================================

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

DROP TABLE "SELECT";
GO

SET QUOTED_IDENTIFIER OFF;
GO

=================================================

Here is the result set.

ID String

----------- ------------------------------

1 'Text in single quotes'

2 'Text in single quotes'

3 Text with 2 '' single quotes

4 "Text in double quotes"

5 "Text in double quotes"

6 Text with 2 "" double quotes

7 Text with a single ' quote

SET STATISTICS XML

Causes Microsoft SQL Server to execute Transact-SQL statements and generate detailed information about how the statements were executed in the form of a well-defined XML document.

The setting of SET STATISTICS XML is set at execute or run time and not at parse time.

When SET STATISTICS XML is ON, SQL Server returns execution information for each statement after executing it. After this option is set ON, information about all subsequent Transact-SQL statements is returned until the option is set to OFF. Note that SET STATISTICS XML need not be the only statement in a batch.

SET STATISTICS XML returns output as nvarchar(max) for applications, such as the sqlcmd utility, where the XML output is subsequently used by other tools to display and process the query plan information.

SET STATISTICS XML returns information as a set of XML documents. Each statement after the SET STATISTICS XML ON statement is reflected in the output by a single document. Each document contains the text of the statement, followed by the details of the execution steps. The output shows run-time information such as the costs, accessed indexes, and types of operations performed, join order, the number of times a physical operation is performed, the number of rows each physical operator produced, and more.

The document containing the XML schema for the XML output by SET STATISTICS XML is copied during setup to a local directory on the computer on which Microsoft SQL Server is installed. It can be found on the drive containing the SQL Server installation files, at:

\Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml.xsd

The Showplan Schema can also be found at this Web site.

SET STATISTICS PROFILE and SET STATISTICS XML are counterparts of each other. The former produces textual output; the latter produces XML output. In future versions of SQL Server, new query execution plan information will only be displayed through the SET STATISTICS XML statement, not the SET STATISTICS PROFILE statement.

The two statements that follow use the SET STATISTICS XML settings to show the way SQL Server analyzes and optimizes the use of indexes in queries. The first query uses the Equals (=) comparison operator in the WHERE clause on an indexed column. The second query uses the LIKE operator in the WHERE clause. This forces SQL Server to use a clustered index scan to find the data that satisfies the WHERE clause condition. The values in theEstimateRows and the EstimatedTotalSubtreeCost attributes are smaller for the first indexed query indicating that it was processed much faster and used fewer resources than the nonindexed query.

=================================================

USE AdventureWorks2012;
GO
SET STATISTICS XML ON;
GO
-- First query.
SELECT BusinessEntityID 
FROM HumanResources.Employee
WHERE NationalIDNumber = '509647174';
GO
-- Second query.
SELECT BusinessEntityID, JobTitle 
FROM HumanResources.Employee
WHERE JobTitle LIKE 'Production%';
GO
SET STATISTICS XML OFF;
GO

===================================================

GO to the link mentioned below to know more about SET operations:

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