zero length string in SQL server 2000 3

[seaport]

According to some book about the SQL server 2000, a table should contain less null value as possible. I found out that the table in SQL server accept zero-length string. Therefore, to elimiate null values, I plan to update all text fields in the table to be zero-length string, if they are null.

Am I doing right? Is there any side effect?

Thanks in advance.

seaport

 

[SQLSister]

I've never noticed null vallues harming anything as long as a null value was appropriate for the field involved. You don't want to allow the data entry of nulls for fields which must have a value which is done when you set up the tables. But if entering the data is optional, then I don't see where a null would hurt.

One consideration of setting all your null values to the empty string is that the programmers who are programming applications against your database might not be checking for the empty string and some of their program code may start to bomb. Also, the code which sends data to any tables will need to set any text fields to the empty string at the time the data is entered or you need to write triggers to do so. Otherwise, you will be have null values in some records and empty strings in others.

All in all, I think you are better off making sure the correct constraints are on your data entry forms and in your table definitions than in trying to convert nulls to empty strings.

 

[StewartJ]

Nuls impact performance and require explicit coding. Just set a default of an empty string for the column.

 

[nakkii]

I have run into an issue where I have a SQL procedure:

ISNULL(emp_name_alias,emp_name_first)

Where if the "emp_name_alias" is a zero length string this statement fails to accomplish its task.

 

[tlbroadbent]

The statement doesn't fail to do its task. NULL and empty string a different. NULL means no value has been stored in the column. Empty string is a value. You have to code differently for empty string. The following is one way to handle both Null and Empty string conditions.

FirstName = Case When IsNull(emp_name_alias,'')='' Then emp_name_first Else emp_name_alias End Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[nakkii]

tlbroadbent,

Thanks I realize that the statement is functioning as it is suppose to but I needed it to return the firstname if the alias was NULL (or a zero length field).

This field is originally a NULL value but when it is updated via the VB front end application and then the value is then deleted from the VB front end application, it doesn't return back to a NULL instead it becomes a zero length string which now my "ISNULL" does not function. I have tried to get the VB app to update the field with a "NULL' if the bound control is a zero length string but had no luck there either.

I was looking at other posts and found a solution that someone suggested for a similar problem:

ISNULL(NULLIF(emp_name_alias,''),emp_name_first)

Not sure which is better your suggestion or this one.

 

[tlbroadbent]

I prefer the solution you found in this case. Thanks for posting it. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[nakkii]

I am interested in the comments about SQL performance being affect by NULL values in the tables vs. zero length strings. Does anyone have any more information on this? Books? Links? etc.?

Thanks.