Quick one today:

Temporary tables and table variables are a great addition in Sql Server. Pretty much every database code take advantage of this feature, for right reasons. Now, with this increased usage, comes responsibility to do two things:

• Use the feature correctly & efficiently
• Create efficiencies in TempDB database.

Keep in mind that for

• Indexes and Stats: Temporary tables you can create indexes and statistics, but for table variables statistics are not an option [indexes could be created, but only as column constraint]. See below:

  • 1 2 3 4 5 6 7

    -- --  Create table variable -- DECLARE @tvariable TABLE (      ID   INT   NOT NULL   PRIMARY KEY CLUSTERED )

  • Because of lack of statistics, Sql Server Optimizer it cannot fully come up with the best query plan to execute code with table variables.
  • “Estimated rows” and “Actual rows” will vary a lot. So keep an eye out to monitor such statements.

• Transaction Integrity: Temporary tables participate in TRANSACTION and follow the rules to maintain transaction integrity. Table variables are just like any other variables (a.k.a integer or character variables) and do not participate in TRANSACTION.
  • This is both good and bad.
  • When you ROLLBACK, data in table variables is not rolledback.
  • So, you do not want to use them for maintaining integrity of transactions
  • But they are great for troubleshooting purposes.

Hope this helps,
_Sqltimes