Skip to main content
21 votes
Accepted

What is a scalable way to simulate HASHBYTES using a SQL CLR scalar function?

Since you're just looking for changes, you don't need a cryptographic hash function. You could choose from one of the faster non-cryptographic hashes in the open-source Data.HashFunction library by ...
Paul White's user avatar
  • 86.4k
17 votes
Accepted

How is it possible for Hash Index not to be faster than Btree for equality lookups?

Disk based Btree indexes truly are O(log N), but that is pretty much irrelevant for disk arrays that fit in this solar system. Due to caching, they are mostly O(1) with a very large constant plus O((...
jjanes's user avatar
  • 40.2k
17 votes

What is a scalable way to simulate HASHBYTES using a SQL CLR scalar function?

I'm not sure if parallelism will be any / significantly better with SQLCLR. However, it is really easy to test since there is a hash function in the Free version of the SQL# SQLCLR library (which I ...
Solomon Rutzky's user avatar
15 votes
Accepted

Using HASHBYTES() yields different results for nvarchar and a variable

If you want a quoted string to be NVARCHAR (treated as Unicode), you need to prefix it with N. DECLARE @hash NVARCHAR(MAX) = 'password5baa61e4c9b93f3f0682250b6' SELECT HASHBYTES('SHA1', N'...
Erik Darling's user avatar
  • 41.9k
13 votes

How is it possible for Hash Index not to be faster than Btree for equality lookups?

The why? issue is already addressed by other answers, but I question whether the premise is still correct. Things have moved on in Postgres since 9.6. Hash indexes are now first-class citizens (as ...
qris's user avatar
  • 341
12 votes

What is a scalable way to simulate HASHBYTES using a SQL CLR scalar function?

This isn't a traditional answer, but I thought it would be helpful to post benchmarks of some of the techniques mentioned so far. I'm testing on a 96 core server with SQL Server 2017 CU9. Many ...
12 votes
Accepted

Is left hash join always better than left outer join?

Semantically, both queries are the same. The LOOP versus the HASH simply tells SQL Server which option to use to return results. If you run the query without LOOP or HASH, SQL Server may pick either ...
Hannah Vernon's user avatar
  • 70.2k
11 votes
Accepted

What is the algorithm behind the EXCEPT operator?

What is the internal algorithm of how the Except operator works under the covers in SQL Server? I wouldn't say that there's a special internal algorithm for EXCEPT. For A EXCEPT B, the engine takes ...
Joe Obbish's user avatar
  • 32.5k
11 votes
Accepted

Hash aggregate bailout

Hash join and hash aggregate both use the same operator code internally, though a hash aggregate uses only a single (build) input. The basic operation of hash aggregate is described by Craig Freedman: ...
Paul White's user avatar
  • 86.4k
10 votes
Accepted

Best Data Type to Store Result of HASHBYTES('MD5', ...)

According to Books Online: The output conforms to the algorithm standard: 128 bits (16 bytes) for MD2, MD4, and MD5; 160 bits (20 bytes) for SHA and SHA1; 256 bits (32 bytes) for SHA2_256, and ...
db2's user avatar
  • 9,668
10 votes

How is it possible for Hash Index not to be faster than Btree for equality lookups?

Hash lookup is theoretically an O(1) operation when the key hash maps directly to the physical location of the target record. The way it works in Postgres, if I understand it correctly, is a bit more ...
mustaccio's user avatar
  • 26.3k
8 votes
Accepted

Does Microsoft release its SQL Query Hash Algorithm..?

Does Microsoft release its SQL Query Hash Algorithm..? No, Microsoft does not release the hashing algorithm. Additionally, hashing happens at a different layer than original query text - so even if ...
Sean Gallardy's user avatar
8 votes

Is left hash join always better than left outer join?

I'd just like to add a bit to the other answers and comments here: You are comparing apples to oranges. OUTER is a logical join operator. It specifies that you have a side from which you want to ...
Tibor Karaszi's user avatar
7 votes
Accepted

SQL Server Query Plan Hash Collision

Neither query_hash nor query_plan_hash are used to look up query plans for execution, so the exact concern in the question does not arise. The documentation for sys.dm_exec_query_stats explains that: ...
Paul White's user avatar
  • 86.4k
7 votes

Efficient key value store in Postgres

Number one: PostgreSQL will never be great as a key-value store if you have many UPDATEs. Workloads with many UPDATEs are just hard for PostgreSQL's architecture. Make sure that you create your table ...
Laurenz Albe's user avatar
  • 53.5k
7 votes

What is a scalable way to simulate HASHBYTES using a SQL CLR scalar function?

You can probably improve the performance, and perhaps the scalability of all the .NET approaches by pooling and caching any objects created in the function call. EG for Paul White's code above: ...
David Browne - Microsoft's user avatar
7 votes
Accepted

SQL Server Latching from Multithreading/Parallel Inserts and Hash Partitioning

Queries that don't specify the partitioning column will need to touch all partitions. This is particularly an issue with a table partitioned using a computed hash column because the hash value isn't ...
Dan Guzman's user avatar
  • 28.3k
6 votes
Accepted

Byte ordering for multibyte characters in SQL Server versus Oracle

The collation of an NVARCHAR / NCHAR / NTEXT column has no bearing on the encoding used to store the data in that column. NVARCHAR data is always UTF-16 Little Endian (LE). The collation of NVARCHAR ...
Solomon Rutzky's user avatar
6 votes
Accepted

Definition of indexing, datatype configuration, and DBMS for SHA3-256 hashes

SHA3-256 is 256bits. That's 32 bytes. Don't store it as TEXT any form, or it'll be 256 bytes (8x the size). I would store it as inline binary if your database supports it because I doubt anything it'...
Evan Carroll's user avatar
  • 63.7k
6 votes
Accepted

Determine the size of a hashed varchar column

Well - it appears that a fellow Stack Exchange member (Richard Marskell - Drackir) has figured this out here. I've copied his answer below: You should use the binary datatype. You can use binary ...
Scott Hodgin - Retired's user avatar
6 votes
Accepted

SQL Server hashbytes seed

I am able to get identical results between python and T-SQL code with the MD5 algorithm. For example, the NO COLLUSION string hashes to 0x5CA1A58C070F24EF1D4D2900E5727F37 on both platforms. Example T-...
Joe Obbish's user avatar
  • 32.5k
5 votes

Does Microsoft release its SQL Query Hash Algorithm..?

It sounds like all you want to do is get the Statement Text that corresponds to the query in dm_exec_requests/dm_exec_query_stats? You can APPLY the sys.dm_exec_sql_text() function using the ...
Mark Sinkinson's user avatar
5 votes
Accepted

Should I use the deprecated MD5 function in SQL Server?

From Microsoft's Documentation: When a feature is marked deprecated, it means: The feature is in maintenance mode only. No new changes will be done, including those related to inter-...
Hannah Vernon's user avatar
  • 70.2k
5 votes

SQL Server hashbytes seed

Joe has correctly pointed out that Python's hashlib.md5 and SQL Server's HASHBYTES('MD5', ...) functions have the same output. As an additional clarification, the built-in hash() function in Python is ...
Josh Darnell's user avatar
  • 29.4k
5 votes

Is left hash join always better than left outer join?

Is there something that I should look out for when running a LEFT HASH JOIN instead of a LEFT OUTER JOIN? Yes. Using a join hint like LEFT HASH JOIN forces the join order for tables specified in the ...
Paul White's user avatar
  • 86.4k
4 votes

Byte ordering for multibyte characters in SQL Server versus Oracle

What you are seeing is Little-Endian encoding that SQL Server uses to store Unicode characters (more precisely, it uses UCS-2 LE). More on Little-Endian here: Difference between Big Endian and little ...
sepupic's user avatar
  • 11k
4 votes

Does Microsoft release its SQL Query Hash Algorithm..?

Late to the party, but I do have a workaround. Sean is correct that Microsoft does not publish the hashing algorithm and that some normalization happens first (for example, if you insert a newline ...
bobroxsox's user avatar
  • 141
4 votes
Accepted

Use a scalar hashing function in a computed column - non-deterministic?

How do I convince SQL Server that the function is nondeterministic and make the function evaluate for each row, computing different hash values? SQL Server marks the function nondeterministic, ...
Paul White's user avatar
  • 86.4k
4 votes

In PostgreSQL, what area in memory does HashSetOp use, work_mem or shared_buffer?

work_mem is not an "area", it is a just value. HashSetOp is pretty poor at memory estimation and usage. It has no provision for spilling to disk. If it ends up using more memory than ...
jjanes's user avatar
  • 40.2k
4 votes
Accepted

PostgreSQL: what are purpose of functions uuid_hash(uuid) and uuid_hash_extended(uuid, bigint)

They are used for hash index method. As clearly mentioned in source code: hash index support It's possible to track dependencies through pg_amproc/pg_opclass/pg_opfamily system catalogs, but this is ...
Melkij's user avatar
  • 3,224

Only top scored, non community-wiki answers of a minimum length are eligible