Posted by Dave on 01/08/07 16:40

I like your explanation!

The table does not currently have a primary key.

I basically have options 2 and 4 available; 1 and 3 are out of my
control.


I will lobby for adding an identity column to this table. It happens
to be very wide with 12 single column indexes so I really don't want
to make the clustered super wide.

Just for clarities sake, in order for the optimizer to properly
understand the tables, do I have to add a PK constraint to the identity
column?

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

Do you know of any book or other reference to this (specifically how
the optimizer needs a key)? It would really help me convince everyone
to use keys on everything.

Thanks!



dmarkle wrote:
> 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]