How to have a not null and unique SSN column where SSN data is not always available RRS feed

  • Question

  • I am trying to figure out how to create an iterating proxy SSN to insert where a SSN is not yet available on new accounts, because in this application the SSN is used, along with other column values, such as Last Name and Birth Date, to match against incoming batches of possible new accounts from another data system, and we need to be sure to not duplicate accounts for individuals. The situation is unique in that the individuals being tracked in this application often have first and last name changes between events when the application needs to track them, which is why it is important to be able to match incoming data on the SSN field.

    Has anybody in the community had any experience solving this sort of a problem? Thanks in advance for any help!

    Friday, August 23, 2019 6:25 PM

All replies

  • Did you try to generator a random string (for the case when SSN is not provided) with a letter at the beginning and following by a 10-digit random number if you use 123-45-6789 as the format of SSN? Since the first character is a letter, you can know that it is not a SSN.

    A Fan of SSIS, SSRS and SSAS

    Friday, August 23, 2019 8:51 PM
  • That's a great idea, I will try that.
    Saturday, August 24, 2019 12:17 AM
  • You're going to have some serious problems. Because of all the illegals in the United States, SSN has become nine random digits and no longer follows the older regular expression format that we grew up with. All zeros and all nines are still illegal and can be used as dummy values. There is also a huge number of duplicate Social Security numbers for the same reason. It was never a great identifier, but it's become even worse. Guoxiong suggestion of a leading alphabetic might be a good solution for you. But I would also recommend that you invest in a data quality package to check names phone numbers and other such things in your database.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, August 26, 2019 3:30 AM
  • Hi RavenCohoSalome,


    Thank you for your issue.


    There is a similar posting and please refer to Format SSN in SQL Server.


    Hope it will help you.


    Best Regards,


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Monday, August 26, 2019 7:20 AM
  • I would highly recommend you find another key besides SSN.  SSN is NOT a unique identifier and can change due to user error or just false information and different people can report the same SSN (not valid, but happens).

    Monday, August 26, 2019 11:41 AM