Reader Comments on Table Variables
Let’s wrap up this week with some comments from our readers on Table Variables.
Michael:
I wanted to send out a gotcha with Table Types as Inputs to stored procedures. The order of the columns in your .Net application need to exactly match what you have defined for your Table Type. Otherwise it will fail(casting to a data type you don’t expect) or you’ll get unexpected results(values in columns flip flop). I once had the same columns exactly between the two, but since the order wasn’t the same I ran into a casting error. If they were both strings and the values were short enough I might have taken longer to discover this. If I remember right, this is by the way it passes the .Net object to SQL Server. It doesn’t pass a binary object in. It does something like this where it doesn’t specify column names:
Declare @p1 YourTableType
Insert Into @p1 Values (ValueList)
Insert Into @p1 Values (ValueList)
Exec yourprocname @p1
The way I solved this was to create a view that explicitly defined the fields in the proper order and then have my code initialize the DataTable with a Select Top(0) * from View
But using this method, I found was one of the fastest methods when sending lots of data where you had to do a combination of inserts, deletes and updates.
The other minor gotcha I found in .Net DataTables was if you updated a column value to the same value, it counts it as having changed that column. So you could end up sending more than you need potentially.
Mark:
Thanks for bringing yet another VERY useful tip to readers. In fact the table variable parameter in stored procedures is one of my favourite additions since SQL 2008. Back in the ‘90’s dynamic SQL/parameterized queries was the norm for creating/updating table data that may or may not have all the fields filled. However, this approach leaves a big gaping hole to SQL injection attacks, not to mention far more cumbersome to use than stored procedures. Stored procedures offer many benefits, and one of my favourite features of SQL Server as opposed to Oracle and others, including inherent security against SQL injection, but every new field format or new field in a standard create/update operation required intervention in every layer of a standard 3-tier system (DB, data access, application logic). Sure the DB and application logic are still necessary, but finally we can go back to the ease of the ‘90’s where the data access layer doesn’t need reworking, especially when it’s an annoying simple field format update like increasing a nvarchar length. And on top of this, creations/updates in the stored procedure are super simple as we can deal with data sets from the table parameter…particularly useful when the application logic calls for adding multiple records at the same!
On another point, addressing a topic you mentioned several days ago, a common scenario I often encounter where table variables within a stored procedure are useful is in application permission resolution. I tend to follow a Microsoft “File Manager” approach to permission management (i.e. folders/files etc.). That is, my applications tend to follow a hierarchic structure where user permissions can be defined at any level and inherited by all functionality contained within them (ex. CRMClientsContact history). This means that I “define” permissions in a GUI with inheritance, and then “resolve” the permissions in a calculated table to make it quick to determine a user’s permissions on any particular interface element. Given this simple example, I could create a single SQL statement that resolves all permissions for all user groups. However, I normally have exceptions defined in other sources, for example an employee takes an extended holiday and delegates his user access to another employee temporarily. In this scenario, it is much clearer to use a table variable to accumulate all permissions from all the definitions and then apply them to the calculated table in one go.
Anyway, just thought I’d point out these two benefits to in memory table variables.
Now is your turn to get into the conversation. Feel free to add your commet below, or drop an Email to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
Extending the Value of SCOM with Spotlight on SQL Server
read more)