For some background on this, let me refer you to Michael Kaplan's excellent post at http://blogs.msdn.com/michkap/archive/2005/01/11/350482.aspx.
Sometimes you will be an environment in which string comparisons don't quite work like you expect them to. One thing to quickly rule out is that your strings actually have the values you think you're looking for.
cast('(text)' as nvarchar(max)),
cast(0x00 as varbinary(max))
select firstname, cast(firstname as varbinary(max))
from person.contact where contactid = 2
select N'Catherine', cast(N'Catherine' as varbinary(max))
select 'Catherine', cast('Catherine' as varbinary(max))
If you run with on the AdventureWorks database, you'll get these results back
All I'm doing here is showing the encoding for the string 'Catherine' as it appears in the database, and the one I'm typing into SSMS. Everything agrees, which is all good.
For fun, I've also added the non-Unicode varbinary representation. If you're dealing with Unicode and non-Unicode strings, you can try encoding strings one way or another to see if all the things are working correctly. Note that SQL Server will do conversions for you automatically - in my case,
select * from person.contact where firstname='Catherine'
is equivalent to
select * from person.contact where firstname=N'Catherine'
This trick is useful to look for exact matches - for a whole lot more of information, you can read up on 'code pages [SQL Server], about code pages' on BOL (also available at http://msdn2.microsoft.com/en-us/library/ms143726.aspx).