MSSQL table variables don't show statistics for their performance hits in Extended Events in SSMS while temp tables do and tend run faster.

The only reason to use a table variable instead of a temp table is if the dataset inside is small. Only then might a table variable shine and perform better than a temp table. This example...

DECLARE @Whatever TABLE
(
   WhateverID int PRIMARY KEY CLUSTERED,
   Green money
)
INSERT INTO @Whatever (WhateverID, Green) VALUES (1, 13.42)
INSERT INTO @Whatever (WhateverID, Green) VALUES (2, 42.13)
SELECT * FROM @Whatever

 
 

...returns the same results as this:

DECLARE @Whatever TABLE
(
   WhateverID int NOT NULL identity(1,1),
   Green money
)
INSERT INTO @Whatever (Green) VALUES (13.42)
INSERT INTO @Whatever (Green) VALUES (42.13)
SELECT * FROM @Whatever

 
 

However, you can probably see that they are not the same, huh? This content is a snippet from a piece of greater talk on improving SQL performance by John Sterrett of Linchpin People Monday night at the Austin .Net User Group. The talk included a lot of content, but I suspect I will (probably) present the whole of the talk in a series of chunks across many blog postings.