Hot answers tagged null
61
I think the question is poorly phrased, as the wording implies that you've already decided NULLs are bad. Perhaps you meant "Should we allow NULLs?"
Anyway, here is my take on it: I think NULLs are a good thing. When you start preventing NULLs just because "NULLs are bad" or "NULLs are hard", you start making up data. For example, what if you don't know my ...
47
COUNT(*) will include NULLS
COUNT(column_or_expression) won't.
This means COUNT(any_non_null_column) will give the same as COUNT(*) of course because there are no NULL values to cause differences.
Generally, COUNT(*) should be better because any index can be used because COUNT(column_or_expression) may not be indexed or SARGable
From ANSI-92 (look for ...
29
Let's say that the record comes from a form to gather name and address information. Line 2 of the address will typically be blank if the user doesn't live in apartment. An empty string in this case is perfectly valid. I tend to prefer to use NULL to mean that the value is unknown or not given.
I don't believe the physical storage difference is worth ...
16
Realistically, the requirement is crazy. Like all great crazy ideas, however, it is probably based on a nugget of potential reasonableness taken far out of context by people that have no understanding of the underlying rationale.
It can be reasonable to design a database schema such that no NULL values are allowed. If you do that, however, you are ...
15
In most DBs a NOT NULL column will be more efficient in terms of stored data for the reason you state, and also more efficient to query and index - so unless you want to allow NULLs in a column you should explicitly disallow them.
There will be a slight performance implication, as the extra NOT NULL constraints will potentially need to be checked for each ...
14
I disagree, nulls are an essential element of database design. The alternative, as you alluded too, would be a proliferation of known values to represent the missing or unknown. The problem lies with null being so widely misunderstood and as a result being used inappropriately.
IIRC, Codd suggested the current implementation of null (meaning not ...
14
You can do that in pure SQL. Create a partial unique index in addition to the one you have:
CREATE UNIQUE INDEX ab_c_null_idx ON my_table (id_A, id_B) WHERE id_C IS NULL;
This way you can have (1, 2, 1) and (1, 2, 2) and (1, 2, NULL) for (a, b, c) in your table, but none of these a second time.
Additional notes
No use for mixed case identifiers ...
14
Boolean logic - or Three valued logic
IN is shorthand for a series of OR conditions
x NOT IN (1, 2, NULL) is the same as NOT (x = 1 OR x = 2 OR x = NULL)
... is the same as x <> 1 AND x <> 2 AND x <> NULL
... is the same as true AND true AND unknown **
... = unknown **
... which is almost the same as false in this case as it will not pass ...
13
There is No Valid Reason to use a magic value instead of NULL. This might be the thought process of someone creating this mess. They write something like this:
SELECT c1, c2 FROM t1 WHERE c3 < 30;
When this doesn't return the results they are expecting, they realize that it does not include NULLs and would need to write this:
SELECT c1, c2 FROM t1 ...
11
I do not know about MySQL and PostgreSQL, but let me treat this a bit generally.
There is one DBMS namely Oracle which doesn't allow to choose it's users between NULL and ''.
This clearly demonstrates that it is not necessary to distinguish between both.
There are some annoying consequences:
You set a varchar2 to an empty string like this:
Update mytable ...
11
It depends on the domain you are working on. NULL means absence of value (i.e. there is no value), while empty string means there is a string value of zero length.
For example, say you have a table to store a person' data and it contains a Gender column. You can save the values as 'Male' or 'Female'. If the user is able to choose not to provide the gender ...
11
I'm afraid that the reason is simply that the rules were set in an adhoc fashion (like quite many other "features" of the ISO SQL standard) at a time when SQL aggregations and their connection with mathematics were less understood than they are now (*).
It's just one of the extremely many inconsistencies in the SQL language. They make the language harder ...
10
You can use
SELECT *
FROM A
INNER JOIN B
ON A.ID = B.ID
AND EXISTS(SELECT A.*
EXCEPT
SELECT B.*)
9
Fabian Pascal and Hugh Darwen have a brief exchange with someone asking the same question: “On The Nothing That's Wrong With NULLs”.
Established reasons are:
NULL is not a value, and therefore has no data type. Nulls need special handling all over the place when NULL is allowed.
NULL breaks two-value (familiar True or False) logic, and requires a ...
9
Take a look at PSOUG's notes on NULL. As Fabricio Araujo hinted, NULL is not really a value like the number 4 or string 'bacon strips'. In fact, NULL is untyped in the SQL language, which is why you cannot validly use it in an equality comparison. You need the special IS [NOT] NULL syntax to check if a value is NULL or not.
9
In any recent (ie 8.x+) version of Oracle they do the same thing. In other words the only difference is semantic:
select count(*) from any_table
is easily readable and obvious what you are trying to do, and
select count(any_non_null_column) from any_table
is harder to read because
it is longer
it is less recognizable
you have to think about whether ...
8
In a recent version there is indeed no difference between count(*) and count(any not null column), with the emphasize on not null :-)
Have incidentally covered that topic with a blog post: Is count(col) better than count(*)?
8
You need to rebuild the clustered index after making the columns sparse. The dropped columns still exist in the data page until you do this as can be seen with a query against sys.system_internals_partition_columns or using DBCC PAGE
SET NOCOUNT ON;
CREATE TABLE Thing
(
ThingId int IDENTITY CONSTRAINT PK PRIMARY KEY,
USER_CHAR1 nvarchar(150) null,
...
8
It's utter madness and there's no justification for it. NULL was created to represent the absence of a value & to use an actual value like -5000 is bonkers.
Ordinarily I wouldn't write an answer this short, but the question deserves to be one of the most visible on dba.se & the more answers the better.
7
From a purely relational point of view (prior to sixth normal form), I don't see any need to move a set of columns out into a separate table, just because they are frequently null.
As a trivial example, consider a customer account table with an end date as one of the columns - until the customer closes their account, the end date will be NULL. You are ...
6
Whether set null is useful or not depends on what you have chosen null to mean in the particular context - with all the confusion and opinion around null IMO the sensible approach is for the DBA to
Choose (and document) what it means for each nullable field
Make sure it means one thing only
With those rules, consider the following use case:
You have a ...
6
If you're looking at it purely from just what's better from a performance perspective ... I'd just test both of your methods and see what's faster for your data.
... but as you only have three states anyway, and this is going in a where clause, it might be even faster to just check that it's not the third case, and then you don't need the NVL test:
column ...
6
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';
SELECT @sql = @sql + '
' + QUOTENAME(name) + ' = CASE
WHEN ' + QUOTENAME(name) + ' = ''NULL'' THEN NULL ELSE '
+ QUOTENAME(name) + ' END,'
FROM sys.columns
WHERE [object_id] = OBJECT_ID('dbo.YourTableName')
AND system_type_id IN (35,99,167,175,231,239);
SELECT @sql = N'UPDATE dbo.YourTableName SET ' + ...
6
No, there is no way to tell SQL Server to treat all NULL float values as zero. You will have to surround these expressions with ISNULL() or, better yet IMHO, COALESCE(). You can do this in a view so you don't have to repeat it in every query.
5
In SQL Server, we have an connection setting to get =NULL to behave equally to IS NULL. But in latest versions is not recommended anymore - it's even marked as deprecated.
The recommended is the SQL Standard way - the IS [NOT] NULL operator.
(And I will not start an war whether 'NULL is a value or a status' here)... hehehe
5
One thing worth keeping in mind is that when you have a field that is not required, but any values that are present must be unique will require you to store empty values as NULL. Otherwise, you'll only be able to have one tuple with an empty value in that field.
There are also some differences with relational algebra and NULL values: NULL != NULL, for ...
5
This link has some interesting remarks about the NULL value, and as a database-agnostic answer, as long as you are aware of the potential affects of having NULL values for your specific RDBMS, they are acceptable in your design. If they were not, you wouldn't be able to specify columns as nullable.
Just be aware of how your RDBMS handles them in SELECT ...
5
Oracle it is in some sense some string type.
Thats what ADO Reader tells me. here is a Powershell Script:
[System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")
$ConnectionString = "Data Source=myTNS;User ID=myUSER;Password=myPassword"
$conn=new-object System.Data.OracleClient.OracleConnection
...
5
user_id, currency_id, and transaction_amount are all defined as NOT
NULL columns in dbo.transactions
It looks to me that SQL Server has a blanket assumption that an aggregate can produce a null even if the field(s) it operates on are not null. This is obviously true in certain cases:
create table foo(bar integer not null);
select sum(bar) from foo
-- ...
5
Martin Smith's answer will serve very well to get you all the columns you need for an entire database in SQL 2008. Very nice!
Here is how I did it in the days before SQL had CTEs and PIVOT. This will be compatible with older versions of SQL where Martin's solution won't work, and still works in 2008 as well, but with poorer performance than his solution.
...
Only top voted, non community-wiki answers of a minimum length are eligible
