In this post I cover ANSI_NULLS and QUOTED_IDENTIFIER, two potentially dangerous SET options that stored procedures inherit from your session when created.
Intro
You might have noticed when scripting out stored procedures in SSMS that it prepends two SET options before the CREATE PROCEDURE statement.
These are:
- ANSI_NULLS
- QUOTED_IDENTIFIER
What ANSI_NULLS and QUOTED_IDENTIFIER do?
ANSI_NULLS
In short, it controls the ANSI SQL compliant handling of NULLs when using equality (=) and inequality (<>) operators in SQL Server.
You can read more about it in this MS docs page.
QUOTED_IDENTIFIER
As per its documentation page, it 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.
Defaults in SSMS
By default, these are all the ANSI/SET options that are set to ON in any session of SSMS:
- ANSI_NULL_DFLT_ON
- ANSI_NULLS
- ANSI_PADDING
- ANSI_WARNINGS
- ARITHABORT
- CONCAT_NULL_YIELDS_NULL
- QUOTED_IDENTIFIER
Other database clients might have other defaults
Using SSMS as the baseline, I compared the SET option values for other clients and even different SQL Server components.
The takeaway here is that:
- SSMS and VSCode’s mssql extension are identical in terms of SET option defaults.
- sqlcmd and SQL Server Agent both have ARITHABORT and QUOTED_IDENTIFIER set to OFF.
- go-sqlcmd, Invoke-DbaQuery, and JDBC (of all things) have ARITHABORT set to OFF.
- startup stored procedures, which would actually be running in a session spawned from SQL Server itself, have ANSI_NULL_DFLT_ON, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, and CONCAT_NULL_YIELDS_NULL set to OFF.
3 of these SET options actually caused some issues in my post about startup stored procedures.
Other client drivers and some ORMs have different defaults, so you’ll need to be mindful of those.
Demo
I’m not going to demo their behavior since this SQLShack post and this one already do that really well.
Instead, I’m going to demo how these two SET options are inherited by a stored procedure from the session that creates it.
Using SSMS, I create the following stored procedure.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | CREATE PROCEDURE [dbo].[sp_test] AS WITH OPTCTE AS (SELECT Options.[id], Options.[Option], ROW_NUMBER() OVER ( PARTITION BY 1 ORDER BY id) AS [bitNum] FROM (VALUES (1,'DISABLE_DEF_CNST_CHK'), (2,'IMPLICIT_TRANSACTIONS'), (4,'CURSOR_CLOSE_ON_COMMIT'), (8,'ANSI_WARNINGS'), (16,'ANSI_PADDING'), (32,'ANSI_NULLS'), (64,'ARITHABORT'), (128,'ARITHIGNORE'), (256,'QUOTED_IDENTIFIER'), (512,'NOCOUNT'), (1024,'ANSI_NULL_DFLT_ON'), (2048,'ANSI_NULL_DFLT_OFF'), (4096,'CONCAT_NULL_YIELDS_NULL'), (8192,'NUMERIC_ROUNDABORT'), (16384,'XACT_ABORT')) AS Options([id], [Option])) SELECT [Option], CASE WHEN ( @@OPTIONS & id ) = id THEN 'ON' ELSE 'OFF' END AS [Session_Setting] FROM OPTCTE ORDER BY 2 DESC; |
This stored procedure just lists the SET options used during its execution.
It’s derived from a query used in PSBlitz to generate the following result as part of the Instance Info report page.
Notice that in the stored procedure creation DDL I haven’t specified any value for the two options, this is because I’m relying on them being inherited from SSMS.
Now, I execute the stored procedure from a normal query editor window.
| 1 | EXEC [dbo].[sp_test]; |
To show how the inheritance works, I execute the stored procedure after setting all the usual ANSI/SET options to OFF first.
| 1 2 3 4 5 6 7 8 9 | SET ANSI_NULL_DFLT_ON OFF; SET ANSI_NULLS OFF; SET ANSI_PADDING OFF; SET ANSI_WARNINGS OFF; SET ARITHABORT OFF; SET CONCAT_NULL_YIELDS_NULL OFF; SET QUOTED_IDENTIFIER OFF; GO EXEC [dbo].[sp_test]; |
Notice that even if ANSI_NULLS and QUOTED_IDENTIFIER where explicitly set to OFF before executing the stored procedure, in the context of the stored procedure they were ON.
This means that whatever your session has configured for ANSI_NULLS and QUOTED_IDENTIFIER will be overridden by the options used by the session that created said stored procedure.
Where is this information stored?
The information is stored in the sys.sql_modules view.
Well, views don’t really store information…. So, it’s actually stored in the sys.sysschobjs$ system base table.
But the sys.sql_modules view allows us, mere mortals, to see that info.
| 1 2 | SELECT * FROM sys.sql_modules WHERE [object_id] = OBJECT_ID(N'sp_test'); |
Why are these considered dangerous SET options for stored procedures?
For starters, they override the SET options used by the sessions executing them.
And if they’re created with those options set to OFF, you can end up running into various errors.
Right out of the documentation:
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.
Furthermore, when dealing with query notifications and/or XML data type methods and/or spatial index operations, as seen in my startup stored procedures post, at least CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING should be set to ON.
What about the SET options that aren’t inherited?
Well, the other 5 SET options should be specified inside your stored procedure, right at the start of your stored procedure.
Using the above example stored procedure, it would look like this:
| 1 2 3 4 5 6 7 8 | ALTER PROCEDURE [dbo].[sp_test] AS SET ANSI_PADDING ON; SET ANSI_WARNINGS ON; SET ARITHABORT ON; SET CONCAT_NULL_YIELDS_NULL ON; SET NUMERIC_ROUNDABORT OFF; /*the rest of your procedure*/ |
This is important because not all database client drivers use the same ANSI/SET defaults, and you might run into stations where code that worked as expected in SSMS might error out when executed from a different client driver or an ORM.
Ask me ho I know…
This leads me to a…
Gotcha with inheritance
At this point you may ask yourself if it wouldn’t be enough to also throw ANSI_NULLS and QUOTED_IDENTIFIER inside the stored procedure and just not care about the session creating the stored proc.
While that’s a valid question, SQL server might have something to say about that.
Demo take 2
Let’s re-create our initial stored procedure, but with a twist.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 | DROP PROCEDURE [dbo].[sp_test]; GO /*using the wrong values for these SET options here*/ SET ANSI_PADDING OFF; SET ANSI_WARNINGS OFF; SET ARITHABORT OFF; SET CONCAT_NULL_YIELDS_NULL OFF; SET NUMERIC_ROUNDABORT ON; SET ANSI_NULLS OFF; SET QUOTED_IDENTIFIER OFF; GO CREATE PROCEDURE [dbo].[sp_test] AS /*using the right values for these SET options here*/ SET ANSI_PADDING ON; SET ANSI_WARNINGS ON; SET ARITHABORT ON; SET CONCAT_NULL_YIELDS_NULL ON; SET NUMERIC_ROUNDABORT OFF; /*don't forget about those pesky inherited ones*/ SET QUOTED_IDENTIFIER ON; SET ANSI_NULLS ON; WITH OPTCTE AS (SELECT Options.[id], Options.[Option], ROW_NUMBER() OVER ( PARTITION BY 1 ORDER BY id) AS [bitNum] FROM (VALUES (1,'DISABLE_DEF_CNST_CHK'), (2,'IMPLICIT_TRANSACTIONS'), (4,'CURSOR_CLOSE_ON_COMMIT'), (8,'ANSI_WARNINGS'), (16,'ANSI_PADDING'), (32,'ANSI_NULLS'), (64,'ARITHABORT'), (128,'ARITHIGNORE'), (256,'QUOTED_IDENTIFIER'), (512,'NOCOUNT'), (1024,'ANSI_NULL_DFLT_ON'), (2048,'ANSI_NULL_DFLT_OFF'), (4096,'CONCAT_NULL_YIELDS_NULL'), (8192,'NUMERIC_ROUNDABORT'), (16384,'XACT_ABORT')) AS Options([id], [Option])) SELECT [Option], CASE WHEN ( @@OPTIONS & id ) = id THEN 'ON' ELSE 'OFF' END AS [Session_Setting] FROM OPTCTE ORDER BY 2 DESC; |
First, I set all the wrong values for the important SET options at the session level.
But inside the stored procedure I use the right values.
Now, the vanilla SSMS session execution:
| 1 | EXEC [dbo].[sp_test]; |
And this is how inheritance messes with you, it even overrides the values for ANSI_NULLS and QUOTED_IDENTIFIER used inside the stored procedure.
Notice how the rest of relevant the SET options are set to ON.
For completeness’s sake, I also try explicitly setting the options at the session level when calling the stored procedure.
| 1 2 3 4 5 6 7 8 9 10 11 | /*wrong values for these options*/ SET ANSI_PADDING OFF; SET ANSI_WARNINGS OFF; SET ARITHABORT OFF; SET CONCAT_NULL_YIELDS_NULL OFF; SET NUMERIC_ROUNDABORT ON; /*right values for these two*/ SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON; GO EXEC [dbo].[sp_test]; |
Notice how the rest of relevant the SET options are still set to ON while ANSI_NULLS and QUOTED_IDENTIFIER are still OFF.
Conclusion
Be mindful of the two dangerous SET options when creating stored procedures and be sure they have the desired value at the session level before creating the stored procedure(s).
Also, don’t let various client drivers or ORMs make decisions for you, instead make sure to explicitly set the appropriate ANSI/SET option values in your stored procedures.
2 comments
I just wanted to say: Thank you for this post. It has helped me to answer my doubts about the necessity of these two SET options before the module CREATE statement inside of my deployment scripts.
Hi Jiri,
Thanks for commenting! I’m glad you found my blog post useful.