Kevin Gill (10/28/2009)
My example was just a simplified version where you might be comparing foreign keys which map to identity columns and thus can never validly be negative.
Identity columns can easy be negative. Create them with a negative seed, or with a negative increment, or both is one way. setting identity insert on and placing explicit negative values is another. They are quite common when a large range is needed (bigger than 2 billion) - an identity column starting with seed 2**-31 and increment +1 gives the maximum possible range of values.
Saying 'it isn't something you should use' seems a little black and white when it's perfectly valid in many situations...
OK, maybe it should have been "it isn't something you should use unless you are absolutely certain you can pick a null-replacement value that is guaranteed to be safe". But remember that Murphy's law applies, so that most people will sometimes choose an "absolutely guaranteed safe" value that isn't safe at all.
Tom