Hi:

I'm using SQL2000 Ent, and I've created a indexed  view in my database,
my question is:

Is it ok to set the following options at database level (By default all
is OFF, correct me if I'm wrong) ?

SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON

Otherwise I've to turn those option ON and OFF when I update the base
table reference in the indexed view.

Please Advice

Thanks
JCVoon

Hi,

If it is me then I would encourage to turn off the options when required;
this will ensure that this behaviour change will not affect other
sessions/connections.
You could very well use the below code to verify and turn on the database
options.

IF sessionproperty('ARITHABORT') = 0 SET ARITHABORT ON
IF sessionproperty('CONCAT_NULL_YIELDS_NULL') = 0 SET
CONCAT_NULL_YIELDS_NULL ON
IF sessionproperty('QUOTED_IDENTIFIER') = 0 SET QUOTED_IDENTIFIER ON
IF sessionproperty('ANSI_NULLS') = 0 SET ANSI_NULLS ON
IF sessionproperty('ANSI_PADDING') = 0 SET ANSI_PADDING ON
IF sessionproperty('ANSI_WARNINGS') = 0 SET ANSI_WARNINGS ON
IF sessionproperty('NUMERIC_ROUNDABORT') = 1 SET NUMERIC_ROUNDABORT OFF
go

Since it is session level; once the session is closed automatically the
values for those database options will go back to default.


Thanks
Hari
SQL Server MVP

Show quoteHide quote

In most cases, you only need SET ARITHABORT ON (or sp_configure 'user
options' bit 64 at the server level).  The OLEDB and ODBC APIs commonly used
to connect to SQL Server automatically set all these options on except
ARITHABORT.  The order of precedence is:

1)  current connection settings
2)  database default settings
3)  server default settings

Also, be aware that  ANSI_NULLS and QUOTED_IDENTIFIER are 'sticky' options
so the settings in effect when a stored procedure is created are used at run
time.

Show quoteHide quote

Hi JCVoon

SET options are only applicable at the session level. There are database
properties with similar names, but they are not SET options. Also, in many,
if not most, cases, the database option is useless, because most client
tools set some of these options when they make a connection. So you might
have a database defined to use one option, but then every connection using
SET commands could override that. Many of the SET options set by your client
software are set behind the scenes, but you can see them if you trace your
connection with Profiler.

Show quoteHide quote

Hari Prasad, Dan Guzman, Kalen Delaney

Thanks.

SQL Server Backup - 100Gb Database
Migrate from Sql Server 2005 to Sql Server 2000
SQL Server 2005 Express SP1 running out of memory on a VPS
Limit number of backups saved
Help on sql sync
Differential backups and transaction logs.
SQL Server 2005 - replication jobs
SQL2005 Ent Saving Diagram reports 'isBusy'
How to set a photo path when creating table
dbcc checkdb on a large table