Hashbyte Generating All Nulls

Question

Hashbyte Generating All Nulls

ArsenalFan

SSC Veteran

Points: 205
More actions
December 9, 2014 at 8:31 pm

#293383

Hi All,
One of my requirements is to generate a unique id for a source that has about 40 odd columns of which I need 30 odd columns for this. Identity is not an option, unfortunately. All the columns set on this table to varchar(255)
My syntax is basically hashbytes ('MD5',col1+col2+col3+col4......). I set this as a Persistent computed column.
This works perfectly when I use an identical copy of my actual table but with fewer records (a few thousand instead of a few million). On the actual table, every single record has a null for this computed column. I did a simple select for it instead of a computed column and that too came back with just nulls.
I have thought of two things - maybe hashbyte will take the set size of the column(s) instead of the true size and since 255x30 is a lot bigger than 8000, it comes back with null having failed the size option? But if that were true, why does it work on a smaller data set with the same exact column size?
Suppose one of the columns has a NULL in the col1+col2+col3... for a record. Would Hashbyte generate a null if a single column, in a list of concatenated columns, has a null value?
Haven't been able to fix the issue, so any help will be appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 1

Null + Anything = NULL

... but that may not be the problem because HASHBYTES will return an error on a "void", which would be produced by a NULL concatenation.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 2

Nulls may be of different types. So nulls in some columns may be a problem. HASHBYTES raises an error on 'untyped' null. Compare

select HASHBYTES('MD5', ''+null);

select HASHBYTES('MD5', cast(null as char(1)));

select HASHBYTES('MD5', null);

ArsenalFan SSC Veteran Points: 205 More actions · Answer 3

Thanks for your help.

Indeed, it was null causing the issue. I checked with hashbytes('md5', 'hello'+'') and hashbytes('md5', 'hello'+null)

column names wrapped within isnull fixed the issue.