I’m mulling over some design decisions that will probably result in my using Table-Valued Parameters (TVPs) for the first time. Let’s see what you think.<\/p>\n
Please note: this post applies to SQL 2008 and above. No real differences in the restrictions or behavior in SQL 2012, AFAICS. <\/p>\n
We have a complex SSIS flow that handles batch imports, with a large number of validation steps on staging tables before the eventual consumption of the correctly transformed, and correctly reduced, set of rows by production tables.<\/p>\n
For various reasons, I need to reproduce this logic without using SSIS, and without using staging tables, in some circumstances.<\/p>\n
An external application will invoke a single “master process” method, which in turn will invoke all the logic necessary to handle the original SSIS behavior.<\/p>\n
I want the sequence of steps to remain faithful to the original SSIS process, to ensure that data can be consumed both ways with the same results. <\/p>\n
For the purpose of performance considerations, please understand that the SSIS process handles large-scale imports, whereas this new one is light-weight. I don’t have to worry about bulk processing in the new design. In fact, the expected use cases for it are real-time integrations with only a single row being passed in — although I’d like my methodology to handle multiple rows, in small numbers, as well.<\/p>\n
I like the idea of creating a custom or user-defined table type for this purpose. Rather than manipulating a staging table, it seems ideal to move the data into a variable shaped like that staging table, passing it down the line to additional steps, massaging it as I go, until eventually the TVP is ready for upsert.<\/p>\n
When you pass a TVP into a called function or a called sproc, however, you must use the READONLY keyword. You can’t massage the contents of the TVP in the called code and then see the changes reflected in the parent procedure. <\/p>\n
I guess another way to say this (if you’re a .NET programmer) is that it’s not like passing by reference in the .NET world — even though you are<\/em> passing by reference in the sense that a copy of the data is not made, and you do reap perf benefits from that fact ordinarily. (As you will see, I’m probably not going to be reaping that benefit in this solution.)<\/p>\n Still another way to say this (if you’re a T-SQL guy) is “you can’t declare a TVP as an OUT param or, if you could, it wouldn’t make any difference.”<\/p>\n So what’s the best way to resolve this?<\/p>\n At first I thought I would declare and fill the TVP and then pass it into a series of functions. Each function will do what it has to do, and return a TVP of the same type back. Naturally the return value, declared by and manipulated within the function, is readwrite for the function. The master proc would have code something like this:<\/p>\n DECLARE<\/span><\/span><\/span>@TestTVPInstance<\/span><\/span><\/span>as<\/span><\/span><\/span>dbo<\/span><\/span><\/span>.<\/span><\/span><\/span>TestTVP;<\/span><\/span><\/span><\/span><\/span><\/span><\/p>\n — use a function to update for each step, There are two problems with this approach:<\/p>\n 1 – There are a lot of things you just can’t do in functions, including dynamic SQL and using temp tables. Each step in the current process does a lot of “stuff”, in many cases using techniques not allowed in functions. The further I modify the original code for the new process from the original, the more difficult it will be to get exactly consistent results.<\/p>\n 2 – Looking at the above code, what you don’t see is the (large) number of columns to be UPDATEd in the real code. Given the (large) number of steps there are all together, each with its clumsy UPDATE statement, the master process starts to look like an ungainly mess. It will be really difficult to follow the flow. Contrast that with the nice visual presentation we had in the SSIS flow, and it’s not a happy thought. I want some structure in the code that gives you something of the sense of the process, just as the SSIS Designer provides it. Well, near as I can get.<\/p>\n So, I changed it up.<\/p>\n At the moment, I’m looking at a much cleaner presentation in the master proc, using a series of TVPs in the master procedure, each of which is the input to one step and results in the filling of another one. The code looks like this at the top:<\/p>\n DECLARE <\/span><\/span><\/span>@TestTVPInstance <\/span><\/span><\/span>as <\/span><\/span><\/span>dbo<\/span><\/span><\/span>.<\/span><\/span><\/span>TestTVP<\/span><\/span><\/span>, The first step fills the first TVP using the input data, which is coming in from the external application, and of course is not a TVP itself (it’s actually XML, but that’s another blog post).<\/p>\n Thereafter, the procedure’s steps look something like this (with more descriptive proc names, obviously) ;<\/p>\n — use a procedure to manipulate INSERT <\/span><\/span><\/span>INTO <\/span><\/span><\/span>@TestTVPInstance_2 <\/span><\/span><\/span>— many, many more such calls… do lots of work… <\/span><\/span><\/span>— followed by some work done to update a “real”Moving a TVP down a line<\/h2>\n
— like this:
<\/span><\/span><\/span>UPDATE <\/span><\/span><\/span>@TestTVPInstance
<\/span><\/span><\/span> SET <\/span><\/span><\/span>col2 <\/span><\/span><\/span>= <\/span><\/span><\/span>tvp2<\/span><\/span><\/span>.<\/span><\/span><\/span>col2
<\/span><\/span><\/span>FROM <\/span><\/span><\/span>dbo<\/span><\/span><\/span>.<\/span><\/span><\/span>TestUpdateTVPByFunction<\/span><\/span><\/span>(<\/span><\/span><\/span>@TestTVPInstance<\/span><\/span><\/span>) <\/span><\/span><\/span>tvp2
<\/span><\/span><\/span> JOIN <\/span><\/span><\/span>@TestTVPInstance<\/span><\/span><\/span>tvp1
<\/span><\/span><\/span> ON <\/span><\/span><\/span>tvp1<\/span><\/span><\/span>.<\/span><\/span><\/span>col1 <\/span><\/span><\/span>= <\/span><\/span><\/span>tvp2<\/span><\/span><\/span>.<\/span><\/span><\/span>col1<\/span><\/span><\/span>;<\/span><\/span><\/span><\/p>\nChaining TVPs <\/h2>\n
<\/span><\/span><\/span>@TestTVPInstance_2 <\/span><\/span><\/span>as <\/span><\/span><\/span>dbo<\/span><\/span><\/span>.<\/span><\/span><\/span>TestTVP<\/span><\/span><\/span>,
<\/span><\/span><\/span>— lots more here…
<\/span><\/span><\/span>@TestTVPInstance_N <\/span><\/span><\/span>as <\/span><\/span><\/span>dbo<\/span><\/span><\/span>.<\/span><\/span><\/span>TestTVP<\/span><\/span><\/span>;<\/span><\/span><\/span><\/p>\n
— and bring back into a second copy:<\/span><\/p>\n
<\/span><\/span><\/span>EXEC <\/span><\/span><\/span>dbo<\/span><\/span><\/span>.<\/span><\/span><\/span>TestUpdateTVPByProc <\/span><\/span><\/span>@TestTVPInstance<\/span><\/span><\/span>;<\/p>\n
<\/span><\/span><\/span>INSERT <\/span><\/span><\/span>INTO <\/span><\/span><\/span>@TestTVPInstance_N
<\/span><\/span><\/span>EXEC <\/span><\/span><\/span>dbo<\/span><\/span><\/span>.<\/span><\/span><\/span>TestUpdateTVPByProc_N <\/span><\/span><\/span>@TestTVPInstance_N_minus_1<\/span><\/span><\/span>;<\/p>\n
<\/span><\/span><\/span>— table using the final intermediate instance:
<\/span><\/span><\/span>EXEC <\/span><\/span><\/span>dbo<\/span><\/span><\/span>.<\/span><\/span><\/span>UpdateSomeTableWithComplicatedLogic <\/span><\/span><\/span>