Posted by Erland Sommarskog on 03/02/06 22:40

John Bokma (john@castleamber.com) writes:
> I was somehow thinking on SET ANSI_WARNINGS OFF, query, back on, but
> indeed, need to read the problems this might cause. I was hoping that
> there would be an easier option (well, maybe it's easy enough :-) )

More specifically, these are the issues you can run into:

1) You cannot perform updates that would affect an indexed view or an
indexed computed column.
2) SELECT queries cannot make use of index on views and computed columns
and my run slower.
3) Cannot perform queries on linked server.
4) Adding SET ANSI_WARNINGS ON in a stored procedure causes the procedure
to be recompiled, which can degrade performance if compilation cost
is high in relation to execution time.
5) Overflows and division by zero will go unnoticed, leading to other
stange and unexpected results. This can mitigated by setting ARTITHABOT
ON. (Which has to be on for indexed views and indexed computed columns
anyway.)

My experience is that if you use stored procedure or parameterised
statments is that you rarely run into this errors. This is because,
the errors is only raised on column assignment, but not parameter or
variable assignment.

--
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]