Howto transfer tracefile into a trace table without loosing the sort order?

Question

I have traced some data into a trace table into Sql Server using the Profiler GUI. This table has the following structure:

It contains the SQL statements as well as execution plans in the TEXTDATA column. I figured out that usually the execution plan xml that belongs to a select statement has been placed into the row before the sql statement.

So for example the execution plan for the Select statement in row with [ROWNUMBER] = 60 usually can be found in the row with [ROWNUMBER] = 59.

This seems to be quite resilient (enough for my purpose) and I would like to keep this relation for later use. (So far I could not find another way of catching both, the query and the plan - ideally it should come as ONE merged result, for example the plan attached to the query inside of a comment... but I could not manage to create that.)

However, to avoid the massive overhead of using the Profiler GUI I created a trace script, stored the results into a trace file (because unfortunately using a script it's not possible to directly redirect the results into a table) and then imported the trace file into a table using the following statement:

SELECT * INTO mytracetable FROM ::fn_trace_gettable('c:\traces\myTraceFile.trc', default)

Unfortunately this produces a different table structure and worst about that is, that the ROWNUMBER identity column is now missing, so the records are messed up and it's impossible to find the plans that belong to the queries.

Q 1: Is it possible to create a table from the trace file that has the rows stored in an order similar to the trace table profiler had created?

Q 2: Does anyone know another better way to get those information without using the profiler GUI?

Answer 1

During creation of the question process I figured out a way:

SELECT IDENTITY(int, 1, 1) AS RowNumber, EventClass, TextData, Duration, SPID, BinaryData, ServerName, DatabaseName INTO trace_table3
FROM fn_trace_gettable('c:\traces\myTraceFile.trc', default);

does the trick. The results are kept "together" same way as in the trace table.

http://zarez.net/?p=228

https://msdn.microsoft.com/de-de/library/ms188425(v=sql.120).aspx

I tried to trace the execution of the function fn_trace_gettable to get an idea how it orders the records to get them lined up again, but of course nothing showed up but the execution itself.

However, this answers question 1, I have the desired result.

EDIT: As AaronBertrand suggested in the comments, the eventsequence delivers the events in an order that very reliably contains the PLANS (column/type 51) in advance of the SQL Statements. That's a better approach than identities.