Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase «««123

Comparing a hash Expand / Collapse
Posted Wednesday, February 3, 2010 1:07 PM



Group: General Forum Members
Last Login: Today @ 1:49 AM
Points: 7,765, Visits: 11,372
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:
Post #858982
Posted Thursday, February 4, 2010 5:18 AM

SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing 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
Post #859532
Posted Tuesday, March 30, 2010 10:07 AM

SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, November 11, 2016 6:39 AM
Points: 9,932, Visits: 11,346
Everything that has been observed, regarding overflows, truncations, ELSE clauses executing and so all documented in Books Online, under the following topics:


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
Post #892995
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse