Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 The Yak Corral
 Unique SSN

Author  Topic 

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2005-01-18 : 13:39:05
Here's how the SSN number works in Sweden:
------------------------------------------

When a person is born a unique SSN number is assigned: YYMMDD-DDDC
The first 6 digits is the persons birthdate, followed by a hyphen(-), then follow 3 digits and a checksum digit.

The year a person is 100 years old, the hyphen is replaced by a plus(+) sign.

How is that for UNIQUE !!!!

rockmoose

Kristen
Test

22859 Posts
Posted - 2005-01-18 : 14:10:43
1,000 births a day not going to happen?

Population is 9 million, is that right?

Average lifespan, say, 70 years and, say, 2.4 children [per couple]. That's about 500 births / day - would 1,000 in a day be statistically impossible? You know ... around October ... "Sure as hell can tell was a cold winter last year"

Kristen
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2005-01-18 : 14:18:40
You know, they have a "special kind o number" as well, called a "reserve" number.

It's when YYMMDD is not a valid date !
It's reserved for corporations (non physical legal persons), and for people with temporary citizenships.
Maybe they can resort to that if the sytem gets fucked (pun intended)

rockmoose
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2005-01-18 : 14:24:10
What about Y3K bugs?



Brett

8-)
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2005-01-18 : 14:33:36
Ha Ha Ha, they just discovered 6500 people with the same SSN as now dead pople.
Maybe they should hotfix that before worrying about Y3K *LOL*.

We have been reassured that with todays computer systems,
this kind of mixup is not possible anymore.

(maybe they decided to not pracice KEY REUSE anymore)

Hey, whats your SSN ?
cool, mine too!

rockmoose
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

3246 Posts
Posted - 2005-01-18 : 15:11:32
Lest anyone think that can't happen in the USA, think again, it has. You'd best not use SSN as your PK. Not to mention that there are regulations that provide that you are not required to provide your SSN to any entity other than the Social Security Administration. Most people still do out of convenience, but some people will refuse to provide it out of concerns for privacy.

Better come up with a more reliable key...

---------------------------------------------------------------------------------
Infoneering: Information Technology solutions engineered to professional standards.
Go to Top of Page

JimL
SQL Slinging Yak Ranger

1537 Posts
Posted - 2005-01-18 : 16:08:07
With all the fake SSN around as a employer we need a better way to verify if they are good.

We had a new employee a couple of months ago and on his start day he did not have his SS card with him nor did he have a picture ID. We gave him a couple of days to come up with it.

After the two days I got suspicious and ran his info with social security. Sure enough they told me he needed to contact them ASAP as his information was incorect.(goverment talk for its Bogus) When we confronted him about it he told us that their must be some foul up and that he would have the ID an SS card the next day.

We never saw him again.



Jim
Users <> Logic
Go to Top of Page

jhermiz

3564 Posts
Posted - 2005-01-18 : 17:10:04
Good ol autonumbers

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

3246 Posts
Posted - 2005-01-18 : 17:50:07
quote:
Originally posted by jhermiz

Good ol autonumbers
Yes, that's an option, but PLEASE let's not restart that whole debate again.

---------------------------------------------------------------------------------
Infoneering: Information Technology solutions engineered to professional standards.
Go to Top of Page

MichaelP
Jedi Yak

2489 Posts
Posted - 2005-01-18 : 18:00:05
We need to issue every human a GUID to make our lives simple. Oh wait, nevermind. Someone already tried that, and that didn't work out real well for the people that got em or the people that issued them.

Lets ot forget about the 10-digit SSN (which aren't really SSN's) for Illegal aliens here in the USA.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts
Posted - 2005-01-18 : 18:14:55
You mean like ET ?

Or household staff of NY police commisioners ?



Damian
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2005-01-18 : 18:44:28
>> Or household staff of NY police commisioners ?

Just like here then
SELECT UNCOMPRESS(ssn) FROM (SELECT 1229737029 AS ssn UNION ALL SELECT 1195461665) AS illegalaliens

rockmoose
Go to Top of Page

MichaelP
Jedi Yak

2489 Posts
Posted - 2005-01-18 : 18:46:52
UNCOMPRESS()
Is that some undocumented function? I can't find anything about it.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

jhermiz

3564 Posts
Posted - 2005-01-18 : 18:51:48
quote:
Originally posted by AjarnMark

quote:
Originally posted by jhermiz

Good ol autonumbers
Yes, that's an option, but PLEASE let's not restart that whole debate again.

---------------------------------------------------------------------------------
Infoneering: Information Technology solutions engineered to professional standards.


What debate ? I must have been MIA, or is this one of those

"Well for username I'd just use the user name as the key..."

"But why, why not use an auto generated number..."

"But why, when I can just use varchar..."

"But but but..."

"But but but..."

And it goes on for pages and pages and pages of threads

Ok..I wont go that route

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Go to Top of Page

jhermiz

3564 Posts
Posted - 2005-01-18 : 18:53:26
quote:
Originally posted by MichaelP

UNCOMPRESS()
Is that some undocumented function? I can't find anything about it.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>


I think he was kidding ...

Our DNA is unique

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Go to Top of Page

MichaelP
Jedi Yak

2489 Posts
Posted - 2005-01-18 : 18:57:14
If you run that query he posted, it gives you a two row result!


ILLE
GAL!


Could be handy as a simple encryption method, if I can figure out how to use it.

Michael


<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2005-01-18 : 19:07:35
All I can find about it is what Ken's book (SPs, XML, and HTML) says on page 670. He gives an example of its use and says it uncompresses a string.

Tara
Go to Top of Page

jhermiz

3564 Posts
Posted - 2005-01-18 : 21:35:05
I should of known he wasnt kidding..its rockmoose, that guy has more tricks up his sleeve than ... err forgot the name of that magician who scares people :-).

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2005-01-19 : 03:28:03
well here we have it like this ddmmyyyAABCCD as in say 0101954505152
where AA is the conuntry code, B is gender info, CC is the some number (can never remember what it is) and D is the checksum number.
as there's only about 2 mil of us, there won't be any duplicates yet

Go with the flow & have fun! Else fight the flow
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2005-01-19 : 07:06:56
It's just used to uncompress compressed binary text data.
uncompress(0x6162636465), encrypt('abcde') -- encrypt_a('abcde')
normally I think the data is stored in words(2 bytes), but sometimes compressed to just one byte.

You can see uncompress used here, in the syscomments table:
select left(object_name(sc.id)+'.'+sc.name,20) as col, com.text
from syscolumns sc join syscomments com on sc.id = com.id and sc.colid = com.number
where sc.id = object_id('syscomments') and sc.name = 'text'



rockmoose
Go to Top of Page

elwoos
Master Smack Fu Yak Hacker

2052 Posts
Posted - 2005-01-19 : 10:13:54
Jhermiz - Not sure DNA IS unique though I think it's more unique than say fingerprints or retina's. I thought it was highly likely to be unique (but not certain)

Spirit - can your system cope with Hermaphrodites etc...?

Perhaps DNA sequences could be used instead of SSN's, then we need DNA computers - hold on a sec - aren't we them already

steve

And how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain.
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site