Gary (gary_strader@wcc.ml.com) writes:
> How could connection settings on different clients create different
> execution plans on the server?

CREATE TABLE #null(a int NULL)
go
INSERT #null(a) VALUES (NULL)
go
SET ANSI NULLS OFF
go
SELECT * FROM #null WHERE a = NULL
go
SET ANSI NULLS ON
go
SELECT * FROM #null WHERE a = NULL

And, yes, those are different plans. When ANSI_NULLS are ON (as it should
be), the optimizer can transform the query to a no-op, but for the other
case it may have to scan the table.

More generally, a execution plan is associated with a set of SET options,
and if a process does not match that setting, another plan will be used.

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

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