I work on sql server 2014
I need to get partc as expected result
Actualy i need partc from table #partsc that have Verification Hash on table #VervicationCode
are there are any way to get part c without join with Verification Hash
create table #partsc
(
partc int,
[VerificationHash] VARBINARY(512) NULL
)
insert into #partsc(partc,VerificationHash)
values
(11234,0x566B391EDEB07A47B17B89265ABE19850655885D83247AE7E7C28849D8873A31F56D139E4987BFE85B476035E39B7308C5D203955A238F92D5A2096B50AD674C),
(15431,0x5677965D16CEEDC754EAED74780662518E132F08C1B6CFF67BE62D21100C3B4C63E09937BB7153CDE67550F957AFBB23658B2193EB842FC173A8F8460DED246D),
(67501,0x569132E995038072EA4AE52DC760FCB2C21AE1A6F047C66FF86B483BF575DE2EF412B3B10CDC8B8DC8B8D31D28BF90442C31811F13EC5B49DE1EBC008B6EEF16),
(87190,0x56C5668A7FCB015D39EE602FAC80124050D0ADCA684D77FC99D7C293DCC09DA1A4FA2514F89E86D7619AE20A72C6CAE2A7BFE776F89C5494C36994C84241D705),
(987610,0x56E0315397B4A932570101661B449FC41DF9683F5EDCC7387A8C9FA8E357A06FCF1989587CCF3E0548F79F36BB2F5C91BF6CD9E988EA9922C5C865A043B51B1F),
(89871,0x57B1B8FF59BFE3F788AA1C6D5CFFC40D54F0D494D60B43CED1B52C1E290CC8AE62C3E1220365F1DCB0A00688AF92222DC32CC5454DCF9572812735D9FAF7E3C7),
(12909,0x57DAFCAA14E6561FD8C2109627732DFDBEC5E169682080EF1107FA96ED505CC18BD22924BFA19A1A346BB15440FF48F53202A88672AD6FC66F4DAF0D518BC69C)
create table #VervicationCode
(
[VerificationHash] VARBINARY(512) NULL
)
insert into #VervicationCode(VerificationHash)
values
(0x5677965D16CEEDC754EAED74780662518E132F08C1B6CFF67BE62D21100C3B4C63E09937BB7153CDE67550F957AFBB23658B2193EB842FC173A8F8460DED246D),
(0x569132E995038072EA4AE52DC760FCB2C21AE1A6F047C66FF86B483BF575DE2EF412B3B10CDC8B8DC8B8D31D28BF90442C31811F13EC5B49DE1EBC008B6EEF16),
(0x56C5668A7FCB015D39EE602FAC80124050D0ADCA684D77FC99D7C293DCC09DA1A4FA2514F89E86D7619AE20A72C6CAE2A7BFE776F89C5494C36994C84241D705),
(0x56E0315397B4A932570101661B449FC41DF9683F5EDCC7387A8C9FA8E357A06FCF1989587CCF3E0548F79F36BB2F5C91BF6CD9E988EA9922C5C865A043B51B1F)
expected result will be :
partc
15431
67501
87190
987610
November 25, 2021 at 2:06 pm
Why would you want to avoid JOIN? Maybe use IN but there's no benefit afaik and it might actually be slower
select partc
from #partsc
where VerificationHash in(select VerificationHash
from #VervicationCode);
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
November 25, 2021 at 6:04 pm
thank you for reply
i need to generate id for two tables then join by id
so How to do that please ?
if i join hash to hash then for big data will be slow
so i need to generate id for every hash then join by it
so how to do that
I agree with Steve here - why would you want to avoid the JOIN? Put a good index on it and you should be good to go.
As an alternate approach to the "IN" you could use "EXISTS" with something like:
SELECT partc
FROM #partsc A
WHERE EXISTS (SELECT 1
FROM #VervicationCode B
WHERE A.VerificationHash = B.VerificationHash);
Not sure if it is any faster or better than the "IN" approach, but gets you the same result. My quick check on my test system, the execution plan is identical with EXISTS vs IN, and personally, I find the IN approach easier to understand what is going on.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 25, 2021 at 6:17 pm
thank you very much for support
November 25, 2021 at 6:56 pm
Personally, I don't think my answer was any better than Steve's. I think his answer was probably just as "correct" as mine.
But to address your concern about needing to add an ID, there is no fast way to add that after the fact. It should have been put in place at design time if it was needed. But joining on VARBINARY, with a good index on the column, should not be that slow, even with large data sets.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 29, 2021 at 1:05 am
Avoiding a join here is a mistake, IMHO, especially if the hash-code is going to be used for "verification".
Ah... there's that word... "verification". By itself it can mean so many different things and imply so very many different uses especially when it comes to "big data" (as you said).
So, before you go any further, let's ask the question... what do YOU mean by "verification" and what is this "verification" going to be used for? In the process of telling us that, please provide the DDL for the two tables involved including what the Primary Key and the Clustered Index (they could be different) are for both. Who knows? We might even be able to setup a million row test table to help you make sure of the best method. 😀
Also, why are you using VARBINARY(512) when it appears that all of your data (seen so far) is only BINARY(64)? The SHA2-512 hashing algorithm uses 512 BITs, not BYTEs. 512 bits is only 64 bytes.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply