Comparing a hash

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by alastors » Comparing a hash

23 posts, Page 3 of 3

«««1 2 3

Comparing a hash

Rate Topic

Display Mode

Topic Options

Author

Message

Hugo Kornelis

Posted Wednesday, February 03, 2010 1:07 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 3:22 AM
Points: 5,244, Visits: 7,059

john.arnott (2/3/2010)

The real problem is that REPLICATE uses the same input as output data type, which in this case has an 8000-byte limit, and that longer values are truncated.

I would have thought that although this reasonable (if not obvious) behavior of REPLICATE led to the two HASHBYTES calls evaluating identical strings in the original QOD, the real problem is that when HASHBYTES fails due to input-overflow (when we feed it a varchar(max), it apparently does not present a result of NULL that may be tested. Rather, as I showed in my previous post, an IF statement testing the output of HASHBYTES goes to the FALSE branch regardless of whether you test for "IS NULL" or for "IS NOT NULL". That would seem more of a candidate for bug status.

That's the real problem that surfaxced in this discussion; the problem exposed by the question (which I believed David to be responding to) is not related to the input-overflow behaviour.
And I agree that the overflow behaviour is a lot more bug-worthy than the 8000-character limit on varchar data.

Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Post #858982

Bhavesh_Patel

Posted Thursday, February 04, 2010 5:18 AM

SSChasing Mays

Group: General Forum Members
Last Login: Friday, January 11, 2013 12:41 PM
Points: 621, Visits: 297

Good Question ... there is always something new to learn

Bhavesh Patel

http://bhaveshgpatel.wordpress.com/

Post #859532

Paul White

Posted Tuesday, March 30, 2010 10:07 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 1:49 AM
Points: 10,990, Visits: 10,540

Everything that has been observed, regarding overflows, truncations, ELSE clauses executing and so on...is all documented in Books Online, under the following topics:

SET ANSI_WARNINGS
SET ARITHABORT
SET ARITHIGNORE

The various behaviours described in this thread can all be varied, and explained, by reading those references.

The other thing is that a scalar sub-query like in the example (SELECT a FROM @tab) always returns a value - since it returns a column reference. If no rows are produced, the scalar sub-query presents a NULL.

Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #892995

« Prev Topic | Next Topic »

23 posts, Page 3 of 3

«««1 2 3

Permissions