A message from our CEO about the future of Stack Overflow and Stack Exchange. Read now.
    Tweeted twitter.com/StackDBAs/status/1103626353952346112
2 Incorporated comments; edited tags
source | link

I'm working on converting a SQL Anywhere (ASA) 5.5 based application and its database to MSSQLMicrosoft SQL Server. 

Existing is a table with a primary key clustered on two columns, code_type char(4)code_type char(4) and code char(30)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.

If ('cha', '') exists in the table, I'd like to be able to insert ('cha', ' ') as it has more trailing spaces, but not ('cha', '') because it has an equal number of spaces (0) as an existing entry.

This question on UNIQUE Index outlines the behavior on unique index, SQL is applying right padding of spaces to make two strings equally sized before making the comparison per ANSI/ISO SQL-92 Section Section 8.2 General Rule #3. I've confirmed this behavior with the char type as the link used varchar. I'm not committed to using a key, just some way to enforce a distinct number of trailing spaces.

There are quite a few characters guaranteed not to show up in the second column. The field is either letter abbreviations or legal document citations. The legal document citations seem to be contain alphanumeric characters plus '(', ')', '-', '.', ' ', and '/'.

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.

I'm working on converting a SQL Anywhere (ASA) 5.5 based application and its database to Microsoft SQL Server. 

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.

If ('cha', '') exists in the table, I'd like to be able to insert ('cha', ' ') as it has more trailing spaces, but not ('cha', '') because it has an equal number of spaces (0) as an existing entry.

This question on UNIQUE Index outlines the behavior on unique index, SQL is applying right padding of spaces to make two strings equally sized before making the comparison per ANSI/ISO SQL-92 Section Section 8.2 General Rule #3. I've confirmed this behavior with the char type as the link used varchar. I'm not committed to using a key, just some way to enforce a distinct number of trailing spaces.

There are quite a few characters guaranteed not to show up in the second column. The field is either letter abbreviations or legal document citations. The legal document citations seem to be contain alphanumeric characters plus '(', ')', '-', '.', ' ', and '/'.

1
source | link

Enforcing distinct number of trailing spaces

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.