Armed with our new knowledge, we can create a single SQL query that decodes all of the SSNs. The strategy is to define a single CTE with all ten digits and to use one
CROSS APPLYfor each digit in the SSN. Each
CROSS APPLYonly references the SSN column in the
WHEREclause and returns the matching prefix of the SSN that we’ve found so far. Here’s a snippet of the code:
Click through for progressively faster solutions. This is the main reason I do not care for DDM as a feature. Its main benefit seems to be preventing shoulder-surfing on reports; any concerted attacker with a little bit of access to writing queries can subvert it.