SQL Server SET QUOTED_IDENTIFIER and SET ANSI_PADDING Proper Usage Examples

By: Bhavesh Patel   |   Updated: 2020-03-19   |   Comments   |   Related: > TSQL

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.

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.

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:

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.

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.

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

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

When quoted identifier is off this works.

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

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

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.

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.

• More about SET ANSI DEFAULTS
• Filter indexes in SQL Server
• Specifying computed columns in SQL Server
• Creating indexed views in SQL Server

About the author

Bhavesh Patel is a SQL Server database professional with 10+ years of experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips