January 13, 2006 at 2:27 am
Hi
Is there any efficient way to represent NULLS in the database? Agreed that NULLS are not the values, they are marker for absense of values. There will be obvious performence drop if I represent NULL with some value,but its ok.
I am searching for alternative to "NULL" for Numeric and Date data types, that value should behave as 'NULL'.
Any Idea ?
Thanks
Shrikant
Regards
Shrikant Kulkarni
January 13, 2006 at 6:30 am
You can not have any alternate value that will behave like NULL. You probably can work on some workaround that will compare one specific value of each data type and based on this use a different logic but....
First of all WHY?
What is wrong with NULLs?
Some people hate NULLs - I love them, they give you some extra options...
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
January 13, 2006 at 7:01 am
Hi
The reason i was working on some alternate value is 'NULL' breaks the boolean logic.
X & Y are NULL then,
We have (x=y) evaluate to false, now
(X!=Y) also evaluates to false, which should not happen.
I need something which will give the (x!=y) true will both are nulls.
This one of requirement in out application. Any suggestions are highly appreciated !!!
Regards
Shrikant Kulkarni
January 13, 2006 at 7:20 am
NULL basically means "I don't know what this value is".
Why not set your numeric columns to have 0 (zero) as the default? You could set your date columns to have 1900-01-01 as the default. (for the date, if you have years earlier than 1900 find your earliest year and pick one before that as the default).
-SQLBill
January 13, 2006 at 7:29 am
You could use
(x = y or (x is null and y is null)),
(x != y or (x is null and y is not null) or (x is not null and y is null))
for x=y and x!=y, respectively. Maybe embed it in a function, although this will hurt performance.
January 13, 2006 at 7:43 am
Apparently there is a designproblem here...
Could you expand on the purpose of this excercise? Perhaps with a complete example that demonstrates the behaviour you're looking for?
/Kenneth
January 13, 2006 at 8:30 am
Hi This was i was looking for, functionality of nulls shall be as follows. I think jesper had replied for that. Thanks...a lot ..
|
Operation |
Data Types |
1 is Null |
Both are Null |
|
= |
Numeric/Date |
FALSE |
TRUE |
|
Numeric/Date |
TRUE |
FALSE |
|
| ">
> |
Numeric/Date |
FALSE |
FALSE |
| =">
>= |
Numeric/Date |
FALSE |
TRUE |
|
Numeric/Date |
FALSE |
FALSE |
|
|
Numeric/Date |
FALSE |
TRUE |
|
|
+ |
Numeric |
Treat Null=0 |
Null |
|
- |
Numeric |
Treat Null=0 |
Null |
|
* |
Numeric |
Null |
Null |
|
/ |
Numeric |
If numerator is Null, Null. Otherwise, treat Null=0. |
Null |
|
AVG (same record) |
Numeric |
Average single value |
Null |
|
AVG (across 2 records) |
Numeric |
Average single value |
Null |
Regards
Shrikant Kulkarni
January 13, 2006 at 11:50 am
Maybe we are misunderstanding you and making this harder than it has to be....do you want to permanently change the values or just adjust for them in a script?
To permanently change them, use my suggestion.
To adjust for them in a script, consider using ISNULL.
WHERE ISNULL(Cola, 0) = ISNULL(Colb, 0)
-SQLBill
January 13, 2006 at 12:03 pm
Then you will also get "0 = NULL"-matches, which is probably not what you want....
January 14, 2006 at 2:07 am
Like Kenneth said, most probably there are some design issues here. Personally, I do not allow columns to have NULLs (or use any default value) and I do not design tables to store missing information. If you are going to use NULLs then you need to learn to live with them. SQL database systems do not have boolean logic, they have three-valued logic which is a big problem in many levels.
January 15, 2006 at 8:55 am
Is is "death by SQL" to change the Server Wide Setting but if you insist on a certain proc being able to evaluate IF NULL=NULL, then you can use SET ANSI_NULLS ON/OFF. Here's the excerpt from BOL...
"When SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN. When SET ANSI_NULLS is OFF, comparisons of all data against a null value evaluate to TRUE if the data value is NULL. If not specified, the setting of the ANSI nulls option of the current database applies. For more information about the ANSI nulls database option, see sp_dboption and Setting Database Options."
As many have warned in this thread, I believe that the need for changing this setting indicates fundamental flaws in either the way the database is setup or the logic instilled in the stored procedure. It would be far better to use ISNULL, COALESCE, and NULLIF in the equations and criteria than to tamper with the ANSI_NULLS setting.
--Jeff Moden
January 16, 2006 at 2:40 am
>> The reason i was working on some alternate value is 'NULL' breaks the boolean logic. <<
You're trying to apply a value from 3-valued logic (NULL) to 2-valued logic. Mathematically, it's a complete non-starter.
This is why it's best to avoid NULLs completely.
January 16, 2006 at 6:35 am
Maybe this approach can help.
To get a three value comparison on a BIT filed that can have NULLs use the CAST and ISNULL functions.
ISNULL(CAST(col0 AS TINYINT,2) = ISNULL(CAST(col1 AS TINYINT),2) this should create a three value scenario
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply