Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [hashing]

For questions about the usage of hashing in a database context

Filter by
Sorted by
Tagged with
31 votes
4 answers
3k views

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

As part of our ETL process, we compare rows from staging against the reporting database to figure out if any of the columns have actually changed since the data was last loaded. The comparison is ...
Joe Obbish's user avatar
  • 32.2k
24 votes
4 answers
21k views

Choosing the right algorithm in HashBytes function

We need to create hash value of nvarchar data for comparison purposes. There are multiple hash algorithms available in T-SQL, but which one the best to choose from in this scenario? We want to ensure ...
Sky's user avatar
  • 3,684
18 votes
3 answers
14k views

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

For every version of Postgres that supported hash indexing, there is a warning or note that hash indexes are "similar or slower" or "not better" than btree indexes, at least up to version 8.3. From ...
Sampson Crowley's user avatar
17 votes
2 answers
24k views

redis newbie - how to create hash within a hash?

I want to create this type of a structure in redis: (its basically json data) { "id": "0001", "name":"widget ABC", "model": "model123", "service":"standard", "admin_password": ...
Happydevdays's user avatar
11 votes
1 answer
2k views

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? Does it internally take a hash of each row and compare? David Lozinksi ran a study, SQL: Fastest way to ...
user avatar
10 votes
1 answer
474 views

Hash aggregate bailout

A question that arose in a chat discussion: I know hash join bailout switches internally to a sort of nested loops thing. What does SQL Server do for a hash aggregate bailout (if it can happen at ...
Paul White's user avatar
  • 85.7k
10 votes
1 answer
5k views

Hash Join vs Hash Semi Join

PostgreSQL 9.2 I'm trying to understand the difference between Hash Semi Join and just Hash Join. Here're two queries: I EXPLAIN ANALYZE SELECT * FROM orders WHERE customerid IN (SELECT customerid ...
St.Antario's user avatar
  • 1,235
9 votes
1 answer
23k views

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

What would be the best data type to store the results of the HASHBYTES('MD5', ...)? It outputs 16 bytes of binary as follows: e.g. 0x5CFCD77F9FF836189D2F647EBCEA183E I could store it in the ...
GWR's user avatar
  • 2,807
8 votes
3 answers
9k views

Is left hash join always better than left outer join?

I have a query that runs quite slow (see below). While searching for a way to improve it we found that the query runs about ten times faster if we replaced the LEFT OUTER JOIN with LEFT HASH JOIN The ...
merger's user avatar
  • 199
8 votes
3 answers
2k views

Does Microsoft release its SQL Query Hash Algorithm..?

We use SQL Spotlight in our environment. it's pretty handy. we use particularly the output of sys.dm_exec_requests and sys.dm_exec_query_stats. Spotlight pulls the query plan from the plan cache for ...
g0pher's user avatar
  • 83
7 votes
2 answers
25k views

Can't run digest() on PosrgreSQL 9.3 on RDS

Following the advice given on another answer, I tried using the digest() function on PostgreSQL 9.3 on AWS RDS: devdb=> SELECT digest('blah', 'sha1'); ERROR: function digest(unknown, unknown) ...
Adam Matan's user avatar
  • 11.7k
7 votes
1 answer
10k views

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

I'm using server-side hashing to transmit passwords, then running PBKDF2 in the database to store the hashed password + salt combination. Hashing nvarchar(max) and a @variable holding the same value ...
Nate Anderson's user avatar
6 votes
2 answers
2k views

Byte ordering for multibyte characters in SQL Server versus Oracle

I am currently in the process of migrating data from Oracle to SQL Server and I'm encountering an issue trying to validate the data post-migration. Environment Details: Oracle 12 - AL32UTF8 ...
HandyD's user avatar
  • 9,972
4 votes
2 answers
729 views

SQL Server hashbytes seed

I'm trying to compare hashes generated by SQL Server HASHBYTES() with hashes generated with python's hash() (or hashlib) function. For both functions, the same algorithm is used. The problem is I don'...
zimek-atomek's user avatar
4 votes
2 answers
10k views

Efficient key value store in Postgres

I want to use Postgres(11 in CloudSQL) as an efficient key value store. I have about 200GB of dictionaries (average size is 10kB, the structure can be different and nested). I'm thinking about ...
Lukasz Madon's user avatar
4 votes
1 answer
3k views

Determine the size of a hashed varchar column

I am using SQL Server. I have this table: create table MyTable ( ID int not null , Name varchar(500) ) and I am adding a name_hash column to store hash values for the name column: alter myTable ...
Elsayed's user avatar
  • 217
4 votes
1 answer
3k views

Should I use the deprecated MD5 function in SQL Server?

We'd like to use MD5 for our hashing function instead of SHA_256 but as of SQL Server 2016, MD5 is deprecated. We're using this for hashing (comparing which records have changed). We now have this ...
Gabe's user avatar
  • 1,334
4 votes
1 answer
250 views

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

I want to know the difference between the hash table in a hash join and the hash table in HashSetOp Except. Here is the PostgreSQL script I wrote to explain my question. drop table if exists t1 ...
JAEGEUN YU's user avatar
3 votes
1 answer
300 views

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

I am designing a system within which I will need to be able to search a database using a file or string SHA3-256 hash. For example the hash of test is ...
Matthew's user avatar
  • 133
3 votes
1 answer
432 views

How does Hash Join works with relational operators other than equality operator?

I have been reading about Hash Join and how does it work in physical level. However, there is some thing that I don't understand (because of my lack of knowledge). This is the algorithm of Hash Join ...
mtilhan's user avatar
  • 133
3 votes
1 answer
2k views

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

Functions uuid_hash and uuid_hash_extended Exploring postgresql I discovered 2 functions that looks interesting: SELECT p.proname , obj_description(p.oid) AS func_desc, ...
Alex Yu's user avatar
  • 213
3 votes
2 answers
795 views

Conflict resolution for hash-backed unique constraint of large text bodies

So we've just run into ERROR: index row size 2736 exceeds maximum 2712 for index "foo__bar__un" Hint: Values larger than 1/3 of a buffer page cannot be indexed. Consider a function index ...
User1291's user avatar
  • 132
3 votes
1 answer
11k views

how are passwords encoded in pg_shadow

How are passwords encoded in column passwd of pg_shadow? Doing a: select passwd from pg_shadow; I see values like the following: md52c92c858aa1fa12144f1dcbd4ca9c3a0 ... which, doesn't seem to be ...
Marcus Junius Brutus's user avatar
3 votes
1 answer
920 views

Why does an index on the join predicate does not improve the performance of a hash join?

Various references, such as join-strategies-and-performance-in-postgresql, say that an index on the join predicate does not improve the performance of a hash join. However, couldn't a B+ tree index ...
keezar's user avatar
  • 33
3 votes
0 answers
582 views

Recent degradation/variability of HashBytes performance

Up until a few days ago, T-SQL code we have running in SQL Azure that generates an MD5 for temp table values was fast (a few seconds per hundred thousand on a Business Critical Gen 5, 2 VCores DB). ...
Vince's user avatar
  • 175
3 votes
0 answers
87 views

What is a cost of search for records in a table with an equality predicate on a non key attribute A and a static hash organization?

To speed up the search for records in a table with an equality predicate on a non key attribute A and selectivity factor fs, is preferable: A sequential organization on a key attribute ? A static ...
Revolucion for Monica's user avatar
2 votes
1 answer
856 views

SQL Server Latching from Multithreading/Parallel Inserts and Hash Partitioning

We are conducting parallel, multithreading inserts in SQL server tables, and want to reduce latching. What are the disadvantages of utilizing hash partitioning to reduce latching? Is it reduced ...
user avatar
2 votes
1 answer
1k views

How to get back value passed to HASHBYTES()?

I want to use the SQL Server HASHBYTES() function. But I don't know how to get back the value passed to HASHBYTES...
Shivaji Dhumal's user avatar
2 votes
1 answer
2k views

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

I have a scalar function select [dbo].[fn_get_hash_fields] ('blah','dbo') to which is passed a table and a schema and which returns an expression representing a concatenation of all column values in ...
Allen's user avatar
  • 101
2 votes
1 answer
2k views

Create Postgres hash aggregate function

Postgres: 15.1 My goal is simple: I want to create an aggregated hash value for x rows from column c in table z. Example Input: SELECT hash_agg(c) AS checksum FROM z; Example Output: checksum ...
Yuki's user avatar
  • 21
2 votes
2 answers
3k views

Using the hash of text instead of text itself to address a row in Postgres

I have a project for a website where I have to address thousands or maybe tens of thousands of objects in a postgres table. Initially I had the url to be: example.com/object/{ID}/{TEXT SLUG}/ where ...
Ejonas GGgg's user avatar
2 votes
1 answer
4k views

MySQL passwords SHA512 using AES_ENCRYPT instead of ENCRYPT?

A tutorial is asking me to encrypt passwords using SHA512 in the following manner: INSERT INTO `vir_users` (`id`, `domain_id`, `password` , `email`) VALUES ('1', '1', ENCRYPT('pw1', CONCAT('$6$', ...
Henry van Megen's user avatar
1 vote
1 answer
738 views

SQL Server Query Plan Hash Collision

I'm wondering if anyone can shed light on a worry of mine that a query_plan_hash collision could cause a query to be executed as an entirely different query. The hash is a 16 digit Hex that sp_help ...
Dave Sims's user avatar
  • 323
1 vote
2 answers
2k views

oracle - hashing method, excluding DBMS_CRYPTO

BACKGROUND: DBAs have hidden DBMS_CRYPTO package to any role, grantee, and I wonder why. I have to use wrapper instead :( Is there any way to get the same hash for the text field with the same text? ...
Jakub P's user avatar
  • 157
1 vote
2 answers
3k views

MSSQL Hashbytes produces different output, string vs column value

The following SQL SELECT CONVERT(nvarchar(128), HASHBYTES ('SHA2_512', 'test'), 2) as 'From String' produces the following hash: ...
JOSEFtw's user avatar
  • 227
1 vote
1 answer
689 views

Specify build and probe tables to hash join?

I am observing a situation in AdventureWorks database where the hash join operator is building hash table using a larger input (296 rows) and then probing with a much smaller input (3 rows). I'm not ...
upInCloud's user avatar
  • 113
1 vote
2 answers
63 views

Concurrency Control B-Tree between Hash

I am new to transaction. When I read the book "Transactional Information Systems", it is mentioned that B-tree uses key-range locking to guarantee serializability. Is that true all accesses ...
GenGG's user avatar
  • 15
1 vote
1 answer
588 views

HASHBYTES() result of another HASHBYTES(), OR CHECKSUM_AGG() aggregate for HASHBYTES()?

I want to aggregate a single HASHBYTES() from the results of another HASHBYTES()... something like CHECKSUM_AGG() does for CHECKSUM()... I have been able find how to make a single HASH per row of ...
Ricky Lively's user avatar
1 vote
1 answer
510 views

Several Postgres partitioning questions (hierarchical partitioning, HASH, PK order)

I'm pretty new to Postgres and to RDBMS partitioning. I have a few related questions about the performance benefits of partitioning. Background: I am trying to fix slow batch queries on a 4 column ...
Stephen's user avatar
  • 213
1 vote
0 answers
30 views

Execution plan showing high hash match [closed]

i have a sql query running from database 1 that joins table company on to a view SELECT DISTINCT RTRIM([K].[cmpCompanyCode]) AS [cmpCompanyCode] FROM [dbo].[view] AS [K] JOIN [database2].[dbo].[...
deanpillow's user avatar
1 vote
0 answers
52 views

Issue with hash storage resulting from password_verify() output

Consider the following code: $random_token = random_bytes(32); $token_hash = password_hash( $random_token, PASSWORD_DEFAULT ); $token_base64 = sodium_bin2base64( $random_token, ...
DevelJoe's user avatar
  • 163
1 vote
0 answers
96 views

MySQL partitioning by hash not repeatable

I have a MySQL (5.6.17) table with a few million records that I wish to partition. In order to do this, I use this query: ALTER TABLE tablename PARTITION BY HASH(id) PARTITIONS 8; Seems to ...
Martijn's user avatar
  • 131
1 vote
0 answers
1k views

Using hashed value as quick comparison

I have a postgres table that will be used to store data pulled from several remote sources / APIs. I will be syncing the data several times a day. Since the data will vary wildly depending on the ...
user101289's user avatar
1 vote
0 answers
431 views

Performance of Long Primary Keys

There are plenty of discussions on the benefits and drawbacks of using hashes as primary keys, but in my case I have a database of webpages that I need to look up by address, and these addresses are ...
ndm13's user avatar
  • 111
0 votes
2 answers
86 views

SQL Server - Finding Parent Source from T-SQL Snippet

The environments for which I'm responsible have some pretty substantial plan non-reuse challenges. I've run across the following query (h/t Brent Ozar blog commenter Michael J Swart) that does a fine ...
AccidentalDBA_CO's user avatar
0 votes
3 answers
161 views

Index of which columns are NOT empty

I am trying to enhance the search performance of a table based on whether certain columns contain values. I have the following columns : id | link | text | date | lat | long | type If a record ...
Leon's user avatar
  • 103
0 votes
1 answer
1k views

How to create automatic partitions on a non-time based column in Postgres?

I want to create a partition on a column which is of type serial. A hash based partition seems appropriate here. But from what I understand I will need to create all the partitions myself (based on ...
Ashwin's user avatar
  • 272
0 votes
1 answer
407 views

Computing hashes in PostgreSQL for JSONB columns

From PostgreSQL docs: jsonb does not preserve white space, does not preserve the order of object keys, and does not keep duplicate object keys. If duplicate keys are specified in the input, only the ...
VIAGC's user avatar
  • 103
0 votes
1 answer
478 views

Generating MD5 Hash for NVARCHAR and VARCHAR columns across different databases

I am building a utility that generates a single MD5 hash across all CONCATENATED column values for any table row. To make this work, I have to eliminate NULL values using COALESCE and must CAST NUMBER ...
Lauren_G's user avatar
0 votes
1 answer
202 views

How exactly are the password hashing functions in the pgcrypto module different from the regular MD5 and other algorithms?

This is not technically a database question, but about the innards of Postgres's pgcrypto extension.The documentation says The algorithms in crypt() differ from the usual MD5 or SHA1 hashing ...
ahron's user avatar
  • 803