title: SQL Server Performance with Key/Pair Table vs XML Field and XPath
body: <p>I've seen a few questions on this topic already but I'm looking for some insight on the <strong>performance differences</strong> between these two techniques.</p>
  
  <p>For example, lets say I am recording a log of events which will come into the system with a dictionary set of key/value pairs for the specific event. I will record an entry in an Events table with the base data but then I need a way to also link the additional key/value data. I will never know what kinds of Keys or Values will come in so any sort of predefined enum table seems out of the question.</p>
  
  <p>This event data will be constantly streaming in so insert times is just as important as query times.</p>
  
  <p>When I query for specific events I will be using some fields on the Event as well as data from the key/value data. For the XML way I would simply use a Attributes.exists('xpath') statement as part of the where clause to filter the records.</p>
  
  <p>The normalized way would be to use a Table with basically Key and Value fields with a foreign link to the Event record. This seems clean and simple but I worry about the amount of data that is involved.</p>