Nulls in SQL 2005

Hello,

I have a varchar(20) field which stores a value or empty string.

I have noticed that if the value is NULL and I use is null in my query, the query runs very fast. However If I also include in my query a check for an empty string (len(rtrim(field)) = 0) the query is much slower.

Is it a good idea to force the empty strings to null and if Yes is there an automatic way in sql 2005 (maybe a trigger) to update all empty strings to null.

Thanks, I just need some advice.

Michael

The difference may be coming from the use of functions than checking for an empty string versus a null string. The optimizer cannot make use of an expression in the same way as it makes use of a field.

Using a trigger to turn blanks into null is not a bad idea, but it is not without overhead either. Also consider whether a blank string represents something different than a NULL. You might also want to revisit your data model and see whether it makes more sense to put the value in a separate table and remove the entry when it is not necessary.

|||One more option is to create computed column and index over it, but there are some requirements for that (refer for details: http://msdn2.microsoft.com/en-us/library/ms189292.aspx )|||

HI

U can also use this query

select * from table1 where col1 is null or col1 = ''