User defined Table Type UDDT and Passing Table Valued Parameter

 

Performance

When using less than a thousand rows the UDDT variable will normally out perform a temporary table as the start up cost for a UDDT is far less as it has already been defined as a data type within SQL, where as with the temporary table this has to be created before it can be used. UDDTs also benefit from the same level of caching as SQL server gives to temporary tables.

------------------ First create a table type-----------

create type ty_prac1 as table

(

id int,

name varchar(max)

)

-------------------Create a table------------

create table tb_prac

(id int,

name varchar(max)

)

------------- Create procedure with readonly --------------------------

alter procedure prac

(

@ram ty_prac1 readonly)

as

insert into tb_prac(id, name) select * from @ram

---------------------Pass table valued parameter (remember insert into)------

Declare @passpara ty_prac1

insert into @passpara select 1,'rajan' union all

select 2,'manasha' union all 

select 3,'nischal' union all

select 4,'Bishakha'

exec prac @passpara

select * from tb_prac