Implementing table datatype in stored procedure | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Implementing table datatype in stored procedure

Frndz,
How do I use & implement table datatype as an replacement of temp tables & cursors in my stored procedures? Regards,
Vipul Mehta

read about them in BOL.
check this:
declare @temptable table(col1 int,col2 int)
insert into @temptable select 1,2
select * from @temptable
this way you can deal with table datatype
quote:Originally posted by mehtavipulk Frndz,
How do I use & implement table datatype as an replacement of temp tables & cursors in my stored procedures? Regards,
Vipul Mehta
If you are dealing with large amount of data, replacing temp table with table variable might not gain performance benefit but still it will be better than use of cursor.
Hi, Roji, MVP has written a Beautiful article regarding Temp table and Table Variable……. Please refer that http://toponewithties.blogspot.com/2004/08/difference-between-table-variable-and.html madhu
Check this article:
http://www.developer.com/db/article.php/3414331 Article shows the approach for table datatype in 2000 which can be used in almost same manner in 2005
quote:Queries that contain table variables do not generate parallel query execution plans. Performance can be affected by the presence of very large table variables, or table variables in complex queries.

This variable is NOT a memory structure only, not disk like a #temp table. If you have a wide table with lots of rows you are going to have trouble for memory resource. KBAhttp://support.microsoft.com/default.aspx?scid=KB;EN-US;Q305977& fyi. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
Satya, check out Q4 inhttp://support.microsoft.com/kb/305977/en-us
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
Thanks Frank, its a typo on my part I acknowledge the correction. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

http://www.aspfaq.com/show.asp?id=2475 Madhivanan Failing to plan is Planning to fail
]]>