title: How to define a user-defined data type with addressable members?
body: <p>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).</p>
  
  <p>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.</p>
  
  <p>For example (in bad pseudocode):</p>
  
  <pre><code>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
  </code></pre>
  
  <p>and then be able to do things like this:</p>
  
  <pre><code>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')
  </code></pre>
  
  <p>So far all I've found is this <a href="http://www.codeproject.com/KB/database/UserDefDataTypes.aspx" rel="nofollow noreferrer">CodeProject article</a> which does not go far enough or provide links for further info, and this rudimentary <a href="http://msdn.microsoft.com/en-us/library/aa933121(SQL.80).aspx" rel="nofollow noreferrer">MSDN link</a>.</p>
  
  <p>Apparently my google-fu is weak tonight, any links/hints greatly appreciated!</p>
  
  <p>Alternate title: how to simulate 'overlay' fields in SQL SERVER?</p>
  
  <p>MS SQL SERVER 2005 or later assumed/preferred.</p>