script to remove nulls from DB?

[Steve Walker]

Hi all.

I've been tasked with "speeding up" a mid-sized production system. It
is riddled with nulls... "IsNull" all over the procs, etc.

Is it worth it to get rid of the nulls and not allow them in the
columns anymore?

If so, how to go about removing the nulls with a script?

Thanks so much,

Steve in Norcal

[Simon Hayes]

Re: script to remove nulls from DB?


"Steve Walker" <swalker@ainet. com> wrote in message
news:bde87b71.0 405240929.326ab c53@posting.goo gle.com...[color=blue]
> Hi all.
>
> I've been tasked with "speeding up" a mid-sized production system. It
> is riddled with nulls... "IsNull" all over the procs, etc.
>
> Is it worth it to get rid of the nulls and not allow them in the
> columns anymore?
>
> If so, how to go about removing the nulls with a script?
>
> Thanks so much,
>
> Steve in Norcal[/color]

You need to consider the data model first - if it has a good reason for
requiring certain columns to be NULLable, then you should leave them.
Although in general, if you have a large number of NULLable columns, then
your data model probably needs to be reviewed, as it suggests you may have
issues such as incomplete data, or denormalized tables.

To make a column NOT NULL from a script, you can use ALTER TABLE:

ALTER TABLE dbo.MyTable
ALTER COLUMN SomeColumn INT NOT NULL

Simon

[Erland Sommarskog]

Re: script to remove nulls from DB?

[posted and mailed, please reply in news]

Steve Walker (swalker@ainet. com) writes:[color=blue]
> I've been tasked with "speeding up" a mid-sized production system. It
> is riddled with nulls... "IsNull" all over the procs, etc.
>
> Is it worth it to get rid of the nulls and not allow them in the
> columns anymore?[/color]

Probably not. As Simon said, the columns are likely to be nullable for
a reason.

Then again, I would not be surprised if some columns are nullable only
because of lax design.

It's better to track down slow-running queries, and see how you can
improve them, either by rewriting or better indexing.

Lots of isnull does not have to be bad, but it can be. Take these
two examples:

SELECT col1, col2, col3
FROM tbl
WHERE thiscol = isnull(@par, ' ')

SELECT col1, col2, col3
FROM tbl
WHERE isnull(thiscol, ' ') = isnull(@par, ' ')

The first is OK, and the cost of isnull is likely to be minimal. The
second, however, is potentially bad. This is because, if there is an
index on thiscol, then SQL Server can not seek this index to find
the data. As soon as a column is embedded into an expression, it
is disqualified from index seeks. This is because the index is
organized after the values of the column, not the values of the
expression.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp