Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

SELECT question Expand / Collapse
Author
Message
Posted Wednesday, December 11, 2013 6:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 11, 2014 11:09 AM
Points: 37, Visits: 158
The SSN is stored as a hashed value.
Post #1521873
Posted Wednesday, December 11, 2013 6:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 11, 2014 11:09 AM
Points: 37, Visits: 158
The SSN is stored as a MD5 hashed value.

The combination of SSN, PKT and FICE_NBR in table A does not have a match on the combination of SSN, PKT and FICE_NBR in table B.
Post #1521876
Posted Wednesday, December 11, 2013 6:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:19 AM
Points: 6,721, Visits: 13,832
gary.morey (12/11/2013)
The SSN is stored as a MD5 hashed value.

The combination of SSN, PKT and FICE_NBR in table A does not have a match on the combination of SSN, PKT and FICE_NBR in table B.


You were probably expecting matches, Gary. Can you post the query you used?


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1521878
Posted Wednesday, December 11, 2013 6:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 11, 2014 11:09 AM
Points: 37, Visits: 158
SELECT *
FROM Temp_BigTable
WHERE SSN + PKT + FICE_NBR
NOT IN (
SELECT SSN + PKT + FICE_NBR
FROM shi.raw33
)
Post #1521880
Posted Wednesday, December 11, 2013 8:19 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 35,218, Visits: 31,677
gary.morey (12/11/2013)
The SSN is stored as a MD5 hashed value.

The combination of SSN, PKT and FICE_NBR in table A does not have a match on the combination of SSN, PKT and FICE_NBR in table B.


Ah, thanks, Gary. I feel much better now.

Shifting gears a bit, you might want to consider something a little bit more than simple MDF hashing. A simple billion row rainbow table (which is nothing on the machines today) would quickly unravel all of the SSNs. Even adding a salt to the MD5 would be better although that would likely still fail an audit.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1521930
Posted Wednesday, December 11, 2013 9:14 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:30 AM
Points: 3,576, Visits: 8,028
gary.morey (12/11/2013)
SELECT *
FROM Temp_BigTable
WHERE SSN + PKT + FICE_NBR
NOT IN (
SELECT SSN + PKT + FICE_NBR
FROM shi.raw33
)

I recommend you to read the following article NOT EXISTS vs NOT IN



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1521957
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse