Updating tables in sql from another table
Adding indexes to the temporary tables will enhance its performance if the index is chosen correctly, otherwise, it can cause performance degradation.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.This unique introductory SQL tutorial not only provides easy-to-understand SQL instructions, but it allows you to practice what you learn using the on-line SQL interpreter.You will receive immediate results after submitting your SQL commands.You will be able to create your own unique tables as well as perform selects, inserts, updates, deletes, and drops on your tables.This SQL tutorial currently supports a subset of ANSI SQL.In this article, we will see how we can benefit from the ability to add clustered and non-clustered indexes in the temporary tables.Defining PRIMARY KEY and UNIQUE KEY constraints during temporary table creation will enable SQL Server Query Optimizer to always be able to use these indexes.
In this case, it is better to explicitly define a clustered or non-clustered index that could be configured as a non-unique index.
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.
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.
The below script will create the previously mentioned three temporary tables; temp table without an index, temp table with a non-clustered index and temp table with clustered index and fill it with 100k records from the Country Info test table then retrieve these records from the tables: Executing the previous script, the result will show us that in our case, adding a non-clustered index is worse than having the table without index by 1.2 times in our case, but adding a clustered index will enhance the overall performance by one time in our case as in the below timing comparison in ms: Checking the generated execution plan using the Apex SQL Plan application after the execution, we will see that, as we don’t have joins with huge tables or complex queries, the data retrieval from the three tables consume the same resources (1%) and differ in the operator that is used to retrieve the data; Table Scan in the case of the temp table without index, Index Seek in the case of temp table with non-clustered index and Clustered Index Seek in the case of temp table with clustered index.
But you should take into consideration that modifying temporary tables many times in your code may lead to statistics getting out of date.