Should NULLS be handled in code or in the database? Advantages and Disadvantages?
c#, database-design, sql-server, t-sql
Solution
My general advice is to declare fields in a database as `NOT NULL` unless you have a specific need to allow `null` values as they tend to be very difficult for people new to databases to handle.
Note that an empty string and a null string field do not necissiarly mean the same thing (unless you define them to). Often null means "unknown" or "not provided", whereas an empty string is just that, a provided and known empty string.
Allowing or disallowing `null` fields depends entirely on your needs.
Problem
I have several questions regarding where to handle nulls. Let me set up a scenario. Imagine I have a table that has 5 varchar(50) columns to use as an example when providing reasons for using nulls or empty strings. Is it better to handle NULLS in code or in the database? By this I mean, is it better to assign an empty string to a varchar(50) if it contains no value or is it better to assign null to the varchar(50) and handle that null in code? Does assigning an empty string to a column affect performance overhead? How does using a null vs. an empty string affect indexing? I am under the impression that if you do not allow your database to contain nulls, you do not have to handle it in code. Is this statement true? Do other datatypes besides varchars pose the same problems when using a default value or is it more problematic with string datatypes? What is the overhead of using the ISNULL function if the table contains nulls? What are other Advantages/Disadvantages?