How to define a custom data type with addressable elements?
I have an unusual situation for modeling in a MS SQL Server database: the primary key of a table is a multi-segment "natural" key consisting of 5 foreign keys (fixed sizes).
I would like to be able to define a custom data type to implement a data structure based on the CHAR (8) primitive in such a way that the elements are addressed as separate 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 follow these steps:
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 a CodeProject article that hasn't gone far enough or provides links for further information, and this rudimentary MSDN Link .
Apparently my google-fu is weak today, any links / hints are greatly appreciated!
Alternative name: how to simulate "overlay" fields in SQL SERVER?
MS SQL SERVER 2005 or later is assumed / preferred.
source to share
You can define a CLR UDT that has the structure you want, but (1) you won't be able to store foreign keys (foreign keys must be at the column level, not a field in the UDT into a column) and (2) it is non-trivial to implement a CLD UDT (at least compared to what's in your pseudocode). Also, from your description, it looks like they are indeed separate columns semantically, and what you are looking for is just a shortcut for convenience; IMO UDT is probably not the best approach in this scenario.
I would like to suggest keeping separate columns, but create either a view that has a column that concatenates the fields together, or a calculated column in a table that does the same. This will allow you to search using a combined or single entry. Then you can use the INSTEAD OF trigger to decompose the insert / update on the merged column into its component parts.
source to share