SQL Server SET QUOTED_IDENTIFIER and SET ANSI_PADDING Proper Usage Examples


By:   |   Updated: 2020-03-19   |   Comments   |   Related: More > T-SQL

Problem

SQL Server's T-SQL code provides SET statements that can change the way a connection processes data. These statements are available in multiple categories such as: date and time settings, locking statements, miscellaneous statements, query execution statements, ISO settings, statistics statements, transaction statements, etc.  Depending on whether these options are on or off, the way your queries are executed could differ.

In this tip I would like to demonstrate the behavior of QUOTED_IDENTIFIER and ANSI_PADDING, because a query could produce different results based on how these SET options are used.

Solution

T-SQL SET statements run at execute time, but there are also SET statements that are executed at parse time, like SET QUOTED_IDENTIFIER, SET PARSEONLY, SET OFFSETS, SET FIPS_FLAGGER.  In this tip we will look at the behavior of SET QUOTED_IDENTIFIER and SET ANSI_PADDING.

Create Test Database

The following script creates database Test_Statements which we will use for this tip.

USE master 
GO
 
CREATE DATABASE Test_Statements 
GO 

USE Test_Statements 
GO 

Setting Defaults for ANSI SETTINGS

In addition to turning these settings on and off for each session, you can also configure the defaults when using SQL Server Management Studio (SSMS) as follows.

We can configure the default values from SQL Server Management Studio by going to Tools > Options and select Query Execution > SQL Server > ANSI. This shows each of the items we can set for new sessions.  We will look at how to do this for individual sessions with the SET commands.

Setting ANSI_DEFAULTS

SQL Server SET QUOTED_IDENTIFIER

This setting is used to determine how quotation marks will be handled.

  • When QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks and literals must be delimited by single quotation marks.
  • When QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all T-SQL rules for identifiers. This allows for literal strings in expressions such as double quotation marks and single quotation marks.

In the code below I am setting QUOTED_IDENTIFIER ON to see what happens.

SET QUOTED_IDENTIFIER ON

SELECT 'Bhavesh's'

This returns an error.

Apostrophes used in a string

I can run this as below using 2 single quotes inside the string and this works.

SET QUOTED_IDENTIFIER ON

SELECT 'Bhavesh''s'

This also works as follows, which shows the quoted identifier doesn't affect this issue.

SET QUOTED_IDENTIFIER OFF

SELECT 'Bhavesh''s'

Now let's try to do the above with double quotes as follows:

SET QUOTED_IDENTIFIER ON

SELECT "Bhavesh's"

This returns the error:

Msg 207, Level 16, State 1, Line 3
Invalid column name 'Bhavesh's'.

But if we run the following with quoted identifier off this works.

SET QUOTED_IDENTIFIER OFF

SELECT "Bhavesh's"

SELECT "Bhavesh """" Bhavesh"

SELECT "Bhavesh '''' Bhavesh"

The results are below.

Quoted Identifier off behavior

Let's look at a couple of other examples, this time using system keywords.

SET QUOTED_IDENTIFIER ON

Create table dbo.DROP
(
 ID INT
)

When executing the above, it raises the following error.

System keyword used in table definition

Below we can see that if we use double quotes along with quoted identifier on this works.

Double Quote used in DDl with particular settings

This is the same if we try to use a keyword in a SELECT statement.

use double qoute as string buider

When quoted identifier is off this works.

Double quote used as string builder

SQL Server SET ANSI_PADDING

ANSI_PADDING controls the way the column stores values shorter than the defined size of the column and the way the column stores a value that has trailing blanks in char, varchar, binary and varbinary data.

  • When ANSI_PADDING is ON then trailing blanks in character values inserted into varchar columns are not trimmed. Trailing zeroes in binary values inserted into varbinary columns are not trimmed. Values are not padded to the length of the column.
  • When ANSI_PADDING is OFF then trailing blanks in character values inserted into a varchar column are trimmed and trailing zeros in binary values inserted into a varbinary columns are trimmed.

This setting affects new column definitions. After the column is created, SQL Server stores the values based on the settings when the column was created. But existing data is not affected by a later change to this setting.

I will create a table and then add a record with setting ANSI_PADDING ON. You can see below that there are extra spaces and trailing zeros.

SET ANSI_PADDING ON;

CREATE TABLE Customer_Data_Paddinig_on
( 
id int primary key identity (1,1),
sapcode int,
Name varchar(400),
mybinary varbinary(15) NULL,
address nvarchar(max)
)

INSERT Customer_Data_Paddinig_on
SELECT 102, 'Bhavesh Patel ',0x00ee00,'Ahmedabad '
GO

SELECT 
   DATALENGTH(Name),
   DATALENGTH(mybinary),
   DATALENGTH(address)
FROM Customer_Data_Paddinig_on
WHERE sapcode = 102
Ansi_padding behavior

Above we can see the lengths of each column are 14, 3 and 20.

I will do the same thing below using ANSI_PADDING OFF.

SET ANSI_PADDING OFF;

CREATE TABLE Customer_Data_Paddinig_on
( 
id int primary key identity (1,1),
sapcode int,
Name varchar(400),
mybinary varbinary(15) NULL,
address nvarchar(max)
)

INSERT Customer_Data_Paddinig_on
SELECT 102, 'Bhavesh Patel ',0x00ee00,'Ahmedabad '
GO

SELECT 
   DATALENGTH(Name),
   DATALENGTH(mybinary),
   DATALENGTH(address)
FROM Customer_Data_Paddinig_on
WHERE sapcode = 102
Ansi_Padding behavior

If we look at these results, we can see I now get 13, 2 and 20.  The values for the varchar and varbinary changed, but the nvarchar stayed the same.

Incorrect settings for QUOTED_IDENTIFIER and ANSI_PADDING

As shown above, both settings are not feasible in every query execution. This means it might be possible that query execution could fail due to incorrect settings.  Also, as shown below there are some exceptions of how these can be used.

Incorrect settings: QUOTED_IDENTIFIER ANSI_PADDING exception

Identity Setting Values for QUOTED_IDENTIFIER and ANSI_PADDING

Below we can see scripts we can use to see the settings for specific sessions connected to SQL Server.

Session Specific Settings

This gets the settings for a specific session.

SELECT QUOTED_IDENTIFIER, ANSI_PADDING FROM sys.dm_exec_sessions where session_id = @@SPID 

Queries to Get Settings from MSDN

DECLARE @QUOTED_IDENTIFIER VARCHAR (3) = 'OFF'; 
IF ( (256 & @@OPTIONS) = 256 ) SET @QUOTED_IDENTIFIER = 'ON'; 
SELECT @QUOTED_IDENTIFIER AS QUOTED_IDENTIFIER; 
  
DECLARE @ANSI_PADDING VARCHAR (3) = 'OFF';  
IF ((16 & @@OPTIONS) = 16) SET @ANSI_PADDING = 'ON';  
SELECT @ANSI_PADDING AS ANSI_PADDING; 

Here are the results.

Next Steps

Here is additional reading related to this and specific settings needed for some of these features.



Last Updated: 2020-03-19


get scripts

next tip button



About the author
MSSQLTips author Bhavesh Patel Bhavesh Patel is a SQL Server database professional with 10+ years of experience.

View all my tips





Comments For This Article





download


Recommended Reading

Rolling up multiple rows into a single row and column for SQL Server data

SQL Server Loop through Table Rows without Cursor

Using MERGE in SQL Server to insert, update and delete at the same time

Cursor in SQL Server

How to use @@ROWCOUNT in SQL Server





get free sql tips
agree to terms


Learn more about SQL Server tools