DROP vs TRUNCATE which is faster?

I work with datafiles containing more than 1 million records. Which would be faster DROP TABLE or TRUNCATE TABLE?

You would DROP the table if you do not intend on using the table again.

You would TRUNCATE a table if you intend to use the table again.

I would imagine the speed difference is insignificant compared to doing what is functionally correct for your situation. The only case where the speed might make a difference is if you are performing the operation many hundred times over. So lets consider that scenario:

If you no longer need these several hundred tables, then you might as well DROP them, else they will remain there taking up resources.

If you are going to reuse the tables, then TRUNCATE them, else you are faced with having to recreate them. I would guess that TRUNCATE is faster versus the two oeprations of DELETE+CREATE.

Drop the table or Truncate?

I am setting up about 60 or so DTS packages to run every night. These
packages will be recreating all the tables from a foreign database.
At the moment, I have them dropping the tables, creating the tables,
copying all the data to the new table and creating the keys.
Would it be better to just drop the keys, truncate the tables, copy the
data and recreate the keys? Or does it really matter? I am, in
essence, doing the same thing.
Just trying to see if I am going about the task in the best way.
Thanks,
Tom.
They are effectively the same, but dropping the table in the right order is
effectively quicker.
Anyway, you could really gain some performance, if you could look into the
possibility of incremental updates, instead of getting rid of the whole lot
and repopulating.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:4092B4F1.6060700@.deltanet.com...
I am setting up about 60 or so DTS packages to run every night. These
packages will be recreating all the tables from a foreign database.
At the moment, I have them dropping the tables, creating the tables,
copying all the data to the new table and creating the keys.
Would it be better to just drop the keys, truncate the tables, copy the
data and recreate the keys? Or does it really matter? I am, in
essence, doing the same thing.
Just trying to see if I am going about the task in the best way.
Thanks,
Tom.
|||Narayana Vyas Kondreddi wrote:

> They are effectively the same, but dropping the table in the right order is
> effectively quicker.
I thought so, also. But someone had mentioned that tables are meant to
be permanent, so it would be better to truncate. The problem is just
because they are meant to be permanent, doesn't mean there is a problem
doing it.
Someone else said it would be better to do all the table drops and
creates - then populate them. I'm not sure why that would be better.
Why would dropping them in the correct order be quicker - I assume you
are talking about referential integrity. In my case, I am deleting
everything each time at night.

> Anyway, you could really gain some performance, if you could look into the
> possibility of incremental updates, instead of getting rid of the whole lot
> and repopulating.
I agree. But in our case the tables are already created on a foreign
system and we have no control over the schema, so it would be difficult
to do incremental updates.
Thanks,
Tom.

> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:4092B4F1.6060700@.deltanet.com...
> I am setting up about 60 or so DTS packages to run every night. These
> packages will be recreating all the tables from a foreign database.
> At the moment, I have them dropping the tables, creating the tables,
> copying all the data to the new table and creating the keys.
> Would it be better to just drop the keys, truncate the tables, copy the
> data and recreate the keys? Or does it really matter? I am, in
> essence, doing the same thing.
> Just trying to see if I am going about the task in the best way.
> Thanks,
> Tom.
>
>
|||Thomas
As Vyas said that dropping the table in the right order is
effectively quicker.
I would a little bit re-phrase his by saying that truncating the table in
the right order is effectively quicker.
As I understood you don't have to drop the table so you have to know
relationship between them and to define the order to truncate tables.( First
truncate a referenced(child) table and then a referncing(father) )
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:40936865.4010204@.deltanet.com...[vbcol=seagreen]
> Narayana Vyas Kondreddi wrote:
is[vbcol=seagreen]
>
> I thought so, also. But someone had mentioned that tables are meant to
> be permanent, so it would be better to truncate. The problem is just
> because they are meant to be permanent, doesn't mean there is a problem
> doing it.
> Someone else said it would be better to do all the table drops and
> creates - then populate them. I'm not sure why that would be better.
> Why would dropping them in the correct order be quicker - I assume you
> are talking about referential integrity. In my case, I am deleting
> everything each time at night.
the[vbcol=seagreen]
lot
>
> I agree. But in our case the tables are already created on a foreign
> system and we have no control over the schema, so it would be difficult
> to do incremental updates.
> Thanks,
> Tom.
>
|||Uri Dimant wrote:

> Thomas
> As Vyas said that dropping the table in the right order is
> effectively quicker.
> I would a little bit re-phrase his by saying that truncating the table in
> the right order is effectively quicker.
> As I understood you don't have to drop the table so you have to know
> relationship between them and to define the order to truncate tables.( First
> truncate a referenced(child) table and then a referncing(father) )
I am running this using DTS and to do a straight copy, the program does
a "Create" with optional "Drop". I was thinking of doing a
"Select/Into". So I would do a "Drop Table" and then do a "Select/Into"
from the foreign database.
Is there a reason why I should use the Create and Insert that DTS does
vs the Select/Into way. I was thinking of doing this so I wouldn't have
to make 60 packages (one for each file copied), since each create would
be different. I would just need to pass the file name in a Global
Variable and let DTS do the Select/Into. This would allow me to create
only one package.
Of course, the other problem would be creating the indexes after the
Select/Into. If I need to have a package for each table just to handle
the Indexes, I might as well let DTS do it's normal Create.
Thanks,
Tom.

>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:40936865.4010204@.deltanet.com...
> is
> the
> lot
>
>
|||One thing to consider;
Truncating the table does not get logged in the transaction log. So if
there is a lot of data in the tables, deleting the tables can cause the log
to grow.
Rand
This posting is provided "as is" with no warranties and confers no rights.
sql

Drop the table or Truncate?

I am setting up about 60 or so DTS packages to run every night. These
packages will be recreating all the tables from a foreign database.
At the moment, I have them dropping the tables, creating the tables,
copying all the data to the new table and creating the keys.
Would it be better to just drop the keys, truncate the tables, copy the
data and recreate the keys? Or does it really matter? I am, in
essence, doing the same thing.
Just trying to see if I am going about the task in the best way.
Thanks,
Tom.They are effectively the same, but dropping the table in the right order is
effectively quicker.
Anyway, you could really gain some performance, if you could look into the
possibility of incremental updates, instead of getting rid of the whole lot
and repopulating.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:4092B4F1.6060700@.deltanet.com...
I am setting up about 60 or so DTS packages to run every night. These
packages will be recreating all the tables from a foreign database.
At the moment, I have them dropping the tables, creating the tables,
copying all the data to the new table and creating the keys.
Would it be better to just drop the keys, truncate the tables, copy the
data and recreate the keys? Or does it really matter? I am, in
essence, doing the same thing.
Just trying to see if I am going about the task in the best way.
Thanks,
Tom.|||Narayana Vyas Kondreddi wrote:

> They are effectively the same, but dropping the table in the right order i
s
> effectively quicker.
I thought so, also. But someone had mentioned that tables are meant to
be permanent, so it would be better to truncate. The problem is just
because they are meant to be permanent, doesn't mean there is a problem
doing it.
Someone else said it would be better to do all the table drops and
creates - then populate them. I'm not sure why that would be better.
Why would dropping them in the correct order be quicker - I assume you
are talking about referential integrity. In my case, I am deleting
everything each time at night.

> Anyway, you could really gain some performance, if you could look into the
> possibility of incremental updates, instead of getting rid of the whole lo
t
> and repopulating.
I agree. But in our case the tables are already created on a foreign
system and we have no control over the schema, so it would be difficult
to do incremental updates.
Thanks,
Tom.

> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:4092B4F1.6060700@.deltanet.com...
> I am setting up about 60 or so DTS packages to run every night. These
> packages will be recreating all the tables from a foreign database.
> At the moment, I have them dropping the tables, creating the tables,
> copying all the data to the new table and creating the keys.
> Would it be better to just drop the keys, truncate the tables, copy the
> data and recreate the keys? Or does it really matter? I am, in
> essence, doing the same thing.
> Just trying to see if I am going about the task in the best way.
> Thanks,
> Tom.
>
>|||Thomas
As Vyas said that dropping the table in the right order is
effectively quicker.
I would a little bit re-phrase his by saying that truncating the table in
the right order is effectively quicker.
As I understood you don't have to drop the table so you have to know
relationship between them and to define the order to truncate tables.( First
truncate a referenced(child) table and then a referncing(father) )
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:40936865.4010204@.deltanet.com...
> Narayana Vyas Kondreddi wrote:
>
is[vbcol=seagreen]
>
> I thought so, also. But someone had mentioned that tables are meant to
> be permanent, so it would be better to truncate. The problem is just
> because they are meant to be permanent, doesn't mean there is a problem
> doing it.
> Someone else said it would be better to do all the table drops and
> creates - then populate them. I'm not sure why that would be better.
> Why would dropping them in the correct order be quicker - I assume you
> are talking about referential integrity. In my case, I am deleting
> everything each time at night.
>
the[vbcol=seagreen]
lot[vbcol=seagreen]
>
> I agree. But in our case the tables are already created on a foreign
> system and we have no control over the schema, so it would be difficult
> to do incremental updates.
> Thanks,
> Tom.
>
>|||Uri Dimant wrote:

> Thomas
> As Vyas said that dropping the table in the right order is
> effectively quicker.
> I would a little bit re-phrase his by saying that truncating the table in
> the right order is effectively quicker.
> As I understood you don't have to drop the table so you have to know
> relationship between them and to define the order to truncate tables.( Fir
st
> truncate a referenced(child) table and then a referncing(father) )
I am running this using DTS and to do a straight copy, the program does
a "Create" with optional "Drop". I was thinking of doing a
"Select/Into". So I would do a "Drop Table" and then do a "Select/Into"
from the foreign database.
Is there a reason why I should use the Create and Insert that DTS does
vs the Select/Into way. I was thinking of doing this so I wouldn't have
to make 60 packages (one for each file copied), since each create would
be different. I would just need to pass the file name in a Global
Variable and let DTS do the Select/Into. This would allow me to create
only one package.
Of course, the other problem would be creating the indexes after the
Select/Into. If I need to have a package for each table just to handle
the Indexes, I might as well let DTS do it's normal Create.
Thanks,
Tom.

>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:40936865.4010204@.deltanet.com...
>
> is
>
> the
>
> lot
>
>|||One thing to consider;
Truncating the table does not get logged in the transaction log. So if
there is a lot of data in the tables, deleting the tables can cause the log
to grow.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Drop the table or Truncate?

I am setting up about 60 or so DTS packages to run every night. These
packages will be recreating all the tables from a foreign database.
At the moment, I have them dropping the tables, creating the tables,
copying all the data to the new table and creating the keys.
Would it be better to just drop the keys, truncate the tables, copy the
data and recreate the keys? Or does it really matter? I am, in
essence, doing the same thing.
Just trying to see if I am going about the task in the best way.
Thanks,
Tom.They are effectively the same, but dropping the table in the right order is
effectively quicker.
Anyway, you could really gain some performance, if you could look into the
possibility of incremental updates, instead of getting rid of the whole lot
and repopulating.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:4092B4F1.6060700@.deltanet.com...
I am setting up about 60 or so DTS packages to run every night. These
packages will be recreating all the tables from a foreign database.
At the moment, I have them dropping the tables, creating the tables,
copying all the data to the new table and creating the keys.
Would it be better to just drop the keys, truncate the tables, copy the
data and recreate the keys? Or does it really matter? I am, in
essence, doing the same thing.
Just trying to see if I am going about the task in the best way.
Thanks,
Tom.|||Narayana Vyas Kondreddi wrote:
> They are effectively the same, but dropping the table in the right order is
> effectively quicker.
I thought so, also. But someone had mentioned that tables are meant to
be permanent, so it would be better to truncate. The problem is just
because they are meant to be permanent, doesn't mean there is a problem
doing it.
Someone else said it would be better to do all the table drops and
creates - then populate them. I'm not sure why that would be better.
Why would dropping them in the correct order be quicker - I assume you
are talking about referential integrity. In my case, I am deleting
everything each time at night.
> Anyway, you could really gain some performance, if you could look into the
> possibility of incremental updates, instead of getting rid of the whole lot
> and repopulating.
I agree. But in our case the tables are already created on a foreign
system and we have no control over the schema, so it would be difficult
to do incremental updates.
Thanks,
Tom.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:4092B4F1.6060700@.deltanet.com...
> I am setting up about 60 or so DTS packages to run every night. These
> packages will be recreating all the tables from a foreign database.
> At the moment, I have them dropping the tables, creating the tables,
> copying all the data to the new table and creating the keys.
> Would it be better to just drop the keys, truncate the tables, copy the
> data and recreate the keys? Or does it really matter? I am, in
> essence, doing the same thing.
> Just trying to see if I am going about the task in the best way.
> Thanks,
> Tom.
>
>|||Thomas
As Vyas said that dropping the table in the right order is
effectively quicker.
I would a little bit re-phrase his by saying that truncating the table in
the right order is effectively quicker.
As I understood you don't have to drop the table so you have to know
relationship between them and to define the order to truncate tables.( First
truncate a referenced(child) table and then a referncing(father) )
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:40936865.4010204@.deltanet.com...
> Narayana Vyas Kondreddi wrote:
> > They are effectively the same, but dropping the table in the right order
is
> > effectively quicker.
>
> I thought so, also. But someone had mentioned that tables are meant to
> be permanent, so it would be better to truncate. The problem is just
> because they are meant to be permanent, doesn't mean there is a problem
> doing it.
> Someone else said it would be better to do all the table drops and
> creates - then populate them. I'm not sure why that would be better.
> Why would dropping them in the correct order be quicker - I assume you
> are talking about referential integrity. In my case, I am deleting
> everything each time at night.
> >
> > Anyway, you could really gain some performance, if you could look into
the
> > possibility of incremental updates, instead of getting rid of the whole
lot
> > and repopulating.
>
> I agree. But in our case the tables are already created on a foreign
> system and we have no control over the schema, so it would be difficult
> to do incremental updates.
> Thanks,
> Tom.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > http://vyaskn.tripod.com/
> > Is .NET important for a database professional?
> > http://vyaskn.tripod.com/poll.htm
> >
> >
> >
> >
> > "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> > news:4092B4F1.6060700@.deltanet.com...
> > I am setting up about 60 or so DTS packages to run every night. These
> > packages will be recreating all the tables from a foreign database.
> >
> > At the moment, I have them dropping the tables, creating the tables,
> > copying all the data to the new table and creating the keys.
> >
> > Would it be better to just drop the keys, truncate the tables, copy the
> > data and recreate the keys? Or does it really matter? I am, in
> > essence, doing the same thing.
> >
> > Just trying to see if I am going about the task in the best way.
> >
> > Thanks,
> >
> > Tom.
> >
> >
> >
> >
>|||Uri Dimant wrote:
> Thomas
> As Vyas said that dropping the table in the right order is
> effectively quicker.
> I would a little bit re-phrase his by saying that truncating the table in
> the right order is effectively quicker.
> As I understood you don't have to drop the table so you have to know
> relationship between them and to define the order to truncate tables.( First
> truncate a referenced(child) table and then a referncing(father) )
I am running this using DTS and to do a straight copy, the program does
a "Create" with optional "Drop". I was thinking of doing a
"Select/Into". So I would do a "Drop Table" and then do a "Select/Into"
from the foreign database.
Is there a reason why I should use the Create and Insert that DTS does
vs the Select/Into way. I was thinking of doing this so I wouldn't have
to make 60 packages (one for each file copied), since each create would
be different. I would just need to pass the file name in a Global
Variable and let DTS do the Select/Into. This would allow me to create
only one package.
Of course, the other problem would be creating the indexes after the
Select/Into. If I need to have a package for each table just to handle
the Indexes, I might as well let DTS do it's normal Create.
Thanks,
Tom.
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:40936865.4010204@.deltanet.com...
>>Narayana Vyas Kondreddi wrote:
>>
>>They are effectively the same, but dropping the table in the right order
> is
>>effectively quicker.
>>
>>I thought so, also. But someone had mentioned that tables are meant to
>>be permanent, so it would be better to truncate. The problem is just
>>because they are meant to be permanent, doesn't mean there is a problem
>>doing it.
>>Someone else said it would be better to do all the table drops and
>>creates - then populate them. I'm not sure why that would be better.
>>Why would dropping them in the correct order be quicker - I assume you
>>are talking about referential integrity. In my case, I am deleting
>>everything each time at night.
>>
>>Anyway, you could really gain some performance, if you could look into
> the
>>possibility of incremental updates, instead of getting rid of the whole
> lot
>>and repopulating.
>>
>>I agree. But in our case the tables are already created on a foreign
>>system and we have no control over the schema, so it would be difficult
>>to do incremental updates.
>>Thanks,
>>Tom.
>>
>>--
>>HTH,
>>Vyas, MVP (SQL Server)
>>http://vyaskn.tripod.com/
>>Is .NET important for a database professional?
>>http://vyaskn.tripod.com/poll.htm
>>
>>
>>"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
>>news:4092B4F1.6060700@.deltanet.com...
>>I am setting up about 60 or so DTS packages to run every night. These
>>packages will be recreating all the tables from a foreign database.
>>At the moment, I have them dropping the tables, creating the tables,
>>copying all the data to the new table and creating the keys.
>>Would it be better to just drop the keys, truncate the tables, copy the
>>data and recreate the keys? Or does it really matter? I am, in
>>essence, doing the same thing.
>>Just trying to see if I am going about the task in the best way.
>>Thanks,
>>Tom.
>>
>>
>|||One thing to consider;
Truncating the table does not get logged in the transaction log. So if
there is a lot of data in the tables, deleting the tables can cause the log
to grow.
Rand
This posting is provided "as is" with no warranties and confers no rights.

DROP ot TRUNCATE a table

How do I DROP or TRUNCATE a SQL Server's table in a Stored Procedure by
passing a parameter value for the tabble name; something like the following:
CREATE PROCEDURE [dbo].[MyTestTable]
@.MyTable nvarchar(50)=''
AS
If @.MyTable<>''
BEGIN
IF OBJECT_ID(@.MyTable) IS NOT NULL
TRUNCATE TABLE @.MyTable --DROP TABLE @.MyTable
END
GO
The code above returns error message on line:
TRUNCATE TABLE @.MyTableSaima wrote:
> How do I DROP or TRUNCATE a SQL Server's table in a Stored Procedure
> by passing a parameter value for the tabble name; something like the
> following:
> CREATE PROCEDURE [dbo].[MyTestTable]
> @.MyTable nvarchar(50)=''
> AS
> If @.MyTable<>''
> BEGIN
> IF OBJECT_ID(@.MyTable) IS NOT NULL
> TRUNCATE TABLE @.MyTable --DROP TABLE @.MyTable
> END
> GO
> The code above returns error message on line:
> TRUNCATE TABLE @.MyTable
Declare @.sql nvarchar(100)
Set @.sql = N'Truncate Table [' + @.MyTable + N']'
EXEC (@.sql)
David Gugick - SQL Server MVP
Quest Software