Need Help With SQL Query

Question

Need Help With SQL Query

darrylmybiz

SSC Enthusiast

Points: 149
More actions
September 2, 2012 at 10:34 am

#280678

Have a table with a SSN column that is a float data type. My objective is to obtain the correct query to keep the column at 9 digits, change the data type from float to nvarchar and obtain the update query to make the change.
This is the SSN column before the my query:
SSN
020210101
100110010
Ran this query:
select dbo.ValidSSN(cast(SSN as bigint)), * from temp.__do_metro
Received this result after the query:
SSN
020200000000
100100000000

Viewing 11 posts - 1 through 10 (of 10 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What does the function ValidSSN do?

p.s. I hope SSN doesn't stand for social security number, because social security numbers should be stored encrypted if they're stored at all.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

darrylmybiz SSC Enthusiast Points: 149 More actions · Answer 2

SSN does stand for social security number. The company I work for receives encrypted medical data from companies. The SSN, name, address, etc. are fields used to verify members medical information. The several SSN's used in this example are fictitious.

Jeff Moden SSC Guru Points: 1004525 More actions · Answer 3

darrylmybiz (9/2/2012)
SSN does stand for social security number. The company I work for receives encrypted medical data from companies. The SSN, name, address, etc. are fields used to verify members medical information. The several SSN's used in this example are fictitious.

I guess the question here is, does the SSN ever appear in a non-encrypted form in your sstored data?

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dwain Camps SSC Guru Points: 86978 More actions · Answer 4

Perhaps ValidSSN is returning a masked version of the SSN?

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

darrylmybiz SSC Enthusiast Points: 149 More actions · Answer 5

Yes, the SSN appears in a non-encrypted form a database.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

I'd suggest you take that issue up with management, it's a risk.

Now, back to your original question, what is the definition of ValidSSN?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004525 More actions · Answer 7

darrylmybiz (9/3/2012)
Yes, the SSN appears in a non-encrypted form a database.

Heh.. that's cool. What company do you work for?

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dwain Camps SSC Guru Points: 86978 More actions · Answer 8

Jeff Moden (9/3/2012)
darrylmybiz (9/3/2012)
Yes, the SSN appears in a non-encrypted form a database.
Heh.. that's cool. What company do you work for?

I take it that you're asking this question so you can be sure you never appear in their database?

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1004525 More actions · Answer 9

Among other things not the least of which is that.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004525 More actions · Answer 10

darrylmybiz (9/3/2012)
Yes, the SSN appears in a non-encrypted form a database.

It's databases like this that cause identity theft and a world of other pains to people who don't know that some company has violated what is supposed to be a sacred trust. Do everything you can to convince management that the SSN's should be deleted or encrypted. If you're doing backups, the backups should be destroyed properly. This is a serious offense to many different statutes by many federal agencies not to mention a clear violation of best practices. If management doesn't budge, consider blowing the whistle on them because this is a very, very bad thing and it needs to be fixed at any cost.

If you don't believe there's anything wrong with storing clear text SSNs, then please email me your SSN so I can show you what can happen.

If you agree that storing clear text SSNs is a bad thing but are afraid to approach management and afraid to blow the whistle even anonymously, then PM me with the name of the company and I'll make sure they go through an information security 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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)