SQL Server provides the User Defined Table Types as a method to create a pre-defined temp table. Additionally, because they are a defined object in a database, you can pass them around as parameters or variables from one query to another. They can even be read only input parameters to stored procedures.
In sql you might do something like the following to use a User Defined Table Type. I’ll first create one as follows:
CREATE TYPE dbo.Strings AS TABLE
(
String VARCHAR(256) NOT NULL
,PRIMARY KEY (string)
)
GO
Now that you have a table data type defined, you can create a variable of that type, and utilize it.
DECLARE @strings Strings
INSERT INTO @strings (string)
VALUES
('This')
,('is')
,('a')
,('good')
,('day')
,('to')
,('live.')
SELECT * FROM @strings
This code returns the following table:
a
day
good
is
live.
This
to
You may notice that the words are in a different order than when entered. That is because we created a primary key on the string column. As a result, the data is returned in alphabetical order. Moreover, if we attempt to include the same word more than once in our values list, the insert statement fails due to a duplicate key violation.
Once I create the variable @strings, I can pass it around, and even include it as an input parameter to a stored procedure.
I found it interesting when using a table value type as a parameter to a stored procedure, being called from ADO.Net. Running a trace on SQL Server I found that, although I didn’t write the code, ADO.net transforms the DataTable object I am passing to the stored procedure, into the same code as you see above. Essentially, ADO.Net writes all the code needed to transform data from a System.Data.DataTable into an SQL Server User Defined Table Type, and inserts the values found in the data table. That saves a lot of time coding. Since the data is all passed to SQL Server as a parameter, it limits the potential for SQL Injection.
Cheers,
Ben