I'm working on converting a SQL Anywhere (ASA) 5.5 based application and its database to MSSQL. Existing is a table with a primary key clustered on two columns, code_type char(4) and code char(30). I was able to build the table fine, but attempting to move over the data I ran into a conflict because the table contained ('cha', ' ') and ('cha', '') and speaking with another developer it sounds like this has a use case. Not really looking to retrain the users, what options are there to enforce a distinct number of spaces at the database level?
The database is for a single application so in the end I can rely on control of how data is inserted into the table but would like to enforce an intended distinction in the table definition.