SQL Server: Null VS Empty String

Question

How NULL and Empty Varchar are stored in SQL Server. And in case I don't have a user record for the string field in my user interface, should I store NULL or '' ?

Answer 1

There's a good article here that discusses this issue. The key point to catch is the lack of a difference in the size of the table, however, some users prefer to use an empty row, as this can simplify queries because there is no NULL check. You just check if the string is empty. It should also be noted that NULL means in the context of a relational database. This means that the pointer to the character field is set to 0x00 in the line header, so there is no access to data.

Update There is a detailed article here that talks about what actually happens based on a series of

Each row has a null bitmap for columns that allow null values. If the row in this column is zero, then the bit in the bitmap is 1, otherwise it is 0.

For variable size data types, the actual size is 0 bytes.

For a fixed-size data type, the actual size is the default data type size in bytes, set to the default value (0 for numbers, `` for characters).

The DBCC PAGE result shows that both empty and empty rows occupy zero bytes.

Answer 2

Be careful with zeros and inequality checking in sql server.

for example

 select * from foo where bla <> 'something' 

Will NOT return records where bla is null. Although logically it should be.

So the correct way to check would be

 select * from foo where isnull(bla,'') <> 'something' 

Which, of course, people often forget, and then get strange mistakes.

Answer 3

An empty string is a string with zero length or without a character. Null - lack of data.

Answer 4

The conceptual differences between NULL and the empty string are real and very important in database design, but are often misunderstood and misapplied - here is a brief description of two:

NULL - means that we DO NOT know what a value is, it may exist, but it may not exist, we simply do not know.

Empty-String - means that we know what the value is and that it is nothing.

Here is a simple example: Suppose you have a table with the names of people, including separate columns for first_name, middle_name and last_name. In a scenario where first_name = 'John', last_name = 'Doe' and middle_name IS NULL, this means that we do not know what the middle name is, or even it exists. Change this script so that middle_name = '' (i.e., an empty string), and now that means we know that there is no middle name.

I once heard a SQL Server instructor advance the creation of each character type column in a required database, and then assign a DEFAULT VALUE value to each of the "(empty row)" or "unknown". Pointing this out, the instructor demonstrated that he did not have a clear understanding of the difference between NULL and blank lines. Admittedly, the differences may seem confusing, but for me, the above example helps clarify the difference. In addition, it is important to understand the difference when writing SQL code and correctly handle NULL as well as empty strings.

Answer 5

NULL values ​​are stored separately in a special raster space for all columns.

If you do not distinguish between NULL and '' in your application, I would recommend that you store '' in your tables (if the row column is not a foreign key, in which case it would probably be better to prevent the column from storing empty rows and allow NULL, if it is compatible with your application logic).

Answer 6

NULL is not a value, such as undefined. '' is an empty string with 0 characters.
The value of the row in the database depends on your value in the user interface, but this is usually an empty string '' if you specify a parameter in your query or stored procedure.

Answer 7

if this is not a foreign key field without using blank lines, this can save you some trouble. only null is allowed if you accept null to mean something other than an empty string. for example, if you have a password field, a null value may indicate that the new user has not yet created his password, and an empty varchar may indicate an empty password. for a field of type "address2", which allows a null value, it can only complicate life. things to pay attention to include null references and unexpected results from the = and <> operators mentioned by Vagif Verdi, and watching these things is often unnecessary overhead programmers.

edit: if performance is a problem, see this related question: Zero or non-zero varchar data types - which is faster for queries?

Answer 8

How NULL and empty varchar values ​​stored in SQL Server are stored. Why do you need to know this? Or, in other words, if you knew the answer, how would you use this information?

And if I don't have a user record for a row field in my user interface, should I store NULL or ''? It depends on the nature of your area. Ask yourself if an empty string is a valid value for your field.

If this (for example, the name of the house in the address), then this may be what you want to keep (depending on whether you know that the address does not have a name for the house).

If this is not the case (for example, the name of a person), then you should keep zero because people do not have empty names (in any culture, as far as I know).