Why are Column Defaults on my User-Defined Table Type (UDTT) not being honored?

Question

I create a table type in SQL:

CREATE TYPE [dbo].[MyObject_TableType] AS TABLE
(
    [Name] VARCHAR(MAX) DEFAULT '',
    [Index] VARCHAR(MAX) DEFAULT ''
)

I build a DataTable and populate it with one DataRow. I provide that table as a parameter to a stored procedure:

// Set up connection and command variables (code omitted)
// ...

// Make the data table
var table = new DataTable();
table.Columns.Add("Name");
table.Columns.Add("Index");

// Add one row that is missing an Index    
var row = table.NewRow();
row["Name"] = "ObjectOne";
table.Rows.Add(row);

// Make a parameter for the table
var tableParameter = new SqlParameter("MyObjects", SqlDbType.Structured)
{
    TypeName = "MyObject_TableType",
    Value = table
};

command.Parameters.Add(tableParameter);
command.ExecuteNonQuery();

I expected that inside of the stored procedure, the index value would default to '' (empty VARCHAR). Instead, I observed that the value was null. I know this because I attempted to merge the table type into an actual table, and the index column on my table does not allow nulls.

Why doesn't SQL Server honor the default that I placed on my table type? Is there a way to force SQL Server to honor it?

I think I can get around the issue by using the DataTable.DefaultValue property, but that solution has the disadvantage that I need to declare same defaults in two different places. I want to avoid redundant code if possible.

Answer 1

Why doesn't SQL Server honor the default that I placed on my table type?

Actually, SQL Server does indeed hono[u]r that default as the following test (using the UDTT provided in the Question) shows:

DECLARE @Test dbo.MyObject_TableType;
INSERT INTO @Test (Name) VALUES ('ObjectOne');
SELECT * FROM @Test;

Returns:

Name        Index
ObjectOne              <--- empty string, not NULL

The reason you are getting NULL instead of an empty string is because that is what you are asking the app code to provide. You even (unknowingly, of course ;-) identify the root cause of the problem when stating (emphasis added):

I build a DataTable and populate it with one DataRow. I provide that table as a parameter to a stored procedure:

A DataTable is a representation of an actual Table in memory, not of one or more INSERT statements. So when you create that single row, the Index column can't be undefined: it is either NULL or some value. And when you pass that DataTable into SQL Server as a TVP, you are passing in the equivalent of a Table.

Yes, you could probably get around this by specifying a default value for that DataColumn in the DataTable. But I agree that doing this is not the best approach.

My preference is to never use DataTables unless you already happen to have one, regardless of using a TVP or not. I can't think of a reason to ever create one simply for the purpose of passing data into a TVP. Besides this particular issue, it also duplicates whatever collection you are placing into it simply to be a temporary transport for the TVP (which wastes memory and the time it takes to copy that data, even if it isn't much of either of those).

Instead, create a method that will take whatever collection you already have and simply stream it, one item / row at a time, into the TVP. You do this by returning IEnumerable<SqlDataRecord> from this method, and then specify this method as the Value property of the SqlParameter representing the TVP.

method to stream the collection into the TVP:

private static IEnumerable<SqlDataRecord> SendRows(List<DbObject> DbObjects)
{
   SqlMetaData[] _TvpSchema = new SqlMetaData[]
   {
      new SqlMetaData("Name", SqlDbType.VarChar, SqlMetaData.Max,
                      true, false, SortOrder.Unspecified, 1),
      new SqlMetaData("Index", SqlDbType.VarChar, SqlMetaData.Max,
                      true, false, SortOrder.Unspecified, 2)
   };
   SqlDataRecord _DataRecord = new SqlDataRecord(_TvpSchema);

   int _NumRows = DbObjects.Count;

   // read a row, send a row.
   // "for" should be faster than "foreach", right?
   for (int _Index = 0; _Index < _NumRows; _Index++)
   {
      _DataRecord.SetString(0, DbObjects[_Index].Name);
      _DataRecord.SetString(1, DbObjects[_Index].Index);
      yield return _DataRecord;
   }
}

using the method:

private struct DbObject
{
    public string Name;
    public string Index;
}

List<DbObject> _DbObjects = new List<DbObject>();

// Add one row that is missing an Index    
_DbObjects.Add(new DbObject() { Name = "ObjectOne" });

// Make a parameter for the table
SqlParameter _ParamMyObjects = new SqlParameter("MyObjects", SqlDbType.Structured)
{
    TypeName = "MyObject_TableType", // not required for CommandType.StoredProcedure
    Value = SendRows(_DbObjects)
};

command.Parameters.Add(_ParamMyObjects);
command.ExecuteNonQuery();

The trick to getting the Default defined on the UDTT to populate the Index column with an empty string is the particular overload of the SqlMetaData Constructor that I used above. It has a boolean parameter for useServerDefault. Passing in true causes the column to not be passed into the INSERT statement if not "set" in the SqlDataRecord. Or, at least that is how I have always gotten IDENTITY columns in UDTTs to work (never tried it on an actual DEFAULT, but should be the same behavior).

P.S. Why are you using VARCHAR(MAX) as the datatype of these two columns in the UDTT? If either, or both, of these columns need to contain SQL Server identifiers (i.e. names of Tables, Views, Stored Procedures, Indexes, etc) then those are all sysname which is an alias for NVARCHAR(128). So you really need to at least be using NVARCHAR to avoid potentially data loss / hard-to-trace bug.