There are four main types for the temporary tables; The SQL Server Database Engine can distinguish between the same temporary tables created while executing the same stored procedure many times simultaneously by appending a system-generated numeric suffix to the temporary table name.This is why the local temporary table name can’t exceed 116 characters.Also, not every temporary table needs adding indexes, as it depends on many things such as the way that this temporary table will be called, joined with other huge tables or if it will be part of a complex stored procedure.

updating table variables sql 2016-48updating table variables sql 2016-41updating table variables sql 2016-30

We will concentrate in checking the time consumed by each case and the generated execution plan.

To evaluate the consumed time, we will declare @Start Time variable before each execution, set its value to GETDATE () and at the end of each execution, we will print the date difference (in ms) between the current time and the start time.

If the table is not in the current database or is not contained by the schema owned by the current user, the database and schema must be explicitly specified.

ALTER COLUMN Specifies that the named column is to be changed or altered.

Although both temporary tables and table variables are stored in the Temp DB database, there are many differences between them such as: In addition to that, SQL Server column level statistics are generated automatically against temporary tables, helping the SQL Server Query Optimizer to generate the best execution plan, gaining the best performance when querying that temporary table.

But you should take into consideration that modifying temporary tables many times in your code may lead to statistics getting out of date.

But is it different when we create the index before or after filling the temp table?

To answer this question let’s perform the following test in which we will check the consumed time in all cases; adding a non-clustered index before filling the temp table, adding a non-clustered index after filling the temp table, adding a clustered index before filling the temp table and adding a clustered index after filling the temp table: It is clear from the result generated from executing the previous script that it is better to create the non-clustered index after filling the table, as that is 1.2% faster, and create the clustered index before filling the table, as that is 2.5% faster, due to the mechanism that is used to fill the tables and create the indexes: Checking the execution plan, the result will show us that creating the clustered index before the insertion consumes 15.7% of the overall execution, where creating it after the insertion will consume 22% of the overall execution.

This would require manually updating for these statistics, or enabling Trace Flag 2371.