SQL Server Null VS Empty String

Question

How are the NULL and Empty Varchar values stored in SQL Server  And in case I have no user entry for a string field on my UI  should I store a NULL or a

User · Answer

Be careful with nulls and checking for inequality in sql server.

For example

select * from foo where bla <> 'something'

will NOT return records where bla is null. Even though logically it should.

So the right way to check would be

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

Which of course people often forget and then get weird bugs.

User · Answer

How are the  NULL  and  empty varchar  values stored in SQL Server    Why would you want to know that  Or in other words  if you knew the answer  how would you use that information       And in case I have no user entry for a string field on my UI  should I store a NULL or a       It depends on the nature of your field  Ask yourself whether the empty string is a valid value for your field    If it is  for example  house name in an address  then that might be what you want to store  depending on whether or not you know that the address has no house name    If it s not  for example  a person s name   then you should store a null  because people don t have blank names  in any culture  so far as I know

User · Answer

There s a nice article here which discusses this point   Key things to take away are that there is no difference in table size  however some users prefer to use an empty string as it can make queries easier as there is not a NULL check to do   You just check if the string is empty  Another thing to note is what NULL means in the context of a relational database  It means that the pointer to the character field is set to 0x00 in the row s header  therefore no data to access   Update There s a detailed article here which talks about what is actually happening on a row basis     Each row has a null bitmap for columns that allow nulls  If the row in   that column is null then a bit in the bitmap is 1 else it s 0       For variable size datatypes the acctual size is 0 bytes       For fixed size datatype the acctual size is the default datatype size   in bytes set to default value  0 for numbers     for chars     the result of DBCC PAGE shows that both NULL and empty strings both take up zero bytes

User · Answer

The conceptual differences between NULL and  empty-string  are real and very important in database design  but often misunderstood and improperly applied - here s a short description of the two   NULL - means that we do NOT know what the value is  it may exist  but it may not exist  we just don t know   Empty-String - means we know what the value is and that it is nothing   Here s a simple example  Suppose you have a table with people s names including separate columns for first name  middle name  and last name   In the scenario where first name    John   last name    Doe   and middle name IS NULL  it means that we do not know what the middle name is  or if it even exists   Change that scenario such that middle name       i e  empty-string   and it now means that we know that there is no middle name   I once heard a SQL Server instructor promote making every character type column in a database required  and then assigning a DEFAULT VALUE to each of either     empty-string   or  unknown   In stating this  the instructor demonstrated he did not have a clear understanding of the difference between NULLs and empty-strings   Admittedly  the differences can seem confusing  but for me the above example helps to clarify the difference   Also  it is important to understand the difference when writing SQL code  and properly handle for NULLs as well as empty-strings

User · Answer

NULL is a non value  like undefined     is a empty string with 0 characters  The value of a string in database depends of your value in your UI  but generally  it s an empty string    if you specify the parameter in your query or stored procedure

User · Answer

if it s not a foreign key field  not using empty strings could save you some trouble   only allow nulls if you ll take null to mean something different than an empty string   for example if you have a password field  a null value could indicate that a new user has not created his password yet while an empty varchar could indicate a blank password   for a field like  address2  allowing nulls can only make life difficult   things to watch out for include null references and unexpected results of   and  lt   operators mentioned by Vagif Verdi  and watching out for these things is often unnecessary programmer overhead   edit  if performance is an issue see this related question  Nullable vs  non-null varchar data types - which is faster for queries

User · Answer

An empty string is a string with zero length or no character  Null is absence of data

User · Answer

NULL values are stored separately in a special bitmap space for all the columns   If you do not distinguish between NULL and    in your application  then I would recommend you to store    in your tables  unless the string column is a foreign key  in which case it would probably be better to prohibit the column from storing empty strings and allow the NULLs  if that is compatible with the logic of your application

[sql-server] SQL Server: Null VS Empty String

Examples related to sql-server