We help IT Professionals succeed at work.

Difference between #MyTable and Declare #MyTable Table()

clintnash
clintnash asked
on
436 Views
Last Modified: 2008-04-24
Can someone explain to me the difference between
Declare @Season Table()
Create Table #Season

I realize one creates a temp table and the other creates a variable of type table, in the end they basically do the same thing, are there guidelines as to which one should be used over the other, memory concerns, locking, etc.  Any input you have is appreciated.

Thanks,
Clint...

Comment
Watch Question

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
The other side of that is that temp tables are created on disc and are therefor slower.  Also, depending on the version of SQL Server, sometimes temp tables don't get dropped properly, so you should always explicitly drop the table when you are done with it.  Using memory tables avoids these issues.

Author

Commented:
Thanks, that clarified it.

Commented:
Declare @Season Table()
-Good for short term, low row count tables
-It can be good for larger tables just depending on how much work you are going to do on them
but just depends on your memory limitations because these tables are created in memory
-Only available in scope of sp or function

Create Table #Season
-Tables are created in the tempdb
-More IO intensive
-Good for extremely large tables that don't play well with memory
-Can be used across multiple sps or functions


In General I use the Declare @Season Table(), but I have some dbs that we do some really large temp tables (not by my design) like 30 gigs plus and they have to be in the #Season method.

Commented:
I guess I need to answer faster... hehe
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.