declare table vs create table # in sp

What are the differences between these two? They seem to work the same.

Is declare table also using tempdb database? Or does it use pure memory?
I noticed that I don't need to drop the declared table.

Is declare table always better than create table #xyz syntax?

declare @accounts table (
account_id numeric(10,0) not null primary key)

create table #accounts (
account_id numeric(10,0) not null primary key)

Sometimes the performance impact can be huge. I have seen table variables not generating parallel query plans on a large SMP box while in the same circumstances, temporary tables did create them.

Also SQL Server maintains statistics for queries that use temporary tables but not for queries that use table variables. In general, you should use table variables for smaller data set queries.

HTH
-Rajeev Lahoty

Table Variables:
Advantages:
in memory so access is faster.
No recompilation of stored procedures (Thus reducing CPU cost)
Disadvantages:
If the table size is big then sQl Server automatically writes to Tempdb.
There can be no indexes and statistics.

So if data returned is more, then create temptable

and if data returned in is very less then create table variable.