0

Can you have your own clr udt as a primary key on a table? It seems you cannot - I added interfaces for binary serialization and comparable, but when using the type in a PK Constraint I receive an error: The type cannot be used in a primary key.

So - is this possible at all, and if so, which attributes do I need to decorate my type with to allow it?

*edit: Found a clue to my problem: from https://learn.microsoft.com/de-de/sql/t-sql/statements/create-index-transact-sql?view=sql-server-2017 section "columns": You can create indexes on CLR user-defined type columns if the type supports binary ordering.

So binary ordering needs to be "true" (mine was false). Follow up question is now: how to serialize your type (collection of strings) so that it becomes binary orderable?

-- Background Info

The reason I want to do that is maybe a worse followup to a bad initial idea: our current db implementation uses composite business keys. The components of the composite key are not in "many" columns, but concatenated with separators in one column. For example, "jeep-red-ny" could be the primary key for a row describing a Jeep, colored in red, that is based in New York. The column that contains this concatenation is simply varchar(50).

I was wondering if having a "dedicated" type to host these three aspects (vehicle type, color, city) makes it a bit better.

I suppose, best option would be to drop these concatenated keys and use "normal composite" keys in distinct columns instead. Having the concatenation is nice in a way that the whole key is one column, there is no doubt what belongs to it and what not. The UDT would give both: contained in one column, but access to all components.

Ralf
  • 538
  • 1
  • 6
  • 17
  • It sounds like your trying to set the primary key to be a calculated column (using a CLR function), rather than using a CLR function to initialise the value. Is my guess correct? – Thom A May 15 '19 at 08:02
  • In a way, yes and no. Thanks for your suggestion of a computed column (clr or not) to host the primary key. That would also keep track of what belongs together to form the key for each table. A simple prefix to the column names would be sufficient to make that visible though. However, the original implementors of the tables chose NOT to include the columns that form the key as distinct columns, but only in the concatenated form. So computed column is not possible, since the values to compute with are not there on the table. – Ralf May 15 '19 at 08:24
  • Edited the question: Found a bit of info on index creation – Ralf May 15 '19 at 08:40
  • Yes - this is a worse idea. I think you confuse different visualization of a row with the content of that row. Nothing but a person cares how "nice" it is to see your concatenated version. You seem to forget that sorting character data can be complicated - binary sorting is both case and accent sensitive. And yes - you should use a computed column if needed (or a view). And yes - you will need to change the structure of your table and "pull apart" the existing values into 3 new columns. You then drop the old column and create a new computed column for your concatenated version. – SMor May 15 '19 at 11:40

0 Answers0