title: Storing 'dictionaries' and 'arrays' in SQL Server
body: <p>I'm looking for some advice on how to store 'arrays' and 'dictionaries'-type information in an SQL Server 2012 database. Up to now, I've been using the xml type, as I can query this easily, and they can be simply represented, e.g:</p>
  
  <pre><code>&lt;Array&gt;&lt;i&gt;1&lt;/i&gt;&lt;i&gt;2&lt;/i&gt;&lt;i&gt;3&lt;/i&gt;&lt;/Array&gt;
  &lt;Dictionary&gt;&lt;Item1&gt;Value1&lt;/Item1&gt;&lt;Item2&gt;Value2&lt;/Item2&gt;&lt;/Dictionary&gt;
  </code></pre>
  
  <p>Up until now, I assumed that the xml data type would benefit from PAGE compression but today I realised that isn't the case. This is a fairly big issue since the database will store hundreds of thousands of these records every day, and it seems much of the data will be this uncompressed XML. </p>
  
  <p>So I'm looking for a workaround. I figure I may be able to tabulate this data. For example,  a data dictionary table:</p>
  
  <pre><code>[ID] bigint PK,
  [OwnerID] bigint,
  [Name] varchar(32),
  [Value] varchar(32)
  </code></pre>
  
  <p>And an array:</p>
  
  <pre><code>[OwnerID] bigint PK
  [Index] int PK
  [Value] varchar(32)
  </code></pre>
  
  <p>This would work, but I'm worried about how 'queryable' this method would be - i.e. 'WHERE ObjectID's X dictionary properly = Y'. I could write a scalar function for this, but I know that'll cause performance issues. </p>
  
  <p>I've read up on this particular issue, but the general consensus seems to be 'use an xml column and don't worry about it', but given the amount of data I need to allow for, I don't think this is an option for me. </p>
  
  <p>Any advice or insights would be very much appreciated.</p>