Computed column

[BCR]

I created a index on a computed column. I did not see any improvement
in performance with a join to this column and also my inserts and
updates to this table are failing. Any ideas?

Chender

[Dan Guzman]

Regarding your failing inserts and updates, the likely cause is that one ore
more connection options are not set properly for maintaining a computed
column index. The error message will indicate which option(s) is
incompatible. The required options specified in the Books Online
<createdb.chm::/cm_8_des_05_8os3.htm> are:

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
SET NUMERIC_ROUNDABORT OFF

All but ARITHABORT are set automatically when connection via OLEDB or ODBC.

Please post your DDL, query and sample data for help with your performance
issue.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"BCR" <bc***@yahoo.com> wrote in message
news:32**************************@posting.google.c om...

I created a index on a computed column. I did not see any improvement
in performance with a join to this column and also my inserts and
updates to this table are failing. Any ideas?

Chender

[Erland Sommarskog]

[posted and mailed, please reply in nes]

BCR (bc***@yahoo.com) writes:

I created a index on a computed column. I did not see any improvement
in performance with a join to this column and also my inserts and
updates to this table are failing. Any ideas?

As Dan pointed out the update/inserts are failing because of the settings.
I like to add two things. The first is that the lack of performance is
very likely due to the same problems. If the proper settings are not in
effect, SQL Server will not consider the index.

The other is that the two settings ANSI_NULLS and QUOTED_IDENTIFIER are
saved when you create a stored procedure, view, trigger or user-defined
functions. So that if even if these are on at run-time, they will not be
in effect if they were off when the procedure was created. Some tools,
Enterprise Manager and OSQL to be precise, have QUOTED_IDENTIFIER off by
default.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp