Sql-server – how to ignore the SSMS (sql server management studio) sql server ansi settings

configurationexecution-plansql serversql-server-2008-r2ssms

The options in Management Studio

On Management studio Tools->Options->Query Execution->SQL Server
there are options that are set and these prevail over the same options that are set on database level.

enter image description here

enter image description here

The Options at Database level

on management studio you right-click on a database and you can see what are the defaults for a number of options: (in yellow on the picture below)

enter image description here

using sys.databases you can get hold of these options using T_SQL.

enter image description here

The Options At Session Level

Using sys.dm_exec_sessions you can get hold of the options as they are set for the current session.

enter image description here

Questions

Is there a way to make sure the database setting will prevail over the management studio settings?

I thought about creating a plan, and use OPTION (USE PLAN)
but that has other consequences that is beyond the scope of this question.

Best Answer

If you are asking whether it is possible to enforce the database defaults for every connection that ever happens on the server, then no. Those defaults only take effect if something contradictory isn't set at the connection level.

In any individual batch in SSMS you can use statements like SET ANSI_NULLS ON, SET ANSI_NULLS OFF, SET QUOTED_IDENTIFIER ON, and so on, to control the way the following statements are interpreted.

With stored procedures it is a little more complicated:
When a procedure is created it remembers the current values for the ANSI_NULLS and QUOTED_IDENTIFIER options and uses them within any call to that procedure, but the same is not the case for other settings like ARITHABORT and ANSI_PADDINGS - for those you need to use SET <setting> ON/OFF in the procedure to fix the behaviour. See the Interoperability section of CREATE PROCEDURE (Transact-SQL) in the product documentation for more description of this situation.

Related Question