indexed views and session properties

I discovered Friday that session properties affect whether or not the
query optimizer uses the index of an index view in its query plan.  I
thought I should leave a note here because I found no clear discussion of
it in Microsoft's documentation.  

(I also think we should consider how to set session properties
automatically in FreeTDS.  I first mused about that on 11 Feb 2009,
"FreeTDS and nvarchar(MAX) in MSSQL-2005+".)  

I created an indexed view.  The base table has 500 million rows; the view
3000 rows.  Queries against the view are very fast compared to the base
table if the query optimizer takes advantage of the view's index.  

Observed behavior: Queries issued from the 2005 GUI client ran in under a
second, but isql/osql took forever, more than several minutes.  SET
SHOWPLAN_ALL ON confirmed that the query plan was ignoring the view's
index.  Because the only difference between the clients from the server's
point of view was the session properties, I experimented with them.  

Conclusion: The SQL Server 2005 query optimizer will not use the view's
index unless the same combination of session properties are in effect as
are needed to create the view in the first place.  If any are off, the
view is still accessible but only in the non-indexed way: the query will
be resolved against the table, ignoring the view's index.  

As long as the session properties meet the server's critera, the view's
index will be used, regardless of client library.  

Recall that indexed views have very specific session property
requirements:

http://msdn.microsoft.com/en-us/library/ms191432(SQL.90).aspx

	"The following SET options must be set to ON 
	when the CREATE INDEX statement is executed: 
	ANSI_NULLS
	ANSI_PADDING
	ANSI_WARNINGS
	CONCAT_NULL_YIELDS_NULL
	QUOTED_IDENTIFIER
	The NUMERIC_ROUNDABORT option must be set to OFF."

Not mentioned: those same settings had to be in effect when the table was
created.  (I did thoroughly enjoy copying the 500 million rows to between
two identical tables created with different session properties.  It was
fun, really.)  

Also not mentioned: those same settings must be in effect if the index it
to be used in querying the view.  

But there is this:

http://technet.microsoft.com/en-us/library/cc917715.aspx

	"The SET options of the current session must be set to the values shown
in the Required Value column for the current session whenever these
operations occur:

	"An index is created on a view.

	"There is any INSERT, UPDATE, or DELETE operation performed on any table
participating in the indexed view.

	"The indexed view is used by the query optimizer to produce the query
plan."

(I find that last sentence a bit obscure.  Basically, an *indexed* view is
useless unless the session properties are just so.) 

<suggestion>
Dear Microsoft, 

Let's help the developer notice when things aren't going according to
plan.  Create a new session property SHOWPLAN_WARN.  When ON, produce a
severity 1 message whenever an otherwise useful index isn't used by the
query optimizer.  
</suggestion>

--jkl