Is converting everything to varbinary the correct way to compare character data?

Question

I was using a MIN function to compare character data in a column that allowed nulls, with disastrous results. ;-) Here's a much simplified example that shows the same kind of thing:

1. Determine the number of rows in sys.indexes:

   select count(*) from sys.indexes;
   

2. Run this SQL:

   select count(name), substring(cast(min(name) as varbinary),1,1) 
     from sys.indexes;
   

   If count matches #1, stop here. Try a different database (with more objects) instead.

3. Go to the Messages tab. You should see: Warning: Null value is eliminated by an aggregate or other SET operation.

4. How would you handle the nulls in the "name" column in order to resolve that Warning? with coalesce?

5. Replace "name" with "coalesce(name,char(0x7e))" and run the SQL:

   select count(coalesce(name,char(0x7e))), 
     substring(cast(min(coalesce(name,char(0x7e))) as varbinary),1,1) 
     from sys.indexes;
   

6. Note the result of the MIN function in #5 (0x7e rather than 0x63).

Questions:

A. Is coalesce the proper way to handle the Warning and missing (null) data per #4?

B. Since the result in #6 is not what's expected, what is the correct way to compare character data in SQL Server? Convert everything to varbinary?

[Edited...]

In the discussions below, there was some confusion and discussion about the relationship between the null replacement via COALESCE and the results of the comparisons. The relationship between the two is this: when you select a string (including a single character) as a null replacement placeholder (steps #4 and #5 above), that string must satisfy the expected results of the comparison(s) that are being performed against values of other data in the query. With some collations, finding a suitable string may be more difficult than with other collations.

Answer 1

EDITED AND UNDELETED

Answer to A.: Yes, or you can use ISNULL() with the same result as COALESCE() in this case.

Answer to B.: Do not convert a varchar to a varbinary to compare them, but understand the collation sort order when using aggregates.

I think this code snippet answers the count with NULL problem, but I'm still a little confused about the question:

select count(*) from sys.indexes; 
-- 697 results
go
select count(isnull(name,'')) from sys.indexes; 
-- 697 results
go
select count(name) from sys.indexes; 
-- 567 results
go

And this gets the count of records for the MIN name field (based on collation and SQL sort order of string fields):

select  i.name
        ,subCnt.Cnt
from    (select min(name) as name from sys.indexes) as i
join    (select name, count(*) as Cnt from sys.indexes group by name) as subCnt
on      subCnt.name = i.name;

And this query explains the aggregate sort order and why the above query chooses the value returned in the name field:

select name, row_number() over (order by name) from sys.indexes order by name;

And this query shows my collation's (Latin1_General_BIN) sort order even when replacing NULLs with char(0x7E):

select  coalesce(name,char(0x7e))
        , row_number() over (order by coalesce(name,char(0x7e))) 
from    sys.indexes order by 2;

And this shows the sort order difference between collations in SQL Server (which determines what is MIN or MAX in a string field):

declare @test table (oneChar char(1) collate Latin1_General_BIN
                    , oneChar2 char(1) collate SQL_Latin1_General_CP1_CI_AS
                    , varb varbinary)

insert into @test (oneChar)
select 'c' union all
select '~' union all
select 'P' union all
select 'X' union all
select 'q' union all
select NULL

update @test set varb = cast(isnull(oneChar,char(0x7E)) as varbinary), oneChar2 = oneChar

select min(oneChar) from @test -- 'P'
select min(oneChar2) from @test -- '~'
select min(varb) from @test  -- 0x50, the varbinary equivalent of oneChar

And if you want the count of all rows and you want the MIN() of the name without considering the NULLs (and not seeing a warning, for whatever reason), use this:

select  i1.Cnt
        ,i2.name 
from    (select count(*) as Cnt from sys.indexes) as i1
        ,(select min(name) as name from sys.indexes where name is not null) as i2

And whatever you do, certainly don't cast a whole field as a different collation just to do some filtering. This question belongs in the discussion forums, not as a simple question/answer.

Answer 2

I'm assuming there is a reason you couldn't use ISNULL doing something along the lines of: ISNULL(MyField,'Some String I will know is a null')

p.s. be careful with the performance of this on large datasets in a production environment depending on what you are doing.