NULL Values in MS SQL

Couple of things I have come across recently with NULL values:

CONCATENATING when one field has NULL value
http://stackoverflow.com/questions/1035819/concating-null-fields

This can happen when you are building up a number of fields to compare against a search string.  Either COALESCE or ISNULL will do the trick.

WHERE COALESCE(p.field1,'') + COALESCE(p.field2,'') + COALESCE(p.field3,'') LIKE @SearchText

SQL NOT IN constraint and NULL values
http://stackoverflow.com/questions/129077/sql-not-in-constraint-and-null-values

OPPOSITE OF COALESCE
http://stackoverflow.com/a/4208280

COALESCE(field1,'') + (CASE WHEN field2 IS NULL THEN '' ELSE ' | ' + field2 END)