I wouldn't use a DECIMAL to store SSNs with rowstore or columnstore tables. The INT data type has the following advantages over DECIMAL:
- It's generally faster for SQL Server to work with
- It allows bitmap filters to be pushed down to the storage engine
- If the column doesn't allow NULLs then it allows for a "perfect hashing function" which doesn't require a probe residual in joins.
If you need better performance for your SSN column I would use an INT with a leading 1. That preserves leading zeroes which seems to be desired. You should store all SSNs in the same format and only cast when necessary. For example if you need to display an SSN as a string to an end user then
SELECT RIGHT(CAST(1012345678 AS INT), 9) returns
"012345678". Otherwise work with the raw value.
I have no idea what your queries look like, but suppose you have 100k SSNs in a table and you need to check if any of those SSNs appear in a different table that has a billion rows. Here's what the query might look like:
FROM dbo.SSNS_TO_CHECK_3 c
WHERE NOT EXISTS (
FROM dbo.ALL_SSNS_CCI_INT_LEADING_1 t
WHERE c.SSN = t.SSN
OPTION (MAXDOP 1);
Using an INT column with a leading 1, the above query takes 7 seconds on my machine. With the DECIMAL(10, 9) format that you proposed the query takes 63 seconds my machine. Nearly all of the time is spent on the bitmap operator.
There are some additional considerations with columnstore but they don't matter here. INT is superior in every way that I know of compared to DECIMAL(10, 9).