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>

professional dissertation abstract writer websites au slaughterhouse five imagery custom definition essay writer services for college popular essays writers site for school cheap school essay editing for hire au professional custom essay writer website best personal statement proofreading services for mba miss brill tone best admission essay writers sites ca best phd essay editing services gb madding crowd meaning cheap blog writer website uk best bibliography writing websites professional business plan ghostwriter site for school professional term paper proofreading sites for college esl cover letter editing site for mba best cover letter proofreading service for school write my management dissertation abstract criminal law writer for hire professional critical thinking proofreading site for college best course work editing sites online esl reflective essay ghostwriter website help writing family and consumer science blog post popular paper proofreading for hire for university sonnet 130 analysis line by line custom custom essay editor services ca write my philosophy book review best report ghostwriters site for school best university personal essay assistance pay to get drama biography top bibliography writer site ca analysis essay ghostwriting for hire uk popular business plan editor services gb cheap masters application letter topics popular dissertation abstract ghostwriter services for university custom thesis proposal editing services us custom college essay writing website for phd pay to do esl custom essay on usa top dissertation methodology editing service for college top presentation editor websites usa help with cheap dissertation chapter online cheap thesis proposal ghostwriters websites for college best home work writing sites gb top dissertation ghostwriting services ca best article review writers site online esl critical essay editor sites for masters top school dissertation introduction ideas top paper ghostwriter sites for school help with my culture article review popular critical analysis essay proofreading service for phd report writers for hire gb top homework proofreading websites for phd custom writing for hire uk pay for my custom dissertation chapter buy family and consumer science problem solving critical analysis essay writers service uk top university essay proofreading for hire au cheap problem solving ghostwriting for hire for school professional critical essay proofreading sites usa herbert gans urban villagers cheap resume editor service for phd esl content writers services for school best essay editing sites usa popular annotated bibliography editing for hire for phd esl best essay writing service for masters custom literature review writing site for university custom dissertation chapter ghostwriters services for mba custom application letter ghostwriting site usa cheap admission essay editor website online professional dissertation conclusion writing site us top dissertation introduction writing sites au professional application letter ghostwriter for hire au esl personal essay editing sites for school esl report ghostwriting website ca theater studies ghostwriter for hire professional movie review ghostwriters site for phd custom personal essay ghostwriter websites for masters those winter days by robert hayden the execution of louis xvi popular persuasive essay editing websites for phd professional school essay editing sites for phd cheap resume ghostwriter site gb cheap article review proofreading services ca professional dissertation results ghostwriters sites gb best cv proofreading for hire usa popular reflective essay ghostwriter service cheap problem solving writer site for college cheap research paper editing sites us technology writers websites popular personal statement writer sites gb top content editing site for masters popular dissertation abstract editing site for mba essay on inequality professional critical thinking ghostwriters service for university best report writing websites for mba professional curriculum vitae writing websites for university esl analysis essay writing site usa write health blog post help me write top persuasive essay online write me best reflective essay on donald trump cheap annotated bibliography editing websites usa popular essays editing service online college editor for hire uk custom mba content samples top critical essay writer website usa help writing criminal law essays custom report proofreading website for masters custom thesis proposal writing service gb anne sexton cinderella poem write me professional problem solving cheap literature review proofreading sites buy film studies dissertation results miseducation of a negro custom dissertation abstract writing services popular mba essay editing site ca academic writing site custom dissertation results ghostwriting services for college top article writer site for phd gatsby tragic hero help writing custom best essay on hillary clinton book review ghostwriting website us popular letter editor websites usa professional mba essay writer websites us buy logic article review blog writing websites au order speech admission paper top dissertation introduction ghostwriter for hire us best custom essay ghostwriters services uk woodrow wilson essay best dissertation conclusion ghostwriter website for mba custom best essay ghostwriting sites for masters examples of self government in the colonies best phd essay writers services ca popular course work ghostwriter site for mba professional dissertation conclusion editor services uk professional creative essay writing sites au top definition essay writers service for masters custom article writers services for college pay to write nursing thesis popular dissertation hypothesis writing for hire for university esl university phd essay sample professional case study ghostwriters site us cheap dissertation hypothesis ghostwriter service gb esl descriptive essay ghostwriters for hire au cheap phd essay ghostwriters websites us top literature review writer website for masters best admission essay writing site for masters pay for my literature blog post guilt redemption cycle top custom essay editing services for phd esl cv ghostwriting site online paper writer websites uk best school essay editor site for college cheap paper editor service for mba help me write accounting dissertation argumentative essay writing sites ca robin hood case esl personal statement ghostwriters website for masters custom argumentative essay proofreading services usa popular home work writers site gb cheap critical essay proofreading service online admission writers service au esl custom essay editor service online custom masters essay editor services uk top term paper ghostwriters service for college custom essays writing website for university professional dissertation proposal editing website gb popular article review ghostwriting services online professional persuasive essay proofreading service au custom admission essay writers services gb top personal essay editing service us cheap definition essay ghostwriters services uk custom admission essay writer for hire online top critical analysis essay editing websites help me write cheap creative essay on usa best thesis writers site pay to write geology paper professional school best essay examples top homework writer services ca custom blog post writers service uk best dissertation editing site for school thesis writer for hire online write me government content custom article review ghostwriter for hire for mba pay for my esl school essay on usa best descriptive essay writing websites for mba custom problem solving ghostwriters site usa buy art & architecture case study popular critical essay writer services au cheap cheap essay ghostwriting services for phd popular masters movie review samples help me write best phd essay top course work ghostwriting site for phd custom dissertation abstract editing websites gb professional dissertation writing service for college i sell my dreams popular application letter writer site us euro disney failure custom critical thinking editor websites gb popular assignment ghostwriters service uk creative writer site gb movie review ghostwriters for hire online top business plan writer website us write my top persuasive essay on civil war university writing sites uk popular critical essay proofreading sites for college professional admission essay editor services for masters top definition essay on founding fathers custom reflective essay ghostwriting services for phd write me speech thesis statement admission essay editor websites usa esl business plan writers services gb custom business plan writer service for university math ghostwriter sites esl creative writing ghostwriting for hire gb custom college essays examples cheap business plan editing websites for university top paper writer sites for masters custom homework editor services for masters best masters application letter help esl creative writing ghostwriter service gb esl term paper ghostwriter for hire for mba professional thesis proposal ghostwriting services gb custom expository essay writers site online best university biography topics best expository essay writing sites ca esl creative essay ghostwriter sites gb cheap dissertation chapter writing service ca popular problem solving ghostwriting for hire for university custom phd essay ghostwriter sites au write theater studies dissertation results type my best cheap essay on trump best term paper ghostwriting service for phd top masters university essay assistance a midwives tale esl article ghostwriter services gb esl cheap essay writing websites uk custom application letter writer site ca advantages of communism best dissertation writers sites custom literature review editing site uk english essay editor websites order popular descriptive essay on hillary clinton esl university essay writer website for college top persuasive essay ghostwriters websites gb cheap writers site au pay to do math dissertation abstract top admission essay writers sites for mba help writing custom reflective essay on pokemon go help with my zoology home work english as second language writing websites top school essay ghostwriters site for mba esl book review ghostwriting for hire us help with my professional argumentative essay on donald trump professional letter ghostwriting sites usa professional application letter writer site best college essay ghostwriters site dissertation results writing for hire usa esl biography ghostwriting website us popular school essay writer sites usa professional dissertation proposal proofreading sites ca cheap bibliography ghostwriters site for university help with my custom admission essay on trump cheap rhetorical analysis essay proofreading website for mba professional book review writing sites for masters custom masters essay ghostwriter website for school type my science essay custom assignment ghostwriter websites for masters popular dissertation methodology writer websites au thesis proofreading sites popular dissertation proofreading website for university who is mistress hibbins popular application letter editor site for university order ancient civilizations resume popular thesis editor service us greek influence on roman culture esl argumentative essay editing service for school professional dissertation abstract editor website for school help with my top analysis essay on hacking essays writers website esl university assignment tablets vs textbooks essay type my anthropology cover letter best university annotated bibliography advice esl custom essay ghostwriters sites uk cheap college business plan samples cheap college essay ghostwriting site for college top argumentative essay editor services for masters custom resume writers for hire au cv writing sites usa best blog writers services for school custom critical thinking writer websites for mba write top critical analysis essay esl essays ghostwriter website gb definition ghostwriting websites usa cheap university curriculum vitae example best critical thinking ghostwriting services online best critical thinking writers service esl literature review editing site online esl critical analysis essay ghostwriting services popular speech writing services online popular assignment writing services online esl bibliography writer service for university funeral blues poet top dissertation conclusion ghostwriters site au order botany course work top masters essay writers services uk top best essay writers site online pay for my law article curriculum vitae writers website usa help with criminal law literature review custom dissertation abstract ghostwriter website usa phd ghostwriting website uk esl paper writers website au best argumentative essay editor sites online write popular argumentative essay on hillary cheap essay writing site ca popular scholarship essay ghostwriters site for college cheap expository essay writers sites custom college thesis statement assistance cheap biography ghostwriter services for mba cheap admission paper writers websites for masters pay for my cheap article review esl masters essay editor websites usa pay for professional masters essay on hillary clinton top critical essay writers websites online buy professional critical analysis essay on civil war top presentation writer sites us custom thesis statement writer site au popular speech ghostwriter sites professional research proposal ghostwriting services usa thesis ghostwriters websites au esl course work writer services for phd popular definition essay on shakespeare popular phd essay ghostwriters site for college cheap business plan ghostwriter websites for school professional university essay proofreading for hire gb professional dissertation abstract editing site for phd professional thesis editing websites for mba arranged vs love marriage custom critical essay writer website usa professional research paper writers service for phd film studies ghostwriting services professional speech writing site au type my esl expository essay on hacking custom dissertation conclusion editing site usa professional mba essay ghostwriting websites au cheap assignment writing site for college esl cover letter editor for hire for mba esl dissertation proposal ghostwriters websites uk julio cortazar the night face up professional letter ghostwriters service usa custom critical essay editor services popular dissertation conclusion writer sites uk help with my popular admission essay on civil war pay to write professional cheap essay on presidential elections professional definition essay editor website gb antigone character analysis esl assignment writer site au cheap case study writing sites au academic editing sites usa top phd essay editor services online best homework writer websites usa spinster sylvia plath dissertation results ghostwriting website usa cheap dissertation methodology editor services ca order best custom essay on lincoln symbolism in beowulf cheap creative writing writers for hire for school best critical analysis essay ghostwriters sites usa popular creative essay ghostwriter sites for university esl biography ghostwriter for hire gb esl scholarship essay editor sites gb top dissertation results editing website au popular custom essay editor website gb esl analysis essay proofreading service online esl university essay ghostwriting website for college professional article review ghostwriter websites gb pay for my communication thesis proposal custom dissertation hypothesis editing website ca top cover letter ghostwriting website for university top course work writers sites online blog ghostwriting sites uk popular phd essay ghostwriters websites usa essay about lying popular report writers site for masters best critical thinking editing site gb cheap dissertation introduction ghostwriting services online help with biology research paper esl reflective essay ghostwriters website ca best home work ghostwriters website for university write me environmental studies problem solving critical analysis writing service au esl business plan writer website usa professional masters essay ghostwriting services for mba esl dissertation proposal proofreading website gb professional definition essay ghostwriter for hire ca characteristics of roman art management essay advice political causes of french revolution accounting writing for hire professional case study ghostwriting website usa esl phd dissertation abstract topic annotated bibliography ghostwriting service au popular essay proofreading services for mba professional dissertation introduction writers sites for phd custom critical analysis essay editor services for university top dissertation introduction ghostwriter for hire for mba pay for psychology book review custom school custom essay example custom dissertation introduction writing websites usa top curriculum vitae writers website ca as i lay dying criticism help writing popular creative essay on trump top presentation writers services online professional article review ghostwriter sites us top curriculum vitae ghostwriter site for phd case study writers service usa popular cv writing websites summary of anthem write my philosophy cv esl mba essay editor websites au stereotypes are bad best thesis editor site uk write me world literature cover letter presentation editor sites uk help me write management business plan custom creative essay proofreading service online pay for top argumentative essay on civil war blog post help best university essays ideas esl dissertation proposal ghostwriter sites for phd custom movie review ghostwriting service for college essay editor websites ca popular application letter editing sites ca examples of dramatic irony in oedipus esl thesis proposal ghostwriters site ca do my expository essay on presidential elections popular academic essay writers website top cheap essay writer services ca professional annotated bibliography proofreading for hire gb professional dissertation ghostwriting service us custom cv editing website for university buy medicine creative writing top admission paper editor website uk pay to write investments dissertation methodology analysis ghostwriting site us top dissertation writer website gb cheap biography editing for hire uk best admission paper ghostwriters website usa popular papers writer service uk pay to write professional reflective essay on hillary write law dissertation esl college essay ghostwriters for hire for mba feudal government definition popular dissertation abstract writer sites for school report editing custom thesis writing service for university popular definition essay writing websites online best biography editor website for university help writing finance research paper college ghostwriter website online custom college blog example pay for philosophy thesis proposal custom argumentative essay proofreading website for school type my popular college essay on presidential elections popular personal essay writer site for school help writing leadership personal statement order best university essay on founding fathers professional mba personal essay samples esl paper writers for hire for school custom biography ghostwriter site esl content proofreading website custom movie review ghostwriting websites for college cover letter editing site usa professional content editor website for phd cheap cheap essay ghostwriting service for phd custom critical thinking proofreading service ca best argumentative essay writers service for university popular school essay proofreading websites for masters help me write chemistry dissertation top term paper ghostwriting websites au top letter writers websites online best bibliography ghostwriter site for masters custom persuasive essay writing sites for school pay to do custom expository essay on hillary clinton professional problem solving proofreading site for phd custom masters essay editing websites online popular home work writing sites for mba buy custom custom essay on hillary best dissertation chapter ghostwriter service au top movie review ghostwriters sites gb popular reflective essay editor for hire for college custom blog writer services uk moes franchise cost creative editor websites online theater studies ghostwriter for hire order sociology article top critical thinking writer websites ca help me write cheap descriptive essay on civil war