October 30, 2015 at 1:28 am
seware74 (10/29/2015)
I have always (or at least intended to) treat NULL and empty strings separately in my SQL querying history
This may not be for everyone! and may attract some flack ...
We took the decision decades ago not to store blank strings. We use triggers to force blank strings to NULL. Thus we never have to do
WHERE MyColumn IS NULL
OR MyColcumn = ''
which I assume has worse performance (given suitable indexes) than
WHERE MyColumn IS NULL
not to mention the need to remember to do it on every such occasion.
If we ever had the need to distinguish between a user's data entry of Empty String, and a form where the column was never actually provided to the user (i.e. thus NULL rather than EMPTY) then we would have to program an exception. In the last 20 years that hasn't happened, that I can remember ...
We have a 3rd party APP & DB that has this NULL OR EMPTY check. My tests suggest that Empty String is never actually stored, so the test is in their code just for belt-and-braces. I would like to add a filtered index to improve performance, but I can't due to the OR condition
October 30, 2015 at 4:43 am
Why not just use a check constraint or do you?
Gerald Britton, Pluralsight courses
October 30, 2015 at 4:59 am
g.britton (10/30/2015)
Why not just use a check constraint or do you?
Sorry - was that to me?
October 30, 2015 at 6:41 am
Hi Kristen,
Just wondering. Why did you go with that path and not the opposite by making all the columns not nullable? I believe that most theorists would prefer blank spaces instead of null values. If I remember correctly, using nulls also adds storage space needed.
October 30, 2015 at 7:01 am
Luis Cazares (10/30/2015)
Hi Kristen,Just wondering. Why did you go with that path and not the opposite by making all the columns not nullable? I believe that most theorists would prefer blank spaces instead of null values. If I remember correctly, using nulls also adds storage space needed.
Don't remember considering it, but with the benefit of 20:20 hindsight it would have been a good idea 🙂
One thing we do, as a side effect of not allowing, separately, EMPTY and NULL, is that our SAVE SProcs treat parameters that are NULL and EMPTY string differently.
The @parameters default to NULL, thus a given APP process does not have to provide values for all column parameters.
A data entry form might have, say, Address 1-to-5. When I modify an address I might put new values in Address1-3 and change Address4 from SomeValue to Empty.
The SAVE SProc takes the @Address4 = '' as an instruction to remove the value from that column (i.e. in our case that column is set to NULL).
However, if another parameter such as @TelephoneNumber is NULL then the pre-existing value is left unchanged - i.e. that Form, and thereby the EXEC of the SProc, is deemed not to include the Phone Number at all.
If we used EMPTY instead of NULL, as per your suggestion, we would have been able to just "save" all the values for @Parameters which were NOT NULL - without having to have use a CASE statement like:
SET MyColumn = CASE @MyColumn = '' THEN NULL ELSE @MyColumn
EDIT: Although in our case the Trigger would fix that if we actually saved the column as Empty String
Also, we would be able to do
WHERE TelephoneNumber = ''
instead of
WHERE TelephoneNumber IS NULL
which would definitely be easier for non-SQL-ists to understand. We have almost zero usage where users fire SQL at the database, but for the few clients that do we have to provide them with a proper understanding of NULL etc etc.
October 30, 2015 at 7:04 am
g.britton (10/30/2015)
Why not just use a check constraint or do you?
Yup -- a quick update from my phone! Anyway why not add a check constraint to these columns CHECK (col1 <> '') or do as Louis suggests, make them all NOT NULL?
In your case, NULL can mean two different things: unknown value and empty string (which is a known value). I see problems ahead
Gerald Britton, Pluralsight courses
October 30, 2015 at 7:06 am
I believe that most theorists would prefer blank spaces instead of null values.
Really? I understand the grief nulls can cause, but without them how do you know something is genuinely unknown as opposed to blank e.g. What's your middle name... NULL tells me I haven't asked that question, and blank tells me you haven't got one.
October 30, 2015 at 7:28 am
Ian Scarlett (10/30/2015)
I believe that most theorists would prefer blank spaces instead of null values.
Really? I understand the grief nulls can cause, but without them how do you know something is genuinely unknown as opposed to blank e.g. What's your middle name... NULL tells me I haven't asked that question, and blank tells me you haven't got one.
And that's exactly as I understand it. In my opinion, nulls and empty strings shouldn't be interchangeable.
But some people, like Chris Date, won't agree. https://en.wikipedia.org/wiki/Null_(SQL)#Criticisms
EDIT: Additional reference: http://www.dcs.warwick.ac.uk/~hugh/TTM/Missing-info-without-nulls.pdf
October 30, 2015 at 7:37 am
Luis Cazares (10/30/2015)
[And that's exactly as I understand it. In my opinion, nulls and empty strings shouldn't be interchangeable.]
OK, I can relax again... I was beginning to think you were a theorist instead of a realist for a minute 🙂
October 30, 2015 at 8:13 am
drew.allen (10/29/2015)
Sean Lange (10/29/2015)
g.britton (10/29/2015)
For that matter I wish we had true Boolean variables. But I dream.We do.
create table MyTrueBoolean
(
TrueBool bit not null
)
If you have a bit column and no value is provided would have it be True or False??? That is why the bit datatype allows NULL. With no known value it can't logically be either.
No we don't. Bit expressions cannot be used in place of Boolean expressions. Try the following:
DECLARE @MyPseudoBoolean BIT = 1;
SELECT GETDATE()
WHERE @MyPseudoBoolean
And you will get an error "An expression of non-boolean type specified in a context where a condition is expected, near '@MyPseudoBoolean'." Here the non-boolean type is bit, so a bit is not a boolean in T-SQL.
Drew
Neither is an "expression" - those are data types. The fact that other languages allow for you to use types and expressions interchangeably is frankly a defect or sloppiness on their end. (Note: I will fully admit that I've used it when it's there, but still - that's a notational thing that is fairly sloppy.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 30, 2015 at 8:24 am
g.britton (10/30/2015)
Anyway why not add a check constraint to these columns CHECK (col1 <> '')
We weren't trying to enforce NOT EMPTY but rather use EMPTY STRING (from APP) as a means of saying "Data WAS present on Form Field but [currently] has no value". In particular before the edit there might have been a value present, which has now been removed.
(Aside: I have a 3rd party APP we are using at present where if you remove a value for a formfield and save the record then the original value [for that column] reappears. Same sort of issue with their SAVE SProc presumably ... only theirs is bust! and we have lots of "." in columns where the value has been removed 🙁 )
I realise that NULL and EMPTY are not the same thing, and we debated long and hard about that (at the time ... but it was more than 15 years ago, and I don't remember the details of the conversation any longer!)
We must have discussed whether we needed to be able to distinguish between "Operator has never been shown a form with Column-X on it, therefore the value of NULL means we have never-known-a-value" as distinct from "Operator HAS been shown a form with Column-X on it and has provided the value EMPTY STRING", but we could not foresee a situation where we would care about the first case, hence we went down the route of only allowing one of the two scenarios. The primary reason was to be able to use @MyColumn = NULL as a (default) parameter to a SAVE SProc to indicate "Don't change current value" and @MyColumn = '' as "Value was, or has become, blank" which, in our case, we changed to NULL. But, sat here right now and considering Luis's suggestion, we might have been better off creating MyColumn as NOT NULL with a default of BLANK STRING instead (thereby saving the overhead of the NULL indicator flag on every VARCHAR column in every row in every table in the DB ...)
I see problems ahead
Not had any in the last 15+ years ... although I do appreciate that I'm non-conforming 😉
October 30, 2015 at 8:25 am
Neither is an "expression" - those are data types. The fact that other languages allow for you to use types and expressions interchangeably is frankly a defect or sloppiness on their end. (Note: I will fully admit that I've used it when it's there, but still - that's a notational thing that is fairly sloppy.
Couldn't disagree more!
Anyway what I miss in SQL are things like this:
CREATE TABLE foo(val char(4), IsValid boolean)
INSERT INTO foo(val, IsValid) values ('abcd', FALSE), ('defg', TRUE)
SELECT val FROM foo WHERE IsValid
DECLARE @myBool boolean;
SET @myBool = (2 > 1)
IF @myBool
....
WHILE TRUE
...
Gerald Britton, Pluralsight courses
October 30, 2015 at 8:40 am
Luis Cazares (10/30/2015)
Ian Scarlett (10/30/2015)
I believe that most theorists would prefer blank spaces instead of null values.
Really? I understand the grief nulls can cause, but without them how do you know something is genuinely unknown as opposed to blank e.g. What's your middle name... NULL tells me I haven't asked that question, and blank tells me you haven't got one.
And that's exactly as I understand it. In my opinion, nulls and empty strings shouldn't be interchangeable.
But some people, like Chris Date, won't agree. https://en.wikipedia.org/wiki/Null_(SQL)#Criticisms
EDIT: Additional reference: http://www.dcs.warwick.ac.uk/~hugh/TTM/Missing-info-without-nulls.pdf
Actually, my understanding of Chris Dates position would be to normalize the null-able column to a new table. Not simply replacing it with a default value of an empty string or 0.
October 30, 2015 at 8:47 am
Jason A. Long (10/30/2015)
Luis Cazares (10/30/2015)
Ian Scarlett (10/30/2015)
I believe that most theorists would prefer blank spaces instead of null values.
Really? I understand the grief nulls can cause, but without them how do you know something is genuinely unknown as opposed to blank e.g. What's your middle name... NULL tells me I haven't asked that question, and blank tells me you haven't got one.
And that's exactly as I understand it. In my opinion, nulls and empty strings shouldn't be interchangeable.
But some people, like Chris Date, won't agree. https://en.wikipedia.org/wiki/Null_(SQL)#Criticisms
EDIT: Additional reference: http://www.dcs.warwick.ac.uk/~hugh/TTM/Missing-info-without-nulls.pdf
Actually, my understanding of Chris Dates position would be to normalize the null-able column to a new table. Not simply replacing it with a default value of an empty string or 0.
Yes, I wasn't clear enough. That's why I shared the links. I don't totally agree with them, but it makes sense to a degree.
October 30, 2015 at 9:03 am
Chris Date is an academic purist...but we have real work to do and need to use nullable data types. The fundamental problem is that simple data types should not have three state logic. Making something nullable means it is really an object type with attributes and methods. My preference would be to implement only object types like javascript does albeit in a lightweight manner for "simple" data types. Sql chose to do the opposite and treat nullables as simple types.
If we had objects instead of simple types we could have much better control over rules, defaults, rounding methods, and validations
and state changes than we do now. Think of the power built into the new geographic types (objects) we have now.
I have replaced all chars in my databases with varchars because of the weird object-like method handling they have:
-- funky handling of chars
DECLARE @C char(5);
SET @C = 'ab ';
SELECT charindex(' ',@c) AS 'charindex', LEN(@c) AS 'len()', dataLENgth(@c) AS 'datalength()', 'x'+@c+'y' AS 'concat';
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply