1
CREATE TABLE #Employee
(
   EmployeeID INT,
   Name VARCHAR(50),
   UNIQUE CLUSTERED (EmployeeID)
)

DECLARE @Employee TABLE
(
   EmployeeID INT,
   Name VARCHAR(50),
   UNIQUE CLUSTERED (EmployeeID)
)

Are there any differences in performance?

I basically want to know which is faster...

JJ.
  • 9,580
  • 37
  • 116
  • 189
  • 1
    See this: http://stackoverflow.com/questions/27894/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server – JohnS Jun 22 '15 at 21:41
  • 1
    The big issue people run into is cardinality. The query engine always things table variables have exactly 1 row, so it may not pick the best plan. – Bacon Bits Jun 22 '15 at 23:36

1 Answers1

0

You can create indexes on temp tables but you cannot create indexes on variable tables. In my experience temp tables are faster and produce much better query plans. SQL server keeps statistics on temp tables. You can create primary and/or unique indexes on var tables but they are used to enforce uniqueness. Without stats I don't think SQL Server will use an index for seeks. In 2014 SQL Server allows the creation of non primary indexes where we would have to see how it uses the indexes.

benjamin moskovits
  • 5,261
  • 1
  • 12
  • 22
  • 1
    I would assume 2014 (and the older versions too) to use indexes, but first it has to know that there is more than 1 row -- and sometimes SQL Server can have that information, but it's mostly it's a rare case that happens due to recompilations. – James Z Jun 23 '15 at 04:19