Need Help With SQL Query

  • 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

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

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

  • 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

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

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

  • 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

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

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

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

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