Difference between drop table and truncate table?

sql sql-server sybase
Brian G picture Brian G · Sep 25, 2008 · Viewed 194.6k times · Source

I have some tables that I build as a part of my report rollup. I don't need them afterwards at all. Someone mentioned to truncate them as it would be faster.

Answer

JoshL picture JoshL · Sep 25, 2008

Deleting records from a table logs every deletion and executes delete triggers for the records deleted. Truncate is a more powerful command that empties a table without logging each row. SQL Server prevents you from truncating a table with foreign keys referencing it, because of the need to check the foreign keys on each row.

Truncate is normally ultra-fast, ideal for cleaning out data from a temporary table. It does preserve the structure of the table for future use.

If you actually want to remove the table definitions as well as the data, simply drop the tables.

See this MSDN article for more info

Related questions

Cannot insert explicit value for identity column in table 'table' when IDENTITY_INSERT is set to OFF

I have the below error when I execute the following script. What is the error about, and how it can be resolved? Insert table(OperationID,OpDescription,FilterID) values (20,'Hierachy Update',1) Error: Server: Msg 544, Level 16, State 1, Line 1 Cannot insert explicit value …

Read More
Execute stored proc with OPENQUERY

I have SQL Server 2008 with a linked Sybase server and I am trying to execute a stored procedure on the Sybase server using OPENQUERY. If I have a stored proc that doesn't take parameters it succeeds fine. If I have …

Read More
Inserting Multiple Rows in Sybase ASE

(Similar question related to SQL Server : SO Link) I know in Sql Server 2008 or above, you can insert multiple rows doing the following: INSERT INTO MyTable (Name, ID) VALUES ('First',1), ('Second',2), ('Third',3) However, it seems like this syntax DOES NOT …

Read More