User Defined Table Types :- SQL Server 2008

We sometimes need to use table variable in our query.
Every time in the query we have to define the table variable.



Example

declare @test table
( id int,
data varchar(10))

select * from @test



We can make it easy now!
What happened if we can create user defined type of table which has definition of our table variable .
Its now possible in SQL Server 2008

We will create table type named as [test]

create type [test] as table
(id int,
data varchar(10))

Now we can use it in our declare statement easily. We dont need to define table variable every time now.

declare @test as test
select * from @test

Points to take care while using table type

1.Default values are not allowed
2.Primary key must be a persisted column.
3.Check constraint can not be done on non persisted computed columns
4.Non clustered indexes are not allowed.
5.It can't be altered. You have to drop and recreate it.