As of SQL Server 2008, you can create user-defined datatypes as tables, which comes in pretty handy when you want to pass an entire table as an argument to a stored procedure or a function. And as a bonus, you get get really pretty, highly readable code!

As you know, you can define temp tables in variables. And just like with other datatypes, you can use the table datatype as a template to define your own table type, a user-defined datatype that is actually a table. Here’s how:

CREATE TYPE dbo.wordList AS TABLE (
    word    varchar(50) NOT NULL,
    PRIMARY KEY CLUSTERED (word));

Note that table types, like all user-defined types come with a schema assignment.

From here on, you can use dbo.wordList as if it were any user-defined datatype.

Performance aspects

A table type variable is equivalent to a table variable. Remember that this means it isn’t optimized for large loads, and you should only use table variables with very little data in them (a commonly agreed guideline is 100 rows or less).

Example: A function that joins a table of values

Here’s a function that merges a list (a table) of words into a single string. Note that in order to be able to use a table type as an argument to the function, we have to include the READONLY keyword, so the function code cannot make changes to the contents of the @wordList variable.

CREATE FUNCTION dbo.fnJoinWordList(
    @wordList     dbo.wordList READONLY,
    @separator    varchar(10)=', ')
RETURNS varchar(max)
AS

BEGIN;
    --- The output string that will contain the joined values:
    DECLARE @joined varchar(max);

    --- A recursive common table expression that will pick one
    --- value at a time from the @wordList table and join them
    --- together in @joined:
    WITH rcte (n, joined)
    AS (
        --- Anchor: n=0, joined=NULL
        SELECT 0 AS n, CAST(NULL AS varchar(max)) AS joined
        UNION ALL
        --- The recursion:
        SELECT rcte.n+1 AS n,
            CAST(ISNULL(rcte.joined+@separator, '')+
                work.word AS varchar(max)) AS joined
        FROM rcte
        CROSS APPLY (
            --- With ROW_NUMBER(), we're assigning a
            --- unique number for each row in @wordList
            SELECT ROW_NUMBER() OVER (ORDER BY word) AS n, word
            FROM @wordList
            ) AS work
        WHERE rcte.n+1=work.n)

    --- The output:
    SELECT TOP 1 @joined=joined FROM rcte ORDER BY n DESC

    RETURN @joined;
END;

And here’s how to call the function using a table type variable:

DECLARE @list dbo.wordList;
INSERT INTO @list VALUES ('a'), ('b'), ('c');
SELECT dbo.fnJoinWordList(@list, ', ');