MS SQL Server - Indexed view and database options

Author

Indexed view and database options

jcvoon

2006-10-24, 6:28 pm

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_N
ULL 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

Hari Prasad

2006-10-24, 6:28 pm

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('ARI
THABORT') = 0 SET ARITHABORT ON
IF sessionproperty('CON
CAT_NULL_YIELDS_NULL
') = 0 SET
CONCAT_NULL_YIELDS_N
ULL ON
IF sessionproperty('QUO
TED_IDENTIFIER') = 0 SET QUOTED_IDENTIFIER ON
IF sessionproperty('ANS
I_NULLS') = 0 SET ANSI_NULLS ON
IF sessionproperty('ANS
I_PADDING') = 0 SET ANSI_PADDING ON
IF sessionproperty('ANS
I_WARNINGS') = 0 SET ANSI_WARNINGS ON
IF sessionproperty('NUM
ERIC_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

"jcvoon" <jcvoon@maximas.com.my> wrote in message
news:1158318183.442425.93730@e3g2000cwe.googlegroups.com...
> 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_N
ULL 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
>

Dan Guzman

2006-10-24, 6:28 pm

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.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"jcvoon" <jcvoon@maximas.com.my> wrote in message
news:1158318183.442425.93730@e3g2000cwe.googlegroups.com...
> 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_N
ULL 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
>

Kalen Delaney

2006-10-24, 6:28 pm

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.

--
HTH
Kalen Delaney, SQL Server MVP

"jcvoon" <jcvoon@maximas.com.my> wrote in message
news:1158318183.442425.93730@e3g2000cwe.googlegroups.com...
> 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_N
ULL 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
>

jcvoon

2006-10-24, 6:28 pm

Hari Prasad, Dan Guzman, Kalen Delaney

Thanks.

Sponsored Links