internal working of SqlDataReader and metadata

Question

Out of curiosity and the eternal hunger for more insight :)

There's this CLR stored procedure here which sends results back to the client through the following code. An array of SqlMetaData is attached to SqlDataRecord. Each SqlDataRecord gets values which are sent to clients through a pipe.

SqlMetaData[] columns = new SqlMetaData[1];

columns[0] = new SqlMetaData("bool", SqlDbType.Bit);

SqlDataRecord record = new SqlDataRecord(columns);
SqlContext.Pipe.SendResultsStart(record);

foreach (bool b in bools)
{
     record.SetBoolean(0, b);

      SqlContext.Pipe.SendResultsRow(record);
}
SqlContext.Pipe.SendResultsEnd();

client code:

SqlCommand cmd = new SqlCommand("CLR_SPROC", connection)

SqlDataReader reader = cmd.ExecuteReader();

int b = reader.GetOrdinal("bool"); // b == 0 because it was added in index 0 in the SqlMetaData array

So the ordinal of "bool" becomes zero as its found first in the first index. Interesting.

Now the question:

Does SqlServer work like this under the hood for every query? I mean when a query is executed, does the query parser extract the column names in the final select, builds up an SqlMetaData array from that, attaches that to an SqlDataRecord and sends it back over a stream?

So the query "SELECT a,b,c FROM table"

becomes

SqlMetaData[] columns = new SqlMetaData[3];

columns[0] = new SqlMetaData("a", SqlDbType.Int);
columns[1] = new SqlMetaData("b", SqlDbType.Int);
columns[2] = new SqlMetaData("c", SqlDbType.Int);

 SqlDataRecord record = new SqlDataRecord(columns);
 SqlContext.Pipe.SendResultsStart(record);

foreach(...)
{
  record.SetInt32(0, a);
  record.SetInt32(1, b);
  record.SetInt32(2, c);
  SqlContext.Pipe.SendResultsRow(record);
}

It's a sophisticated piece of software. All reference source for SQL client is available from Microsoft, for example, SQLCommand.cs: referencesource.microsoft.com/#System.Data/data/System/Data/… — Simon Mourier
– Simon Mourier, Commented Aug 13, 2014 at 6:38
The link above is no longer valid (404), you might look at SqlCommand.cs or SqlClient — surfmuggle
– surfmuggle, Commented Nov 6, 2016 at 14:20

Dan Guzman · Accepted Answer · 2014-08-14 16:26:13Z

3

+50

SQL Server communicates with clients using TDS protocol (Tabular Data Stream). TDS is a streaming protocol that includes meta-data that describes a result set, followed by the result set data stream. Records within a result set contain the same number columns, column names, and data types, so the meta-data needed to process the result set only needs to be sent once rather than attached to each record.

Client APIs like SqlClient expose the low-level TDS structures as higher level objects like SqlMetaData, SqlDataRecord, and SqlDataReader. Although you code against client APIs rather than TDS directly, you might find a cursory understanding of TDS communication interesting since it provides a view of how SQL Server works under the covers. You can find details of the TDS protocol at http://msdn.microsoft.com/en-us/library/dd304523.aspx.

answered Aug 14, 2014 at 16:26

Dan Guzman

46.9k3 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Serve Laurijssen Over a year ago

quite interesting, together with the reference source site I'm gonna spend some time reading this up. Thanks

Pingpong Over a year ago

@Dan I have a problem which might be related to sql meta data. Can you please have a look? It seems the problem is this:columnIndex >= _metaData.Length, but I don't know how to fix it.

Collectives™ on Stack Overflow

internal working of SqlDataReader and metadata

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related