What is table type in SQL Server and how do you use it?

Table type is a special data type. It is used for storing a result set that can be processed later and therefore is a temporary storage of set of rows. This has been available since the release of SQL Serer 2015.

Like you declare, say an integer value by saying, 'declare @ int' you can declare a variable as of table type. One of the best practices is to store not more than 100 rows of data. If you want to store more, use temporary tables.

Table variables can be referenced in a Select Query.

With the above basic information let us see how to create it and use it.

CREATE TYPE MyTableType AS TABLE
(
    Name      varchar(10) NOT NULL,
    ValueDate date        NOT NULL,
    TenorSize smallint    NOT NULL,
    TenorUnit char(1)     NOT NULL,
    Rate      float       NOT NULL
    PRIMARY KEY (Name, ValueDate, TenorSize, TenorUnit)
);

The above code creates a table type called, MyTableType

Let us see how we can use it. Remember as we said earlier it is a data type and can be declared as a variable.

Now we declare it as follows:

Declare @x as MyTableType

Since @s is like a table we can insert values into the columns as shown here:

Insert @x values  ('Jay', '1/1/2014', 6, 'P',2.5); 

The values should match the definition of the table type.

Now we do a selection of columns in the table @x

SELECT * from @x

Here is a screen shot of the T-SQL and the result

That is all there is to it.