Prdp Prdp - 2 months ago 12
SQL Question

Truncate temp table Vs Drop temp table Sql Server

There are two ways to check the existence of

temp table
and recreate it

1.

IF Object_id('TEMPDB..#temp') IS NOT NULL
TRUNCATE TABLE #temp
ELSE
CREATE TABLE #temp
(
id INT
)


2.

IF Object_id('TEMPDB..#temp') IS NOT NULL
DROP TABLE #temp

CREATE TABLE #temp
(
id INT
)


is there any advantage of using one over the another

Answer

If there is table called temp TRUNCATE it otherwise create new table.

IF Object_id('temp') IS NOT NULL
  TRUNCATE TABLE temp
ELSE
   CREATE TABLE temp
    (
       id INT
    );

There is possibility that original table has different schema than in ELSE statement you will end with bad structure.

CREATE TABLE temp(col VARCHAR(100));
INSERT INTO temp VALUES ('a');

IF Object_id('temp') IS NOT NULL
  TRUNCATE TABLE temp
ELSE
    CREATE TABLE temp
    (
       id INT
    );

INSERT INTO temp VALUES (1);  

SqlFiddleDemo

Output:

╔═════╗
║ col ║
╠═════╣
║   1 ║
╚═════╝

If there is table called temp drop it. Then recreate it.

IF Object_id('TEMPDB..#temp') IS NOT NULL
  DROP TABLE #temp

CREATE TABLE #temp
(
       id INT
); 

In this example you are always sure that you get structure defined in CREATE statement.

CREATE TABLE temp(col VARCHAR(100));
INSERT INTO temp VALUES ('a');

IF Object_id('temp') IS NOT NULL
  DROP TABLE temp

CREATE TABLE temp
(
       id INT
)    

INSERT INTO temp
VALUES (1);  

SqlFiddleDemo2

Output:

╔════╗
║ id ║
╠════╣
║  1 ║
╚════╝

If table does not exist both method return the same structure:

SqlFiddleDemo_3 SqlFiddleDemo_4