Featured Article(s)
How to Manage… Without Micromanaging
As leaders and managers, we struggle with how to manage without micromanaging. One effective method is to manage the situation versus the people. When we focus on the overall goal or solution instead of the insignificant details and tasks, everything runs smoother.
A Use for Select *
Today I’m sharing a tip from Michael who uses SELECT * as a technique to automate code working with Table Value Parameters as input sets for Stored Procedures.
He uses views as a prototype to pass along the structure of an Input Table Parameter for stored procedures. In order to use a table value parameter from .net it is easiest to have a DataTable with the same schema.
Using SELECT * to from the view without returning rows generates a DataTable with the exact same schema as the underlying User Defined Table Value Parameter resulting in accurate code every time. Because he has to drop and recreate the view and stored procedures in order to modify the User Defined Table Type, he does not experience caching issues
Michael Writes:
Here is a use of select * that I believe hasn’t been mentioned yet. I used select top(0) * from in order to get metadata about a table. When you do an initial load into .Net DataTables the DataTable is created with all the columns cast as the proper type and for string fields, the Max Length is already filled out. This helps ensure that certain errors are found at the application level versus at the database level.
Doing this with a wrapper around a view ensures that the columns are created in a specific order. This is necessary if you are wanting to pass the .Net DataTables to SQL Server stored procedures using user defined Table Types where the .Net DataTable columns have to be in the exact same order in order for SQL Server to work with it properly which is one of the fastest ways to save a lot of data from an application.
Passing .Net DataTables to SQL Server is one of the fastest ways to save data. The times were comparable if I remember right to bulk copy. However, in order to use this method you have to create a stored procedure that takes a user defined table type as its parameters.
So, now you can have a .Net Table with FieldA , FieldB And a Table Type defined as fieldA, fieldB.
This gets the stored procedure to fire. However, say you created the .Net table as FieldB, Field. Now SQL Server is going to map .Net field FieldB to Table Type FieldA and FieldA to B. This can cause errors in conversion or out of place data. Maybe this has been fixed since I used it with 2008, but if you look at the syntax that SQL Server passes when passing .Net DataTable to a sql server stored proc, the names of the fields are not sent. So it maps them in the order that they were defined in the user defined Table Type which may be different then the .Net DataTable definition even if the columns were identically named.
So, to get around this, I created view’s that returned the columns to .Net DataTables in the exact order as represented by the User Defined Table Types.
We wanted to write the application so that it was smart where all of the data definitions came from the database and those definitions were not buried in code. That way DBA’s could further manipulate and tweak the database as they saw fit as the Application on those tables was now decoupled from the actual column names on the actual tables as the view could also be used to return some column data that did not need to be stored in the database(i.e columns that seemed to always contain a certain value, but we weren’t sure if we could remove from the database).
Thanks Michael for sharing this tip. While it doesn’t follow current trends in database access it certainly results in good performance. I think allowing tables as input parameters to stored procedures is one of the best additions to SQL Server 2008.
Share your comments with us by writing to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
How to Use SQL Server’s Extended Events and Notifications to Proactively Resolve Performance Issues
read more)