2

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 ).
  • I don't know but I experimented and you can create a persisted computed column that replaces space with something else and create a PK on that. Definitely a workaround though. – Nick.McDermaid Jan 18 '17 at 4:14
  • It does not pad. It truncates for comparison. – paparazzo Jan 18 '17 at 8:35
  • @Nick.McDermaid Wouldn’t that prevent me from using the char you chose for replacement in the value? Also, wouldn’t a persisted column that actually takes up as much space as the source column be a waste of storage space? – binki Jan 18 '17 at 14:47
  • 1
    @Paparazzi I’m basing my statement that there’s automatic padding on this MS support article and various questions/answers on stack overflow which referenced it. When comparing these types of strings, SQL Server is supposed to behave as if it pads both operands to the length of the longest one prior to performing the comparison. That doesn’t mean that the padded version is stored in the table or that SQL Server actually does the padding when comparing. It’s just required to behave like it does, and only within the comparison operation. – binki Jan 18 '17 at 14:55
  • In answer to your question: Yes and Yes – Nick.McDermaid Jan 18 '17 at 22:11
4

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).
2

That weird padding during comparison thing is the ANSI-SQL standard at work. You would have to do a workaround of some sort. Either replace the spaces with some unique character that you wouldn't have in your data set and remove the characters at display time or use a computed column, etc.

What you are trying to do is sort of against how the ANSI standard handles these things. As far as I know the only operator to really consider those trailing spaces is the LIKE operator.

I wonder if you could do something with computed columns and some guaranteed escape character per space (Tilde, Pipe, etc) for storage, or rethink what you are trying to make as your primary key. Could your primary key be more of a surrogate key like an identity column which makes inserts and updates happier potentially? Could it be more of a composite key with two columns that are unique together?

  • My thought, with my existing usage of putting DATALENGTH in WHERE clauses, is to include a calculated DATALENGTH column in the PRIMARY KEY/UNIQUE. I’m not sure how a surrogate key would help because then I’d have to try to enforce UNIQUE myself. I guess either way I would be working against SQL though I’m kind of stuck with SQL Server for now for this code :-/. Maybe JSON encoding the string before passing it to the database would allow me to see it as a string in the database with tools (unlike the VARBINARY option) and also get the behavior I want, not sure I’d do that though. – binki Jan 18 '17 at 4:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.