Before SQL Server 2008 there was no native method to pass sets to a stored procedure or function. With SQL Server 2008 they introduced the User Defined Table Type. You could define a type just like a table, and pass in data using a variable of that type allowing sets of data to be passed to stored procedures like never before.
Using the User Defined Table Type your data is strongly typed which has a lot of value. You don’t have to manipulate it in any fashion to join or merge to native tables. The Merge command was also released about the same time allowing you to pass in a set of data and have SQL Server merge the necessary changes (Delete, Update, Insert) with persisted data.
One of the things I have found when trying to optimize data processing on a remote linked server is that you cannot take advantage of the table type variable. The reason for this is that the definition of a table type variable is at the database level. You cannot create a table type in one database and share it in another database.
So, when you are using SQL Server to do a lot of your data logic, or sharding, it becomes complicated and difficult to manage. Performance degrades, and implementations are less than optimal and often perform poorly if executing against a different linked instance of SQL Server.
How would you solve this problem? I’ve seen a number of interesting implementations such as creating a stored procedure on the remote server, and passing XML formatted sets (old school). I have also seen passing dynamically generated SQL, which results in heavy use of the memory for ad hoc queries, which is a limited resource. I have seen cursors used locally calling a stored procedure with scalar variables instead of set based variables. What would your solution be if you had to perform these tasks in the SQL Engine?
Share your ideas here or drop an Email to btaylor@sswug.org.
Cheers,
Ben