table data type

[Pattycake245]

I have a stored procedure where I use 3 or 4 temp tables. I am looking to improve performance if possible. What I can't seem to find anywhere is an example of the proper syntax for declaring a variable as type table. I would create table variables instead of my temp tables if it is better from a performance standpoint. Can anyone shed some light or point me in the direction of where I could find such an example?

thx

 

[zhavic]

For example:

DECLARE @my_table TABLE ( Column1 integer, Column2 char(10), Some_other_column datetime )

INSERT INTO @my_table VALUES ( 1, 'blahblah', GETDATE() )

INSERT INTO @my_table SELECT * FROM somewhere

SELECT * FROM @my_table

SELECT *
FROM some_other_table Y
INNER JOIN @my_table X ON X.Column1 = Y.some_column



Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[nigelrivett]

See table variabe in bol.


For small tables they will be faster than temp tables - especially if you have a system that creates a lot on different spids.

They will be slower for larger tables as they have fewer optimisation options and you can't create indexes after they are populated.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[jby1]

Just as a matter of interest, at what sort of size would it become advantageous to use temporary tables instead of table variables? I have recently discovered table variables and now they are everywhere in my code, but I am concerned that maybe I am using them inappropriately in places!

 

[Pattycake245]

thanks Zhavic, nigelrivett. My temp tables are fairly small so I will explore this further.