How do I create a PRIMARY KEY/UNIQUE constraint that considers trailing space significant? I would like to be able to store records about things that are identified by strings where space is significant. I know that T-SQL follows rules about string comparison to implicitly pad up to the length of either string when performing comparisons and that I can work around that in WHERE clauses by also comparing DATALENGTH. But I don’t know how to make a PRIMARY KEY behave similarly. How do I store data so that


tools like SSMS display it as text without me needing to CAST it
it is mostly just treated like a string type in a normal programming language (with case/space being significant in comparisons)
it is stored in a column with UNIQUE/PRIMARY KEY constraints?


I would expect the following to work if T-SQL didn’t do the weird padding-during-comparison thing:

CREATE TABLE #x (
  Id NVARCHAR(16) COLLATE Latin1_General_BIN2 PRIMARY KEY
)
INSERT INTO #x VALUES ('asdf');
INSERT INTO #x VALUES ('Asdf');
INSERT INTO #x VALUES ('asdf '); -- error here
DROP TABLE #x


But on the third insert, I get:

Msg 2627, Level 14, State 1, Line 6
Violation of PRIMARY KEY constraint 'PK__#x________3214EC0761C21167'. Cannot insert duplicate key in object 'dbo.#x'. The duplicate key value is (asdf ).
Since I mentioned that I’m using DATALENGTH in my WHERE clauses, I’m going to try extending that to PRIMARY KEY creation as well. To do this, I’m just going to add a computed column with the DATALENGTH of the first one and then include that column in the PRIMARY KEY:

CREATE TABLE #x (
   Id NVARCHAR(16) COLLATE Latin1_General_BIN2 NOT NULL
  ,IdLength AS DATALENGTH(Id) PERSISTED NOT NULL
  ,PRIMARY KEY(Id, IdLength)
)
INSERT INTO #x VALUES ('asdf');
INSERT INTO #x VALUES ('Asdf');
INSERT INTO #x VALUES ('asdf ');
INSERT INTO #x VALUES ('asdf '); -- Error, proving that UNIQUE works
SELECT '"'+Id+'"' QuotedId, * FROM #x;
DROP TABLE #x;


I get a table like this:

QuotedId           Id               IdLength
------------------ ---------------- -----------
"Asdf"             Asdf             8
"asdf"             asdf             8
"asdf "            asdf             10


The attempt to insert 'asdf ' twice resulted in and error, proving that I am still benefiting from UNIQUE behavior:

Msg 2627, Level 14, State 1, Line 9
Violation of PRIMARY KEY constraint 'PK__#x________A3E5142C3CFA2452'. Cannot insert duplicate key in object 'dbo.#x'. The duplicate key value is (asdf , 10).