Questions tagged [hashing]
For questions about the usage of hashing in a database context
35
questions
0
votes
1answer
21 views
How to optimize query for text array in PostgreSQL using `@>` operator
I have a column of type text[] and I want to search over this column using this SQL operator @>
Note: @> operator, it filters the data as such ['a','b'] in ['a','b','c']
The ...
10
votes
1answer
110 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 ...
0
votes
1answer
54 views
HashBytes Computed Column: Exclude Column
We want to create Hashbytes computed column Non-Persisted. Table has twenty columns, and would like to exclude IdentityId column. How could I do this in computed column? Is there a SQL library ...
3
votes
1answer
59 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
...
3
votes
1answer
584 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 ...
4
votes
2answers
495 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 ...
29
votes
4answers
953 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 ...
10
votes
1answer
341 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 ...
1
vote
1answer
113 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 ...
8
votes
2answers
2k 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 ...
0
votes
1answer
565 views
How to convert hex chars to byte array in mysql
Got sha1 checksum in hexadecimal format(40 characters).
Need help to convert hex chars to byte array in mysql.
Thanks.
0
votes
1answer
427 views
Insert PBKDF2 hash into PostgreSQL container
I am using the PostgreSQL docker image with the following Dockerfile:
FROM postgres:10.1
COPY init-db.sh /docker-entrypoint-initdb.d/init-db.sh
and an init-db.sh script with the following content:
...
-1
votes
1answer
387 views
Use Sha vs md5 or Hash in Snowflake-db
Let me preface by saying that I am not using this for storing passwords or any other sensitive info -- I simply want a row-level sha/hash that I can use later or to check for unique records. My tables ...
5
votes
1answer
6k 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 ...
1
vote
1answer
699 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 ...
0
votes
1answer
1k 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$', ...
5
votes
3answers
740 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 ...
4
votes
1answer
5k 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 ...
3
votes
0answers
50 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 ...
1
vote
1answer
485 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 ...
1
vote
0answers
64 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 ...
4
votes
1answer
1k 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
...
2
votes
2answers
439 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 ...
1
vote
0answers
197 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 ...
1
vote
0answers
114 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 ...
2
votes
1answer
888 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 ...
3
votes
1answer
66 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 ...
9
votes
1answer
2k 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 ...
2
votes
1answer
635 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...
0
votes
1answer
2k views
Mysql How to insert a variable as field value in a table
I have created a variable named hash as a password hash. How can this variable be inserted a a field value in a table?
hash = pbkdf2_sha256.encrypt("password", rounds=200000, salt_size=16)
cur....
8
votes
2answers
10k 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": ...
0
votes
2answers
198 views
How can user determine which hash their password uses?
Is there a query that a MySQL user that does not have admin rights can run to determine if they are using the old (pre-4.1) hash or the new (4.1-and-later) hash? This user was explicitly granted ...
1
vote
1answer
5k 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 ...
4
votes
2answers
14k 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) ...
20
votes
4answers
16k 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 ...
