Stuff I learned today – Table Types

Since SQL Server 2000, you’ve been able to create variables of type TABLE, but you needed to define that table structure when (and each time) you declared the variable. You also couldn’t pass these table variables into a stored procedure.

SQL Server 2008 introduced the user-defined TABLE datatype. Now you can create and store custom-made TABLE types for use with variables and as parameters into your stored procedures. (They cannot, however, be used as the data type for a column within a table.)

TABLE types are created and dropped using the CREATE TYPE and DROP TYPE commands. There’s no ALTER TYPE command, so if you need to change a type, drop it and recreate it.

CREATE TYPE dbo.BookDetails
AS TABLE (
PatronID int NOT NULL,
BookID	int NOT NULL,
Title	varchar(250),
Author 	varchar(100));
GO

So, why would you want to pass a table into a stored procedure? Imagine you work for a library and you’re writing an app to record books that people borrow, and you use a stored procedure to insert the book details into the BooksBorrowed table. Previously, if a patron borrowed multiple books, you’d have to call that procedure once for each record, right? Maybe like this:

CREATE PROCEDURE CheckOutBooks
@CustomerID int, @BookID int, @Title nvarchar, @Author nvarchar
AS
INSERT INTO BooksBorrowed (PatronID, BookID, Title, Author, DateBorrowed)
VALUES (@PatronID, @BookID, @Title, @Author, GETDATE());
GO

-- Now we have to run it multiple times to check out all the books Patron 1 wants

EXEC CheckOutBooks (1, 45, 'The Grapes Of Wrath', 'John Steinbeck');
EXEC CheckOutBooks (1, 532, 'Gone With The Wind', 'Margaret Mitchell');

Not very efficient, right? Now let’s try it with the BookDetails TABLE type we created earlier.

CREATE PROCEDURE CheckOutBooks
@Books BookDetails READONLY
AS
INSERT INTO BooksBorrowed (PatronID, BookID, Title, Author, DateBorrowed)
SELECT PatronID, BookID, Title, Author, GETDATE() from @Books
GO

-- Now we only have to run the procedure once

DECLARE @vBooks BookDetails;

INSERT INTO @vBooks VALUES (1, 45, 'The Grapes Of Wrath', 'John Steinbeck'),
			   (1, 532, 'Gone With The Wind', 'Margaret Mitchell');

EXEC CheckOutBooks @vBooks;

This reduces round-trip overhead and speeds up the entire transaction. Having a single, pre-defined definition of the TABLE type also means consistency across applications. Consistency=good. Fast transactions=gooder.

So, look it up. Play with it. Have fun, people.

Also recommended: