Joe Obbish takes a look at how HASHBYTES doesn’t scale well:
The purpose of the
MAXaggregate is to limit the size of the result set. This is a cheap aggregate because it can be implemented as a stream aggregate. The operator can simply keep the maximum value that it’s found so far, compare the next value to the max, and update the maximum value when necessary. On my test server, the query takes about 20 seconds. If I run the query without theHASHBYTEScall it takes about 3 seconds. That matches intuitively what I would expect. Reading 11 million rows from a small table out of the buffer pool should be less expensive than calculating 11 million hashes.From my naive point of view, I would expect this query to scale well as the number of concurrent queries increases. It doesn’t seem like there should be contention over any shared resources, so as long as every query gets on its own scheduler I wouldn’t expect a large degradation in overall run time as the number of queries increases.
Joe’s research isn’t complete, but he does have a conjecture as to why HASHBYTES doesn’t scale well. That said, the most interesting thing in the post to me was to see Microsoft potentially using bcrypt under the covers for HASHBYTES calculation—if that’s really the case, there actually is a chance that sometime in the future, we’d be able to generate cryptographically secure hashes within SQL Server rather than the MD5, SHA1, and SHA2 hashes we have today.