Friday, September 19, 2008

Think about what you are doing!

Last statement in a stored procedure running in production:

SELECT @ReturnStatus = @@ERROR
IF(@ReturnStatus <> 0)
   RETURN (@ReturnStatus)

Making things as simple as possible isn't just for self-congratulatory back-patting. It's because the next time you're looking at the code it will make it easier to understand what it's doing. So let's make it simple:



winner said...

Hello, E. Squared. I hope you watch my comment. I saw your reply about base64 with SQL in TEK-TIPS(
I am Woo-seung Kim in Republic of Korea. I want to use your function ToBase64 for my job. Could you allow me and my company to use it for commercial business.
I don't ask you to buy the copyright, now. I just want to get the license to use it freely.

winner said...

I have a guestion. Why the result of your ToBase64 is different from MS SQL XML base64(
Does MS SQL XML base64 have another paddings?

ESquared said...

I see you're referring to the ToBase64 function I posted. You're welcome to use it in any way you like.

The reason that Microsoft's version is different is because the values are converted to XML first, before being changed to Base64.

Check back in a while and I will post a new blog entry with a FromBase64 function that you can use to prove this to yourself. I'll also post an improved version of ToBase64 and give an example of how to do the conversion without a function, for use with a large rowset and where performance is important.

winner said...

Thank you for you comment.
Unfortunately, the result of your function is also different from Ruby source that I wrote for testing.
I considered your function was wrong

Where is your new blog?
I want to watch your new function.

ESquared said...

I've checked and double-checked. My code is correct. This online base64 calculator agrees.

I am still working on the function. I tried a set-based method, but it turned out to be slower, though I have more ideas...

winner said...

Oh~ sorry. You're correct. Your ToBase64 has no bug. When I used your ToBase64 with MS SQL 2008, I got the right result. But I couldn't get it with MS SQL 2005.
My query uses HashBytes before using Base64. XML base64 of MS SQL gets the parameter of VARBINARY type. But your ToBase64 gets the parameter of VARCHAR type. So, MS SQL converts the variable from VARCHAR to VARBINARY before calling your ToBase64. And MS SQL 2005 seemed to have bugs in the CONVERT and CAST with control characters.
I modified ToBase64 as follows:
@Input varchar(6000) -> @Input VARBINARY(MAX),
Len(@Input) -> DATALENGTH(@Input)
Len(@Bits) -> DATALENGTH(@Input).

Finally, I got the same result of MS SQL XML base64.

And the performance of ToBase64 and MS SQL XML base64 are same.

I know it that base64 was invented for transfering binary data with ASCII. How about accepting my patch?

I think your ToBase64 conforms SQL standard. If it is true, I prefer your ToBase64.

ESquared said...

You're right. I noticed when I was working on it that I should have made my function accept varbinary. The modifications you suggested are exactly right. Also, I noticed just today when I was looking through my open SQL windows that in fact my previous statement about the MS article was doing the conversion correctly, not to XML, and that it was simply the varbinary.

I will try to keep working on the function, but in the meantime, you can get a really good performance improvement by changing the series of SET statements to just one SET statement (each next character's expression just with a plus sign before it). Sorry I haven't been able to get to this, but I've been really busy and had other distractions.

ESquared said...

Another thought... now I would name the function differently: Base64FromBinary. I also have a new BinaryFromBase64 function. I'll try to post it soon when I get back to the computer where it is.