Null or empty string use more space?

Hi all,
I have some columns in my database which allows null. I want to know if leaving the field to be NULL or storing an empty string into the field, which will take up more space?? if the field type is varchar(100)
No doubt, fields with empty strings take up way more space than their counterparts with NULL values......... have a look at this link for an example >>http://www.sqljunkies.com/WebLog/amachanic/archive/2004/10/22/4743.aspx|||

Hi Narayanas,

Thanks for your reply and solution. Now that i know storing an empty string will take up more spaces then leaving it Null, but if i have a table which has 4-5 fields that allows Null. Then how can i leave those columns to Null if the user don't enter anything for those string fields? e.g

CREATE PROCEDURE add_row
@.field1 varchar(100),
:
@.field5 varchar(100)

AS
IF @.filed1 = ''
INSERT INTO some_table (field2, ..., field5) VALUES (@.field2, ..., @.field5)
IF @.field2 = ''
INSERT INTO some_table(field1, field3, ..., field5) VALUES (@.field1, @.field3,... , @.field5)

If i do it this way then there will be too many combinations of IF statements. Can you show me some better ways to do this?

|||Are you seriously worried, in this modern age, about the extra space taken up with an empty string vs a null? I'd be more concerned about the problems and general confusion nulls create, let alone some potential performance problems with the database has to look up the null flag. If you want to use NULLs then all well and good, but treat them as a feature not a space saving device.
|||

kakusei wrote:

Hi all,
I have some columns in my database which allows null. I want to know if leaving the field to be NULL or storing an empty string into the field, which will take up more space?? if the field type is varchar(100)

You cannot pass an empty string to a Database NULL because ANSI SQL NULL which is database NULL is in an UNKNOWN value not an empty string. It evaluates mathematically to a NULL but it is an UNKNOWN value determined by the creator of the database. NET value types don't accept NULL the reason for DBNULL but SQL Server value types have to accept NULL to be ANSI SQL compliant. If it is Varchar (100) you use DBNULL. Space is not important but passing a Varchar (100) column an empty string will create data consistency problems in your database. Hope this helps.|||you can set default values at the time of defining the parameters in the stored proc.

CREATE PROCEDURE add_row
@.field1 varchar(100) = NULL,
:
@.field5 varchar(100) = NULL

AS
-- so you would need one insert statement.If there is a value in the variable
-- that will be inserted else null.
INSERT INTO some_table (field2, ..., field5) VALUES (@.field2, ..., @.field5)