Contents
Why do we truncate a table?
Typically, TRUNCATE TABLE quickly deletes all records in a table by deallocating the data pages used by the table. This reduces the resource overhead of logging the deletions, as well as the number of locks acquired. Records removed this way cannot be restored in a rollback operation.
What happens when you truncate a table?
TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. To remove the table definition in addition to its data, use the DROP TABLE statement. A TRUNCATE TABLE operation can be rolled back.
Can You Drop a temp table in a stored procedure?
In one stored procedure, I create a temp table and drop it and in the second one, I only created a temp table but do not drop it. I run both the stored procedure for 5000 times and compare their performance. Honestly, I have run many different stored procedures and many different times.
Why does truncating a temp table at the end?
If the temporary table is large enough ( more than 128 extents ), the physical page deallocations are deferred, and performed by a background system task. This is true whether an explicit TRUNCATE TABLE is used or not. The only difference is a tiny implementation detail.
When do you Drop a local temporary table?
A local temporary table that is created in a stored procedure is dropped when the procedure ends; other stored procedures, or the calling process, can’t see them. Other local temporary tables are dropped when the session ends. Global temporary tables (start with ##) are shared between sessions.
How is the name of a temporary table stored?
The full name of a temporary table as stored in the sysobjects table in tempdb is made up of the table name specified in the CREATE TABLE statement and the system-generated numeric suffix. To allow for the suffix, table_name specified for a local temporary name cannot exceed 116 characters.