How do I create a PRIMARY KEY/UNIQUE constraint that considers trailing space significant?
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).