Which is faster: NULL or NOT NULL (with an empty string)?

Question

I have a table with fields NOT NULL and they end up being empty.

What’s the difference between a NULL (allows null values) and NOT NULL with an empty string ('') within?

Answer 1

There is no way to answer this question abstractly, because this has nothing to do with performance. Performance will happen if everything is done right within the needs. If you need one of them and not the other, no matter the performance.

There may be different performance by circumstances. The question should be semantic.

Some say you never need a NULL, but it can lead to iron and fire problems because it involves normalizing in excess and requiring complex operations to get the data the way you need it.

NULL means worthless, and blank means it has an empty value, they are different things.

Answer 2

The difference is that a field NULL can be null, ie it is not required to have any value. And a field NOT NULL needs some value in the column, be a string.Empty or a "".

I believe if you have a field NOT NULL and inserts a string empty inside, something is wrong in its architecture, because of nothing worth having a field NOT NULL if at some point you will insert an empty value in there. In that case it would be better to turn this field into NULL.

When you enter an empty value in some column in the database you accept NULL, be that value a string.Empty or "", He understands that this is an empty value, that is, it has a value there, but nothing inside. In this case, you are occupying seat space, no matter how small, to store an empty value.

So the bank can keep showing NULL and stay NULL definitely, you enter a value NULL the bank understands. Something like:

 command.Parameters.AddWithValue("@paymentDate", item.Renavam ?? (object)DBNull.Value);

That is, if the item.Renavam is null, inserts a value null to the bank, which is the DBNull.Value. This way, your column will still look like NULL.

Answer 3

In a short answer I would say that you should opt for NULL in columns that do not require value even for practical reasons, for example you cannot enter an empty "" value in a date field for example, you will have to keep converting before inserting

Example:

INSERT INTO TABELA (CODKEY, DATA_CAD) VALUES (1, '');

I don’t know all the banks but most have to be:

INSERT INTO TABELA (CODKEY, DATA_CAD) VALUES (1, null);

And if you do:

SELECT * FROM TABELA WHERE (DATA_CAD = '');

You will get no results or receive a conversion error

The correct is

SELECT * FROM TABELA WHERE (DATA_CAD IS NULL);

The same may occur with other data than STRING data type INT

Although your question refers to performance, I think the difference in properly indexed fields is practically null, so I would take more into account the standardization of the data in this case.