Best approach to Default when NULL

[rasETL]

We are trying to determine the best approach to handling the setting of default values on an insert if the column is NULL. We have a number of Tables where NULL values will not be allowed, but a selected default value needs to be substituted when the column being inserted is NULL.

We are looking for the best approach from a maintenance and implementation standpoint.

Is there anything we can set up in the Table design within Enterprise Manager that could handle this?

Just as an FYI - we will be using Informatica's PowerCenter to load the Tables.

Looking for your thoughts on this.

Thanks!

 

[vongrunt]

We have a number of Tables where NULL values will not be allowed, but a selected default value needs to be substituted when the column being inserted is NULL.

Answers are often within questions, so... why? Are these defaults:

- true "application defaults" (1 for "active" when user registers, getdate() for "dateInserted" etc) or
- "lazy empty" values (0, '' etc) used mostly to simplify string manipulation and math expressions?

App defaults are OK. Empties may bring some hidden traps and delayed problems.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[rasETL]

The defaults I am talking about will be real, application defaults. For instance, the data coming in might have a value of 0000-00-00 for a column that is a date field because the date is notapplicable (ie...a closing date for something that is still open).

In a case like this, we would like the deafult date to be something like 1900-01-01.

 

[vongrunt]

> In a case like this, we would like the deafult date to be something like 1900-01-01.

In last application I had to patch people used DEFAULT something NOT NULL all over the database. 1900-01-01 for dates, 0 for numbers, '' for strings etc. And here are some encountered problems:

- aggregate functions like AVG() and STDEV() produced wrong results, unless WHERE datecolumn <> '1900-01-01' was specified
- some queries were slow because column statistics had 'artificial' distribution
- date math was pain in the a** (say, calculating number of days between two dates).
- naturally, people forgot to use WHERE exclusion on at least dozen places

And my favorite: one of columns was named IsCustomerSatisfied, bit data type default 0. Ad hoc report showed that 97.2% of customers weren't satisfied. In reality these customers were only marginally not satisfied; the rest didn't say yes or no. Because distinction between "not satisfied" and "unknown" wasn't present, report was useless.


------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[JamesLean]

I would backup what vongrunt has said here. In your example, if the "something" is not closed, I would leave the closedate NULL.

A closedate column literally holds "the date something was closed". If it's not closed then the closedate is logically "unknown" - this translates to NULL in database terms. Adding a default date is logically - and literally - inaccurate (and will lead to problems down the line.

--James

 

[r937]

i like vongrunt's distinction between true application defaults and "lazy empty values"

it's the lazy empty values that cause all the troubles

they cause more problems than they purport to solve, and are almost always the sign of an inexperienced developer

there's nothing wrong with NULLs ©™



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[rasETL]

I agree with everyone's assessment of the use of NULLs for default values. However, our esteem team in Database Admin and Data Architecture do not see it our way.

Therefore, as a Lead developer, I have to implement their decision in as simple a process as I can.

Any ideas?

 

[r937]

you're kidding, right? your DBA/DA team won't allow NULLs?

yeah, i have several ideas, most of them unprintable

i guess the main one is to start looking for a new job

;-)

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[rasETL]

This is a "special situation" type of Database. It is going to be an Operational Data Store for reporting and interfacing - the interfacing may be sent to a mainframe legacy System. Thus the reason, I guess, that NULLS are out.

 

[vongrunt]

So the same data will be used for SQL2k reporting and interfacing to legacy system. And data entry (OLTP) is separated from these. Correct?

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[rasETL]

Yes, the OLTP is separate on the Legacy Side.

We need to do this because they are replacing the Legacy System with an on-line server-based OLTP System ... but... they will be implementing the new System in phases. Therefore, we will be combining data from both OLTP Systems into a single Operational Data Store for Reporting and Interfaces.

 

[vongrunt]

And once ol' legacy system retires, data store will still contain defaults instead of NULLs?

Back to original question... wanna use something instead of NULL (unknown state), try at least to pick "unknown enough" value. For example, 0 may be OK for weight but not for temperature. Ditto for financial values, assuming that distinction between 0 and "not entered" doesn't exist. 1900-01-01 is first obvious choice for dates (it is "zero" date in SQL2k). Default on data types with narrow domain (bit) is sometimes questionable; use tinyint instead. Etc etc.

Whatever you choose, document it.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[rasETL]

The question is not what default values to use, but the best approach to having them inserted when the column is NULL.

Since we are using Informatica's tool, we could set up a number of trnasformations that convert the values, but we were hoping we could set them up on the Database through something like Enterprise Manager designor.

 

[JamesLean]

In SQL Server you can set default values on columns when creating the table:

CREATE TABLE t (
	col1 int NOT NULL DEFAULT 0,
	col2 datetime NOT NULL CONSTRAINT df_col2 DEFAULT GETDATE(),
	col3 varchar(20) NOT NULL
)

Note the two different forms, the first constraint will have a system-generated name, the second will have the specified name.

You can also add a default to an existing column using:

ALTER TABLE t
ADD CONSTRAINT df_col3 DEFAULT 'test' FOR col3

--James

 

[rasETL]

James ... Thanks for your comments ...

Correct me if I am wrong, but your suggestion, I believe, only works when you insert a record but do not specify anything for that column in the Insert statement.

We need to somehow convert the NULL, when it exists on a record, to a default value. I don't believe your examples will work - I think we will get an error message stating NULLS are not allowed.

What you propose, however, is the type of appraoch we would like to take.

 

[JamesLean]

Yes, if you explicitly try and insert a NULL value, the default will not kick in and the insert will fail.

If you are inserting the data by selecting from another table then you need to check for NULLs at that stage and convert them:

SELECT ISNULL(col1, 0), ISNULL(col2, 'no value')
FROM table

--James

 

[vongrunt]

How about replacing all NULLs with DEFAULT keywords during INSERTs?

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[rasETL]

We are trying to avoid having to write code to Insert the data since we are using Informatica. And we are trying to avoid having a set of transformations in Informatica that handle the "Default when NULL" situations.

Fortunately, the users are beginning to move more towards a "zero or spaces default value" attitude than before.

Someone at my shop brought up the use of "triggers" to maintain NULL values. While it is an alternative, it is, in my opinion, not worth even cosidering - too much overhead, etc. etc. etc.

Thanks for everyone's help so far!