Delete vs truncate table 3

[dobe1]

I have a table, which has over 5 million records in it. I have been attempting to delete all but about 1/2 million records. The deletion, indexed on the datetime field takes in excess of 20 hours. I must stop it at this point.

I have decided to truncate the table. My question is this. Upon truncating the table, will all indexes, stored proceedures, views, be left entact? I am assuming with a truncated table that all be be in place, and I will be left with a shell (table).

Also for future reference, how would I build a trigger to delete all records older than 30 days? I assume this will be built in the above table as a trigger.

New to SQL Server

Dobe

 

[AlexCuse]

I don't think you can use truncate if you have foreign keys.

You will only lose data if it is stored within the table, and I don't think stored procs and views will fit in there ;-)

Also, I don't think a trigger is what you are looking for for your delete. Triggers fire when a record in the table is modified.

All you need is something like this (provided date mod field is a date/time data type):

delete from TABLENAME
where DATE_MODIFIED_FIELD < dateadd(dd, -30, getdate())

Which you could set up as a job to run daily.

Hope this helps,

Alex




A wise man once said
"The only thing normal about database guys is their tables".

 

[AlexCuse]

ps - you will lose data from any views based on the table. best to do a backup first (or rename and create a new table for speed's sake).

A wise man once said
"The only thing normal about database guys is their tables".

 

[SQLDenis]

>>Upon truncating the table, will all indexes, stored proceedures, views, be left entact?


Yes only the data will be gone
Truncate just dealocates all the datapages from the table, this is a minimally logged operation and will perform many times faster than a delete

>>how would I build a trigger to delete all records older than 30 days?

don't, make a stored proc and schedule it, or put the following in a job

delete table
where SomeCol < dateadd(m,-1,getdate())

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[dobe1]

Thanks Alex.

I appreciate the help.

 

[dobe1]

SQLDenis,
Thanks also.
Dobe

 

[Alcar]

but if I remember correctly, if you truncate then it will not be written in your transaction log... so if something goes bad you will no easy way to get back to before the truncate, unless you have a nice full backup of it

just two cents.. worth less than two cents... you know.. inflation...



Daren J. Lahey
Programmer Analyst
FAQ183-874 contains Suggestions for Getting Quick and Appropriate Answers to your questions.

 

[SQLDenis]

>>but if I remember correctly, if you truncate then it will not be written in your transaction log... so if something goes bad you will no easy way to get back to before the truncate, unless you have a nice full backup of it

That's not true
although not every single delete statement is logged, you can still recover. truncate just dealocates the pages and nothing else. that's why you can rollback a transaction with truncate and the data will be available again

run this to verify

create table test (id int)
Go
insert test values(1)
insert test values(1)
insert test values(1)
insert test values(1)
insert test values(1)
insert test values(1)


select * from test


begin tran
	truncate table test
	select * from test
rollback tran

select * from test

drop table test

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[dobe1]

You are right, you can recover with a rollback.

 

[Alcar]

You learn something new every day! =)

Daren J. Lahey
Programmer Analyst
FAQ183-874 contains Suggestions for Getting Quick and Appropriate Answers to your questions.