How to define a user-defined data type with addressable members?

Question

I have an unusual situation to model in a MS SQL Server database: the primary key of a table is a multi-segment 'natural' key composed of 5 foreign keys (of fixed sizes).

I'd like to be able to define a user-defined data type to implement the data structure based on a CHAR(8) primitive in such a way that the elements are addressable as individual fields.

For example (in bad pseudocode):

UDT seggy
(
    seg1 char(2),
    seg2 char(1),
    seg3 char(1),
    seg4 char(2),
    seg5 char(2)
)

create table yotable
(
    pkfield seggy NOT NULL,
    etc varchar(14),   --whatever etc.
)
with pkfield as the primary key,
and also with seg1 as a foreign key to tableseg1,
and also with seg2 as a foreign key to tableseg2,
and so on

and then be able to do things like this:

insert into yotable (pkfield, etc) values ('abcdefgh','whatever')
select * from yotable where seg2 = 'c'
insert into yotable (seg1,seg2,seg3,seg4,seg5,etc)
    values ('ab','c','d','ef','gh', 'whatever')

So far all I've found is this CodeProject article which does not go far enough or provide links for further info, and this rudimentary MSDN link.aspx).

Apparently my google-fu is weak tonight, any links/hints greatly appreciated!

Alternate title: how to simulate 'overlay' fields in SQL SERVER?

MS SQL SERVER 2005 or later assumed/preferred.

Answer 1

You can define a CLR UDT that has the structure you want, but (1) you won't be able to keep the foreign keys (foreign keys have to be at the column level, not a field in a UDT in a column), and (2) it isn't trivial to implement a CLR UDT (at least compared to what is in your pseudocode). Plus, from your description, it sounds like they really are separate columns semantically, and what you're looking for is just a shortcut for convenience; IMO a UDT probably isn't the best approach in that scenario anyway.

I would suggest keeping the separate columns, but create either a view that has a column that concatenates the fields together, or a computed column in the table that does the same. This will permit searching using either the combined or separate notation. You can then use an INSTEAD OF trigger to decompose an insert/update on the combined column into the component parts.