Any better ways to ascertain whether a column in a table is empty or not?

Question

Hi

I have a table say T in SQL Server 2005 database and it has two columns say A and B, which more often than not won't have any values in them. How to check whether A and B are empty (has all zero length strings) or not?

I have this naive way of doing it -

select count(*) as A_count from T where A <> ''

Let's assume A has data type varchar.

I was wondering whether I can get the same information using a system table, and if so would that be faster than this query?

cheers

Answer 1

Your method is essentially correct, although the wording in your question is imprecise. Does empty include NULL or a non-zero length empty string?

You could handle those cases with:

select count(*) as A_count from T where isnull(rtrim(ltrim(A)), '') <> ''

Also, make sure there is an index on column A.

Answer 2

If we are talking about zero-length strings, then this is the way that I would do it:

select count(*) as A_count from T where LEN(A) > 0

Keep in mind, however that if A could be null, then these rows will not be caught by either LEN(A) > 0 or LEN(A) = 0, and that you will have to wrap an ISNULL around A in that case.

Answer 3

If your column is nullable, you will have to modify your query as follows:

select count(*) as A_count from T where COALESCE(A, '') <> ''

otherwise you will not count nulls.