I am currently working with a MSSQL 2008R2 Database which uses the following SET-Commands frequently:


While I was researching these Settings I stumbled across the following Note for ANSI_NULLS on Microsofts technet (Link):

In a future version of Microsoft SQL Server ANSI_PADDING will always be ON and any applications that explicitly set the option to OFF will produce an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Because of this I would like to enforce a "global" Setting. I have been searching for the better part of a day but as I understand it you can always change these settings. To be completely sure I would like to ask here:

Is it possible to either enforce a Setting like ANSI_NULLS ON or to disable the possibility of changing these Settings on "any" level. I would like to use this to test if the Database would "survive" if the ability to set a specific option to OFF is disabled.

I hope I was able to describe my problem. This is not my first language. Thanks in advance.

PS: On StackOverflow I was advised to Ask this Question here too. Original Question: Here


There is no way to enforce a global setting or prevent people running


You can get a quick idea if your application is using any deprecated features by running

FROM   sys.dm_os_performance_counters
WHERE  object_name LIKE '%Deprecated%'
       AND cntr_value > 0
ORDER  BY cntr_value DESC 

If you do find areas of concern then you could set up a Server Side trace capturing the deprecation events.

enter image description here

This also returns the session id and SQL Handle of the offending batch to allow you to investigate further if you do see any events.

A slightly more sophisticated approach would be to set up a notification procedure to retrieve the text associated with the SQL Handle more quickly to reduce the probability that the offending SQL is no longer in the cache when you come to analyse the results.

Or you could probably use extended events to retrieve the TSQL along with the event.

  • First off let me say thank you for that answer! Second: Does this also apply to Quoted Identifiers? – DLA2014 Jan 14 '14 at 13:29
  • @DLA2014 - That isn't deprecated so no it doesn't show up in any of the deprecation events. – Martin Smith Jan 14 '14 at 13:30
  • Could you tell me how you set up such a Server Side trace? I am somewhat new at this. – DLA2014 Jan 14 '14 at 13:56
  • @DLA2014 - If you're going to learn something new much better to learn extended events. Take a look at the article here. Seems to do what is needed from a quick look. – Martin Smith Jan 14 '14 at 14:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.