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.


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.

No comments:

Post a Comment

Popular Posts