Sql server® 2012 t-sql fundamentals

Table Types

SQL Server 2008 and SQL Server 2012 support table types. When you create a table type, you pre-

serve a table definition in the database and can later reuse it as the table definition of table variables 

and input parameters of stored procedures and user-defined functions. 

www.it-ebooks.info

---

358  

Microsoft SQL Server 2012 T-SQL Fundamentals

For example, the following code creates a table type called dbo.OrderTotalsByYear in the current 

database.

IF TYPE_ID('dbo.OrderTotalsByYear') IS NOT NULL 

  DROP TYPE dbo.OrderTotalsByYear; 

 

CREATE TYPE dbo.OrderTotalsByYear AS TABLE 

( 

  orderyear INT NOT NULL PRIMARY KEY, 

  qty       INT NOT NULL 

);

After the table type is created, whenever you need to declare a table variable based on the table 

type’s definition, you won’t need to repeat the code—instead you can simply specify dbo.OrderTotals-

ByYear as the variable’s type, like this.

DECLARE @MyOrderTotalsByYear AS dbo.OrderTotalsByYear;

As a more complete example, the following code declares a variable called @MyOrderTotalsByYear 

of the new table type, queries the Orders and OrderDetails tables to calculate total order quantities by 

order year, stores the result of the query in the table variable, and queries the variable to present its 

contents.

DECLARE @MyOrderTotalsByYear AS dbo.OrderTotalsByYear; 

 

INSERT INTO @MyOrderTotalsByYear(orderyear, qty) 

  SELECT 

    YEAR(O.orderdate) AS orderyear, 

    SUM(OD.qty) AS qty 

  FROM Sales.Orders AS O 

    JOIN Sales.OrderDetails AS OD 

      ON OD.orderid = O.orderid 

  GROUP BY YEAR(orderdate); 

 

SELECT orderyear, qty FROM @MyOrderTotalsByYear;

This code returns the following output.

orderyear   qty 

----------- ----------- 

2006        9581 

2007        25489 

2008        16247

The benefit of the table type feature extends beyond just helping you shorten your code. As I 

mentioned, you can use it as the type of input parameters of stored procedures and functions, which 

is an extremely useful capability.

www.it-ebooks.info

---