Updating tables in sql from another table
It also used to shorten the duration of long running transactions with minimum base table locking by taking the data, processing it and finally opening a transaction to perform the change on the base table.
This approach is applicable to add non-clustered and clustered indexes to the temporary tables, which both can enhance the performance of retrieving data from these tables if the indexes are chosen correctly.
But you should take into consideration that modifying temporary tables many times in your code may lead to statistics getting out of date.
This would require manually updating for these statistics, or enabling Trace Flag 2371.
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.
Temporary tables can be used to enhance stored procedures performance by shortening the transaction time, allowing you to prepare records that you will modify in the temporary table, then open a transaction and perform the changes.
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.
Also you can derive from the execution plan, that the table with non-clustered index took the largest time (1063 ms) and resources (47% of the overall execution) during the table insertion process opposite to the table with clustered index insertion that took less time (827 ms) and resources (32 % of the overall execution): In the previous script we created a non-clustered index after filling the temp table and the clustered index before filling the temp table.
But is it different when we create the index before or after filling the temp table?