Ayende @ Rahien

My name is Oren Eini
Founder of Hibernating Rhinos LTD and RavenDB.
You can reach me by phone or email:


+972 52-548-6969

, @ Q c

Posts: 6,131 | Comments: 45,568

filter by tags archive

SQL Server 2008 Table Value Parameters and NHibernate

time to read 1 min | 147 words

I just took a look at how this feature is exposed. I really wants this feature. I hit the 2,100 parameters limit of SQL Server too many times in the past, always when I had to do some large IN queries. So, I was very happy to hear about that feature, but I didn't really take a look until now.

Unfortunately, the way they are implemented requires a hard reference to them. You have to create the type in the server,  and then you have to reference it by name. Annoying, to say the least, and it looks like there isn't any generic solution that I can accept. This is bad because I can think of quite a few uses for this feature, from applying batches to complex queries, it can be very useful, but it is looked in its own safe, statically typed, world. Urgh!


Andrew Tobin

Hey Ayende,

Yeah, SQL 2008 needs to know the structure of the table "object", so you have to create that structure in SQL before you can utilise it. The other thing I haven't seen, albeit in my incredibly brief investigations, is a way to view what structures there are active against a db.

I have an extension method I talk about here:


What that does is creates a DataTable from any type of IEnumerable, which you can then pass in to a call in .NET as a parameter for stored procs, etc, which accept a Table Valued Parameter "object".

Of course you have to place the DataTable columns in the same order as your TVP "object" is defined.

I have some very simple code and sql in C# that I used as a demo at a user group if you'd like me to shoot it through that would show you?

Andrew Tobin

The other thing I forgot to mention in the last comment - these things look like they'll work beautifully with the MERGE in SQL 2008, because the Merge does an Insert or an Update depending on whether an object exists, you could pass in a complete table of modifications as one object/one call to your SQL stored proc.

And hopefully the Merge would then have less reads/scans through the index on updating the rows cleverly.

Ayende Rahien


This isn't helpful to me for the simple reason that I need a generic approach to do it in order to integrate it with NHibernate.

Yes, I can do one off stuff, but I don't want that.


sorry.. I just got stuck on the part where it says you've "hit the 2,100 parameters limit of SQL Server too many times in the past".. What in the world were you doing? Maybe it's because I've never done whatever you were doing, but it sounds like you needed to rethink your approach.

..and I agree that table value feature would be really useful.

Frans Bouma

The hard-coded link is similar to the narrow minded solution they came up with for UDT's written in .NET code.

It's sad indeed, because all they really needed to do is adding something simple like Oracle has for years: array based parameters.

Andrey Shchekin

Well, I would prefer the only way to do something to be typed than untyped (if the both ways are not possible). You can use Func<T1, T2> approach (which is ugly, I know).

Ayende Rahien


As I said, IN queries for data that reside outside the DB.

Example, I want to get all your direct reports. But that information is in AD, not in the DB, so I have to pass a list of all the IDs.

Some of the people have over 2,000 direct reports (leave that aside), now you hit the limits.

Ayende Rahien


But I don't have the generics approach.

I literally have to create a new type if I want to send an array to the database.

This is close to useless from my perspective


Ever considered passing XML into a stored proc? It's quite easy to shred it into a temp table and use and join however you like.

Now this is weird...

I searched for an example of how to shred XML and found


which strangely enough points right back to your blog.....

Ayende Rahien


Yes, I did

I also tried fn_Split and bulk data entry.

They all have problems.

In particular, XML and splitting strings has high CPU cost.


'I hit the 2,100 parameters limit of SQL Server too many times in the past, always when I had to do some large IN queries'

With Sql 2008 the answer is obvious, but on previous versions all you need is to pass a delimited string such as:

Select @params = '1,5,7,20,24,55'

and then split it and inner join it with your main data on the filtered column

Select * from MyData inner join dbo.Split(@params) Params on MyData.FilterColumn = Params.Data

Here is a splitting function taken from:




@RowData nvarchar(2000),

@SplitOn nvarchar(5)


RETURNS @RtnValue table


Id int identity(1,1),

Data nvarchar(100)




Declare @Cnt int

Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)


    Insert Into @RtnValue (data)


        Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

    Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

    Set @Cnt = @Cnt + 1


Insert Into @RtnValue (data)

Select Data = ltrim(rtrim(@RowData))



Again, Its useful to be able to pass in full tables in Sql Server 2008, but I see this being mostly used to pass a List of Primary key values, rather than the table and its accompanying data, this would be more efficient. If this is a common case a couple of Table type definitions should suffice (one for passing int primary keys, varchar primary keys, etc)

Of course if you want to insert/update lots of data at once, I would avoid this technique and instead bulk insert all data to a temp table and then do a MERGE into the main table (since this is supported by sql 2008)


Just noticed in your reply to jeremy that you also tried fn_split...

Was it really underperforming in your scenario? What was the reasoning behind prefering the maximum number of parameters instead?

Ayende Rahien


We noticed significant CPU work when doing splits for > 1000 items.

Using XML is better if it is > 1000 items, but slower if it is less.

For > 3000 items, you tend to want to bulk insert the data to the DB and manage on that



If you need to be generic (and I think you need to implement TVP in NHibernate ;-), you should query the database to obtain the schema of your TVP of your procedure.

But in the facts, if a procedure use a TVP, this means that the TVP type is already present in the database and the only thing you have to do in your mapping is to obtain it's structure.

In .Net side a DataTable is the object you need to pass as parameter to the procedure, then build the DataTable based on the structure needed by the TVP(s) of the concerned procedure.

To query the base to obtain the schema you should do a query like this one:

SELECT P.name,C.name,C.systemtypeid,C.is_nullable,ST.name,ST.length

FROM sys.parameters P

INNER JOIN sys.objects O ON P.object_id = O.object_id

INNER JOIN sys.table_types T on P.user_type_id = T.user_type_id

INNER JOIN sys.columns C ON T.type_table_object_id = C.object_id

INNER JOIN sys.systypes ST ON C.system_type_id = ST.xusertype


AND O.name = 'my_procedure_name'

This will give you a table with the table(s) value(s) parameter name, columns in TVP, their type and size.

Hope, it helps ;-)

Ayende Rahien


I don't want to have a SP that uses that, I want to be able to send arrays to the DB, that is all.

Comment preview

Comments have been closed on this topic.


  1. RavenDB Conference 2016–Slides - 7 hours from now
  2. Proposed solution to the low level interview question - about one day from now

There are posts all the way to Jun 02, 2016


  1. The design of RavenDB 4.0 (14):
    26 May 2016 - The client side
  2. RavenDB 3.5 whirl wind tour (14):
    25 May 2016 - Got anything to declare, ya smuggler?
  3. Tasks for the new comer (2):
    15 Apr 2016 - Quartz.NET with RavenDB
  4. Code through the looking glass (5):
    18 Mar 2016 - And a linear search to rule them
  5. Find the bug (8):
    29 Feb 2016 - When you can't rely on your own identity
View all series



Main feed Feed Stats
Comments feed   Comments Feed Stats