Are there are any way to get partc without make join by Verification Hash?

  • 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

  • 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

  • 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!

  • thank you very much for support

  • 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!

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply