Using CONVERT(...)

Question

Using CONVERT(...)

ram302

SSCommitted

Points: 1618
More actions
September 30, 2021 at 1:37 am
Go to Answer
#3934857
I'm reading over stored procedures that have been written and came across this:
```
CONVERT(NVARCHAR(32),HashBytes('MD5', 'Some unique combination of values here'),2)
```
Why choose NVARCHAR(32) over VARCHAR(32)?
Jeff Moden

SSC Guru

Points: 1004339
More actions
September 30, 2021 at 5:10 pm
Answer
#3935167

To Grant's point, we can only guess about such a thing on a totally out-of-context snippet of code.
That, notwithstanding, I have the following comments on this...
First, since (in most cases that I know of) HASHBYTES conversions to string (with the ",2)" format like you have) will only ever contain the letters of 0-9 and a-f. I don't know what those may look like in a language other than English but, for English and if it's stand alone, it's a total waste to use NVARCHAR() instead of VARCHAR().
But, hold on a minute, I can't actually think of a good reason to convert HASHBYTES to any kind of string to begin with for the usage that I've had to work with it in the past. Maybe when it needs to be included in a file for reconciliation on the receiver's end, but that's it.
The only way we could tell if you it's a really a mistake for one or more reasons is to actually see the rest of the code where it is used and to know what the business requirements actually are.
--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)

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

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

Cause you have more than one character set? That's the only reason I'd use it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

Or, another possibility, they're using that in another function and some functions require NVARCHAR .

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning