Posted by dmarkle on 01/08/07 00:34

I suspect the reason (...AND ... IS NOT NULL) is helping you out is
that it's forcing the optimizer to choose a table scan instead if
hitting up that index you made on email.

Before you go changing minutiae like ANSI_NULLS, do realize that your
tables are, (relationally speaking), nonsense. Any table without a
PRIMARY KEY or otherwise unique key is likely going to cause problems
with the optimizer, and it often happens in bizarre ways like this.

You should do the following:
1) Make the clustered index on "bk" UNIQUE. If you can't do that...

2) Add email to your clustered index as a secondary key and recreate
the clustered index as UNIQUE, or make the combination of the two
columns a PRIMARY KEY. If you can't do that...

3) Analyze your data and fix your process to not allow wholly duplicate
rows, which are nonsensical. If you can't do THAT (ugh):

4) Add an IDENTITY column to your table. So as not to drastically
increase your index sizes, you could either add this column as a
secondary key after bk and make the clustered index UNIQUE, or you
could create a nonclustered PRIMARY KEY on that identity.

Let me know how it goes.
-Dave Markle
http://www.markleconsulting.com





Erland Sommarskog wrote:
> Dave (daveg.01@gmail.com) writes:
> > Erland that was an excellent post! However it was not my problem. The
> > code is run from .NET application and I am testing using SQL Management
> > Studio.
> >
> > I added this to the test script and it did not help.
> > SET ANSI_NULLS ON
>
> As long it's a loose script, ANSI_BULLS would be on by default. I will
> have to admit that I was clutching a straws. Without access to the
> database seeing it, it is very difficult to analyse the problem accurately.
> It's not alwyas that easy even if you have full acecss.
>
>
>
> --
> 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]