A generic list for passing table-valued parameters to SQL Server

May 21, 2011 21:11 by Matt Whitfield

In my current day job, one of the things I need to do is load log files from devices into SQL Server. Nothing out of the ordinary, but the data volume made for an interesting time - because each log file has maybe 100 or 200 rows. So not the sort of volume that you'd like to bulk load, and definitely not the sort of volume that you'd want to insert with individual insert statements. Because we're using SQL Server 2008 I thought that table-valued parameters would be the obvious choice.

So I read up on it and became concerned after reading Bob Beachemin's Blog at SQL Skills about TVP use and plan compilation. That had me worried, and so I was pleased to find, when I checked again, that he had blogged about it again, but this time with more positive news.

So I decided to implement a solution for data loading based on the use of TVPs. Researching that some more, I found a blog by Leonard Lobel about passing the data in. His method of explitly implementing IEnumerable<SqlDataRecord> fit pretty exactly with the method in which I wanted to pass the data to SQL Server. So I sat down at home and came up with the following utility class (yes I am sad and write day job code in the evenings!) which I thought would be good to share with you...

The idea is that it's a simple wrapper around List<T> that provides the explicit implementation of IEnumerable<SqlDataRecord>, but while remaining generic in and of itself. This necessitated that the classes that would use the list would be able to represent themselves as a SqlDataRecord object, and that the generic list would infer this through the use of a generic type constraint.

So here's the interface:

using System;
using Microsoft.SqlServer.Server;

namespace TVPTest
    /// <summary>
    /// IDataRecordTransformable is the interface to an object that can
    /// transform itself into a SqlDataRecord, for use with the
    /// <see cref="SqlDataRecordList&lt;T&gt;"/> class in order to load
    /// a data stream into a table-valued parameter
    /// </summary>
    public interface IDataRecordTransformable
        /// <summary>
        /// Creates a SqlDataRecord object that represents the meta-data
        /// of each table row
        /// </summary>
        /// <returns>A SqlDataRecord object</returns>
        SqlDataRecord CreateDataRecord();

        /// <summary>
        /// Populates an existing SqlDataRecord object with the values
        /// from the current row
        /// </summary>
        /// <param name="dataRecord">The SqlDataRecord to populate</param>
        void PopulateDataRecord(SqlDataRecord dataRecord);

So we have two methods, CreateDataRecord - which is the method that creates the SqlDataRecord object and populates it with columns, and PopulateDataRecord which is passed the existing SqlDataRecord and is expected to call the various set methods in order to set the values.

Let's have a look at the list class itself then...

using System;
using System.Collections.Generic;
using Microsoft.SqlServer.Server;

namespace TVPTest
    /// <summary>
    /// SqlDataRecordList represents a list of objects that are
    /// <see cref="IDataRecordTransformable"/>, and therefore can be used
    /// to represent the data passed to a table-valued parameter
    /// </summary>
    /// <typeparam name="T">The type of object in the list</typeparam>
    public class SqlDataRecordList<T> : List<T>, IEnumerable<SqlDataRecord>
        where T : IDataRecordTransformable
        /// <summary>
        /// The enumerator for the SqlDataRecord list, used with
        /// SqlDbType.Structured
        /// </summary>
        /// <returns>An IEnumerator object to enumerate the
        /// SqlDataRecord list</returns>
        IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()
            // declare the data record that we will populate
            SqlDataRecord dataRecord = null;

            // if we have entries
            if (this.Count > 0)
                // create the meta-data based on the first entry
                dataRecord = this[0].CreateDataRecord();

                // and yield return each record
                foreach (T entry in this)
                    // populate it

                    // and yield it for IEnumerable
                    yield return dataRecord;
                // no elements present
                yield break;

No particular rocket-science here. Just a generic class with a type constraint, implementing the IEnumerator using the yield and yield break keyword. Job done.

Any questions?

Tags: , ,


Data Type metadata

April 19, 2010 19:24 by Matt Whitfield

So let's take a look at how the typing metadata in SQL Server really works. There are three views involved here, [sys].[types], [sys].[assembly_types] and, on SQL Server 2008, [sys].[table_types].

Each of these views share the columns from [sys].[types], so let's have a look at those first and see what is involved:

nameThis is the name of the type. For system types, you should use the name of the type by itself (for example [varchar]). For user-defined types, you should use the schema qualified name of the type (for example [Sales].[OrderNumber]).
system_type_idThis is the ID of the system type on which the type is based.
user_type_idThis is the unique ID of the type as a whole, so each system and user defined type has it's own ID. MSDN says 'for system data types, user_type_id = system_type_id', but this is incorrect. [sysname], [hierarchyid], [geometry] and [geography] have [user_type_id] values that do not equate to [system_type_id]. The reliable way to determine a user defined type is to look at the [user_defined_type] column.
schema_idThis is the ID of the schema to which the type belongs. For system types, it will always point to the [sys] schema.
principal_idThis is the ID of the database principal that owns the object. If this is NULL, then the database principal that owns the schema also owns the type, as with all schema scoped objects.
max_lengthAs with column and parameter metadata, this is the storage size of the type. It defines the number of bytes that can be stored, and the same caveats apply - i.e. that the number is half the number of characters for unicode text types, that the number is 16 for old-style LOB types and that the number is -1 for (MAX) and [xml] types.
precisionThis is the precision of the type for numeric types. This is the maximum number of digits that the type can store. There are values present for other types too, although these are largely irrelevant.
scaleThis is the scale of the type for numeric types. This is the maximum number of digits to the right of the decimal point. There are values present for other types too, although these are largely irrelevant.
collation_nameIf the type is character based, then this is te name of the collation of the type, otherwise it is NULL.
is_nullableThis is 1 if the type is nullable.
is_user_definedThis is 1 if the type is a user-defined type.
is_assembly_typeThis is 1 if the type is a CLR type, rather than a SQL native data type. Note that msdn says 'based on a SQL Server system data type' - this is misleading, because [hierarchyid], [geometry] and [geography] are system data types, but are CLR based.
default_object_idThis is the ID of the stand-alone (old-style) default bound to the type by using sp_bindefault. Note that this is a deprecated behaviour, and support will be dropped from SQL Server in a future release.
rule_object_idThis is the ID of the stand-alone (old-style) rule bound to the type by using sp_bindrule. Note that this is a deprecated behaviour, and support will be dropped from SQL Server in a future release.
is_table_typeThis is 1 if the type is a table type (SQL Server 2008 only).

So that information tells us quite a lot about the types that are available to us in SQL Server. Both assembly types and table types require a bit more information however... Let's look at the information available for assembly types first:

assembly_idThis is the ID of the assembly that contains the code for the assembly type.
assembly_classThis is the full name of the class that contains the code for the assembly type within the assembly. Note that the class name if fully namespace qualified.
is_binary_orderedThis is 1 if the data that is stored on disk could be sorted by sorting that data, rather than by instantiating the CLR type and calling the comparison operators on that type. This can be quite important for performance, because instantiation of anything but the simplest CLR types can be intensely slow.
is_fixed_lengthThis is 1 is the CLR type always takes up the same amount of space on disk, irrespective of the content. For simple struct types, this will usually be 1, but for more complex types which can contain a variable amount of data, this would usually be 0.
prog_idIf the type is COM-visible, then this is the ProgID (COM class name) of the type as COM would see it. Note that this is not the CLSID (GUID) for the COM visible type.
assembly_qualified_nameThis is the fully qualified, version-number-and-all version of the type name.

Lastly, we come to table types - which are possibly the most complicated in terms of metadata. So we expect to see a lot of extra information in [sys].[table_types], right? No, there's only one extra column:

type_table_object_idThis is the object ID for the table object to which SQL Server attaches column and constraint metadata.

What's going on here?

If we look in [sys].[objects] for the object that relates to the ID given in the [type_table_object_id], and we find that this is an object with type 'TT'. This object is the underpinning of our table type metadata, and we can find out the columns and constraints for the table type using this ID, just as we would do for existing tables. Columns we already know all about from the last post - Constraints are what we look at next time...

Tags: , , ,


SQL Everywhere

Tag cloud


<<  April 2017  >>

View posts in large calendar