[dba-SQLServer] NULLs

I'm not as quick on the draw as you, Susan. There are a couple of parts to
this argument. The first is the implication that any null column cannot be
indexed, or even if the system allows it, the index will never be used. The
second is that column position matters in terms of performance, and leftwise
is better. I first heard this argument way back when in the days of DOS.
dBASE was written by Wayne Ratliff and Jeb Long, both ex JPL programmers,
and it mattered on mainframes. It's possible that they just brought the
notion with them to the PCm or that they tested it first and the tests
mirrored mainframe experience.

At any rate, if the conjecture is true, then by extension one should arrange
the columns in order of frequency of use.(in indexes and queries). I think
that for a small database, a few hundred megs let's say, the performance
difference will be minimal even if the conjecture is true. But medium to
large databases (20 gigs to a terabytem say) the difference, if any, might
be significant.

As it happens, I have a table with over 50 million rows in it; Real data,
not manufactured. I can make a copy and arrange the columns as suggested
(actually, they might aready be arranged that way, in which case I'll
reverse the process and move one or two toward the right)

If anybody wants me, I shall be in the lab. Ooooo ho ho hoagggh.

A.

On Sat, Mar 14, 2009 at 12:59 PM, Susan Harkins <ssharkins at gmail.com> wrote:

> I agree, nulls aren't the problem, not accommodating them is. If you're
> going to allow them, you have to account for them in your queries, etc.,
> but
> other than that... why not allow them?
>
> As for your specific SQL Server indexes to the left please question -- I
> have no idea and I can't imagine that with today's resources, it even
> matters.
>
> Susan H.
>
>
>