Filtered Indexes and CONCAT_NULL_YIELDS_NULL

Another lesson learned at the school of hard knocks…

I recently deployed a filtered index to a table that had a FLAG column.  I showed without a doubt in the test environment that this filtered index would be used by production queries at a much lower cost than without the index.

What I didn’t know was that the developers of the ETL decided to turn off CONCAT_NULL_YIELDS_NULL.

The net result was the first night the ETL ran we got an error message (from the MERGE statement) indicating that CONCAT_NULL_YIELDS_NULL was turned off and was impacting an indexed view.

It is well known, at least by myself, that indexed views have numerous restrictions, and for good reason, including several SET options.

Unfortunately, we didn’t have any indexed views on the database.

After much fussing around, we determined that the root cause of this blockage was the filtered index.

A review of the Filtered Index Guidelines for SQL Server 2008R2 makes no mention of the CONCAT_NULL_YIELDS_NULL restrition.

It is mentioned on the CREATE INDEX MSDN page.

The error that was captured from our ETL logs looked like:

1934 AS ErrorNumber, 16 AS ErrorSeverity, 1 AS ErrorState,  AS ErrorProcedure, 1 AS ErrorLine, MERGE failed because the following SET options have incorrect settings: ‘CONCAT_NULL_YIELDS_NULL’. Verify that SET options are correct for use with indexed view

This was misleading since we did not have any indexed views in the database.

I ran the following code on a local SQL 2014 instance to test the “theory” for the filtered index:

[sourcecode language='sql'  padlinenumbers='true']
CREATE TABLE myTable (	ID	int	identity(1, 1) not null
						, myCharData	char(3) null
						, myFlag		char(1) null );

insert into myTable 
	values (	'abc', 'Y')
			, ('def', 'N')
			, ('ghi', NULL);

create nonclustered index idx_myfilteredindex 
	on	myTable (myFlag)
		where myFlag = 'Y';

-- set compatibility to sql 2008R2;
alter database mySandbox
	set compatibility_level = 100;


update	myTable
set		myCharData = 'xyz'
where	ID = 2;

This proves that the filtered index does not even need to be part of the query to manifest the error.

The error that was presented also included a reference to filtered indexes:

UPDATE failed because the following SET options have incorrect settings: ‘CONCAT_NULL_YIELDS_NULL’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

Since CONCAT_NULL_YIELDS_NULL is advised to not be changed and will be removed in future releases, it is in our own best interest to not change this setting.

Rolling this out of the existing ETL is probably not a short term action item, but we better not be using it on any new releases.

The other take away from this… even the most simple changes needs to be tested by an end-to-end ETL run.  It’s better to discover this in test than production, any day of the week.

Stay curious… Keep learning…


Leave a Reply

You must be logged in to post a comment.

Using Gravatars in the comments - get your own and be recognized!

XHTML: These are some of the tags you can use: <a href=""> <b> <blockquote> <code> <em> <i> <strike> <strong>