title: Extended Event force order of sys.fn_xe_file_target_read_file() DMF results
body: <blockquote>
    <p>Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)   Jun 28 2012
    08:36:30      Copyright (c) Microsoft Corporation     Enterprise Edition
    (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)</p>
  </blockquote>
  
  <p>Is there a way to guarantee the order of results when reading an Extended event .xel log file using the sys.fn_xe_file_target_read_file() DMF? Ideally I would like to number the records 1..X based on the order in which they were inserted into the .xel file.</p>
  
  <p>I need to poll the file target multiple times and insert new records into a table. If I could guarantee the order of results then I could add the Meta data column "Entry Number", and use this column to avoid inserting the same records over multiple executions i.e.</p>
  
  <pre><code>SELECT  entries.EntryNumber
          ,entries.file_name
          ,entries.data
          ,entries.file_offset
  FROM    (
          SELECT  ROW_NUMBER() OVER (ORDER BY &lt;GUARANTEE ORDERING BASED ON ORDER OF INSERTION&gt;) AS EntryNumber,
                  file_name,
                  CONVERT (XML, event_data) AS data
                  ,file_offset
          FROM    sys.fn_xe_file_target_read_file ('C:\ExEvent_0_123456789.xel', 'C:\ExEvent_0_123456789.xem', NULL, NULL)
                  ) entries
  WHERE   NOT EXISTS 
          (
          SELECT  1
          FROM    dbo.ExEventLog d
          WHERE   entries.EntryNumber = d.EntryNumber                         
          )
  </code></pre>
  
  <p>If this is not possible, can I guarantee that all records with the same 'file_offset' value are loaded into the .xel file in one single batch? For example if I query the .xel file for all records with a 'file_offset' of 123, can I guarantee that no additional records with a ‘file_offset’ of 123 will be loaded subsequently? </p>
  
  <p>This is based on creating the extended event <code>WITH MAX_DISPATCH_LATENCY = 1 SECONDS</code> and polling the .xel file every 60 seconds.</p>
  
  <p>I don't feel comfortable using the @initial_file_name and @initial_offset paramters because of this bug / feature : <a href="http://connect.microsoft.com/SQLServer/feedback/details/714286/sys-fn-file-target-read-file-reads-incorrect-files-when-offset-reading#details" rel="nofollow">http://connect.microsoft.com/SQLServer/feedback/details/714286/sys-fn-file-target-read-file-reads-incorrect-files-when-offset-reading#details</a></p>