I have a field that is an alphanumeric field that ideally is a encrypted field for a non-unique identifier. It's used to relate other rather large fact tables in a many-to-many relationship. I do not have the related dimension for this field because there is no other attributes for this FK.
This field is in a rather large and growing data warehouse where the Fact tables are clustered on time and non-clustered on keys like these.
The column is
VARCHAR(50) and only vary between 45 and 50. Would have to check, but I would assume the collation is
SQL_Latin1_General_CP1_CI_AS. I use no FK for optimization reasons. All controlled by the ETL.
Due to the type of key, it makes it hard to index. It's fragmentation is managed by a series of tests I did recently that showed 75% fill factor at least manageable with reducing the fragmentation on daily incremental loads for a least a week until a full rebuild may be needed, which is fine once a week.
With the reducing of the fill factor from 100% to 75%, the inserts and reads have become slower. The records have grown bigger as expected too. Any indexes with includes are pretty much driving the performance into the ground on inserts, but of course helping queries out that need them 10x better.
Does anyone have any good experience working with alphanumerics in a data warehousing environment? How it's handled and index now is fine, but I think it could be better. I was dabbling with the idea of ripping out the keys, forming a new dimension and adding a more manageable key during the ETL process.