what are the difference between this two type of tables? ( # and @ )

Question

What are the difference between a #myTable and a declare @myable table

Answer 1

Table variables are all well and good when dealing with relatively small datasets, but be aware that they do not scale well. In particular, a change in behaviour between SQL Server 2000 and SQL Server 2005 resulted in performance dropping through the floor with large datasets.

This was a particularly nasty gotcha for me on one particular occasion with a very complex stored procedure on SQL Server 2000. Research and testing indicated that using table variables was the more performant approach. However, following an upgrade to SQL Server 2008 performance degraded considerably. It took a while to cotton on to the use of table variables as the culprit because all the prior testing etc had ruled out temp tables as being any faster. However, due to this change between SQL Server versions, the opposite was now true and following a significant refactoring, what was taking well into double digit hours to complete started completing in a couple of minutes!

So be aware that there is no definitive answer as to which is best - you need to assess your circumstances, carry out your own testing, and make your decision based on your findings. And always re-evaluate following a server upgrade.

Read this article for more detailed information and sample timings - http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx

Update: On a separate note, be aware that there is also a third type of temporary table - ##xyz. These are global and visible to all SQL Server connections and not scoped to the current connection like regular temporary tables. They are only dropped when the final connection accessing it is closed.

Answer 2

The table #tabel1 is a local temporary table stored in tempdb. ##table1 is global temporary table stored in tempdb.

and @table is table variable.

Check the link for their differences

Answer 3

Please see:

• TempDB:: Table variable vs local temporary table

• Table Variables In T-SQL

It is often said that @table variables are kept in memory as opposed to tempdb; this is not necessarily correct.

Table variables do not have statistics, and this can affect performance in certain situations.

Answer 4

'#myTable is a temporary table and benefits from being able to have constraints and indexes etc and uses more resources.

@myTable is a table variable that you define as having one or more columns. They use less resources and are scoped to the procedure you use them in.

In most cases where a temp table is used a table variable could be used instead which may offer performance benefits.

Answer 5

In a stored procedure, you often have a need for storing a set of data within the procedure, without necessarily needing that data to persist beyond the scope of the procedure. If you actually need a table structure, there are basically four ways you can "store" this data: local temporary tables (#table_name), global temporary tables (##table_name), permanent tables (table_name), and table variables (@table_name).

Should I use a #temp table or a @table variable?

Both local and global temporary tables are physical tables within the tempdb database, indexes can be created .Because temp tables are physical tables, you can also create a primary key on them via the CREATE TABLE command or via the ALTER TABLE command. You can use the ALTER TABLE command to add any defaults, new columns, or constraints that you need to within your code.

Unlike local and global temporary tables, table variables cannot have indexes created on them. The exception is that table variables can have a primary key defined upon creation using the DECLARE @variable TABLE command. This will then create a clustered or non-clustered index on the table variable. The CREATE INDEX command does not recognize table variables. Therefore, the only index available to you is the index that accompanies the primary key and is created upon table variable declaration. Also transaction logs are not recorded for the table variables. Hence, they are out of scope of the transaction mechanism