I have large fact tables with composite non-clustered indexes, something like this:
NONCLSUTERED INDEX (OrderDate,OrderType,ClientKey,ItemKey,CustomerKey)
The columns ending with "Key" are varchar(50) columns, but they store strings of 20-30 characters.
Do I need to worry in long term, that the varchar columns in the index are actually varies in length? Will I get more page splits, slower performance when millions of rows merge into these tables? https://stackoverflow.com/questions/59667/what-are-the-use-cases-for-selecting-char-over-varchar-in-sql
Does the Sql Server engine have harder times to figure out the plan using that index since the columns are varies in length? I read that CHAR is better since its fixed size, and its easier for the db engine to calculate with, hence it performs better. Is it that significant?
Does it worth to hash these "Key" columns individually with lets say SHA2_256 function, so they become fixed size BINARY(32)?
NONCLSUTERED INDEX (OrderDate,OrderType,HASHEDClientKey,HASHEDItemKey,HASHEDCustomerKey)
I did my test to answer at least the performance question, and doesn't see any performance benefit with hashkeys over varchar. In fact its a big overhead to calculate the hashes and store the extra 32 bytes of each next to the original "Key" column.
What I cannot test is how these varchar indexes will behave when i have millions of rows merging into the tables each day?
Which one would you choose for both performance and maintenance point of view?