Posted by Erland Sommarskog on 10/13/07 15:18

Jason Wilson (wilsonj@ausrad.com) writes:
> I was looking to improve the performance of an ASP.NET application by
> creating the an indexed view that could be used instead of some of the
> root tables.
>
> What I didn't realize is that it would affect any future conenctions
> to the root tables.
>
> This of course crashed the application on any type of insert, update,
> or delte from the root tables.
>
> Funny thing is when I removed the indexed view -- it didn't help.
> I tried flipping the offending options to the opposite of way they
> were set on the database and the errors wouldn't go away.
>
> I ended up restoring the database from before my mess up to fix it.
>
> I was hoping that the SQL experts out there might shed some light on
> my problem before I try again.

Since you do not include which version of SQL Server you are using, you
don't include any error messages, I will have to guess.

For indexed views to work, there are a couple of SET options that must
be in the correct posittion: QUOTED_IDENTIFIFER, ANSI_NULLS, ANSI_PADDING,
CONCAT_NULL_YIELDS_NULL, ANSI_WARNING, ARITHABORT (SQL 2000 only) and
NUMERIC_ROUNDABORT. They must all be on, but the last which must be off.

The first two are saved with stored procedures, and the run-time setting
does not apply. ANSI_PADDING is saved per table column. For the other
only run-time settings apply. A common error is that procedures have
been created with a tool that by default has QUOTED_IDENTIFIER or ANSI_NULLS
off. SQLCMD and OSQL has QUOTED_IDENTIFIER off by default. Enterprise
Manager in SQL 2000 has both off by default.

So my guess is that your stored procedures have been loaded through
any of these tools, and care has not been taking to use the correct
options. Why the error persisted when you removed the indexed view, I
don't know, but my guess is that you had in fact not removed the view
although you claim that you did.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]