deleting but like truncating?

I need to delete particular rows from a table. I would use TRUNCATE since
it's much faster. But I need to selectively do so.
Is there a way to do this?
Thanks
JihoJiho,
All rows - TRUNCATE TABLE
selective rows - DELETE...WHERE
HTH
Jerry
"Jiho Han" <jhan@.infinityinfo.com> wrote in message
news:a19ab9b12aa9f98c79964e66d0a83@.msnew
s.microsoft.com...
>I need to delete particular rows from a table. I would use TRUNCATE since
>it's much faster. But I need to selectively do so.
> Is there a way to do this?
> Thanks
> Jiho
>|||It's the selective deletion aspect that makes DELETE slower. In addition to
selectively sing the rows, SQL Server must reorganize data / index pages
for the remaining rows and also log the work as a transaction.
"Jiho Han" <jhan@.infinityinfo.com> wrote in message
news:a19ab9b12aa9f98c79964e66d0a83@.msnew
s.microsoft.com...
>I need to delete particular rows from a table. I would use TRUNCATE since
>it's much faster. But I need to selectively do so.
> Is there a way to do this?
> Thanks
> Jiho
>|||What I was asking and wasn't clear is that I want to delete from table certa
in
rows but not create logs as that seriously slows down the work.
I know TRUNCATE works but for the whole table.
I was wondering if there was a setting or a command that will temporarily
turn off logging or something like that.
Jiho
> Jiho,
> All rows - TRUNCATE TABLE
> selective rows - DELETE...WHERE
> HTH
> Jerry
> "Jiho Han" <jhan@.infinityinfo.com> wrote in message
> news:a19ab9b12aa9f98c79964e66d0a83@.msnew
s.microsoft.com...|||Jiho,
READ ONLY but then you wouldn't be able to perform the DELETE op.
HTH
Jerry
"Jiho Han" <jhan@.infinityinfo.com> wrote in message
news:a19ab9b12aaa658c79969be74d90c@.msnew
s.microsoft.com...
> What I was asking and wasn't clear is that I want to delete from table
> certain rows but not create logs as that seriously slows down the work.
> I know TRUNCATE works but for the whole table.
> I was wondering if there was a setting or a command that will temporarily
> turn off logging or something like that.
> Jiho
>
>|||> What I was asking and wasn't clear is that I want to delete from table
> certain rows but not create logs as that seriously slows down the work.
> I know TRUNCATE works but for the whole table.
> I was wondering if there was a setting or a command that will temporarily
> turn off logging or something like that.
You can't turn logging off. But you may be able to reduce the impact by
temporarily setting the recovery mode to simple or bulk-logged. Be careful
here.|||You can minimize the volume of transaction logging by setting the database
recovery model to "simple". If this is an OLTP database, then remember to
reset back to "full" then you are finished with this large delete operation.
If you are deleting a seriously large number of rows, like > 100,000, then
you may even want to consider dropping indexes before and re-creating them
afterward, since this will reduce transaction logging and maintenance of
indexes during the operation. This is especially true if there is a
clustered index.
Also, when performing a large number of deletes or updates, performance can
be increased by doing so in batches instead of one large operation. By using
rowcount and a while loop, you can limit each operation to a specifed number
of rows.
set rowcount 1000
while
delete from mytable where status = 0
if @.@.rowcount = 0 break
checkpoint
end
"Jiho Han" <jhan@.infinityinfo.com> wrote in message
news:a19ab9b12aaa658c79969be74d90c@.msnew
s.microsoft.com...
> What I was asking and wasn't clear is that I want to delete from table
> certain rows but not create logs as that seriously slows down the work.
> I know TRUNCATE works but for the whole table.
> I was wondering if there was a setting or a command that will temporarily
> turn off logging or something like that.
> Jiho
>
>|||I am reluctant to post this, but...
One of my clients uses the technique of selecting the rows to be kept
into a new table, dropping the old table, renaming the newly created
table, then rebuilding the constraints (if there are any).
There are more things wrong with this approach than I care to
enumerate. I most emphatically recommend you use DELETE... WHERE and
let SQL Server do what it is designed to do - log everything and
rollback if something goes wrong, thereby protecting the integrity of
your data - rather than taking the SELECT.. INTO approach. But
SELECT.. INTO is fast.
Be careful
Payson
Jiho Han wrote:
> I need to delete particular rows from a table. I would use TRUNCATE since
> it's much faster. But I need to selectively do so.
> Is there a way to do this?
> Thanks
> Jiho|||I like the batch recommendation. If you do decided to switch to SIMPLE be
sure to perform a database backup after switching back to FULL (and please
remember to switch back) or atlease a differential to ensure t-log backups
continue to run correctly.
HTH
Jerry
"JT" <someone@.microsoft.com> wrote in message
news:%23BGDKc3yFHA.2008@.TK2MSFTNGP10.phx.gbl...
> You can minimize the volume of transaction logging by setting the database
> recovery model to "simple". If this is an OLTP database, then remember to
> reset back to "full" then you are finished with this large delete
> operation.
> If you are deleting a seriously large number of rows, like > 100,000, then
> you may even want to consider dropping indexes before and re-creating them
> afterward, since this will reduce transaction logging and maintenance of
> indexes during the operation. This is especially true if there is a
> clustered index.
> Also, when performing a large number of deletes or updates, performance
> can be increased by doing so in batches instead of one large operation. By
> using rowcount and a while loop, you can limit each operation to a
> specifed number of rows.
> set rowcount 1000
> while
> delete from mytable where status = 0
> if @.@.rowcount = 0 break
> checkpoint
> end
>
> "Jiho Han" <jhan@.infinityinfo.com> wrote in message
> news:a19ab9b12aaa658c79969be74d90c@.msnew
s.microsoft.com...
>|||> There are more things wrong with this approach than I care to
> enumerate.
For something that is done regularly, I agree.
However, it is a viable approach if you are only interested in keeping, say,
the most recent day's worth of data, and you currently have 6 years' worth.
A

Delete/truncate table ignoring contraints

Is there any easy way to truncate a table which has a foreign key restraint? I want to override the default behavior which is to not allow truncate of parent tables. I want to be able to temperarily remove the contraint so I can truncate the temple, how do you do this?

I should add that the systables keep track of the contraints. There should be a query that I could run that would just disable the checking of the contraint? Any help?|||No. There is no way to control/alter the behavior of truncate table. If you need to use it you have to drop the FK constraints even if the table is empty.|||

O.K. So... that isn't easily done.

Is there any easy way to simply copy the schema, contraints and everything but simply no data? Perhaps using DTS?

Or perhaps, is there another way to reset the identity information of a table? The delete operator does not reset identity.

|||If you want to reset identity value you can use DBCC CHECKIDENT. See Books Online for more details. Your original question was different.||| your original question was different

:) Thanks.

DELETE/TRUNCATE a table of 10,000 records is taking more than a minute

Hi everyone,
I have a table of 10,000 ++ records with no links keys or foreign
constraints; but a truncate table or a delete from takes more than a
minute to execute or the query analyser just hangs but the timer @. the
bottom corner is running.
What would cause it to take so long to truncate a table?
Please advise.
Thanks
Can you check (sp_who2) to see if the process is being bklocked. If so you
can use dbcc inoutbuffer (spid) to see what the blocking process is doing.
HTH,
Paul Ibison

DELETE/TRUNCATE a table of 10,000 records is taking more than a minute

Hi everyone,
I have a table of 10,000 ++ records with no links keys or foreign
constraints; but a truncate table or a delete from takes more than a
minute to execute or the query analyser just hangs but the timer @. the
bottom corner is running.
What would cause it to take so long to truncate a table?
Please advise.
ThanksCan you check (sp_who2) to see if the process is being bklocked. If so you
can use dbcc inoutbuffer (spid) to see what the blocking process is doing.
HTH,
Paul Ibison

DELETE without logging

Hi all,
Is there a way to execute a DELETE sentence with a WHERE condition (cannot use TRUNCATE TABLE) without logging in order to execute a fast deletion of records?
Thanks in advance.
God Bless.Q1 Is there a way to execute a DELETE statement with a WHERE clause (cannot use TRUNCATE TABLE) without logging?


A1 No. (Not in any kind of production setting. Also, note that truncations are not logged on a row by row basis.)

Q2 Is there a way to execute a DELETE statement with a WHERE clause in order to execute a "faster" deletion of records?


A2 Sometimes, yes. Generally optimizing large scale deletions involves on the data, indexing, the schema, and the environment. Sometimes it is a matter or having a suitable index the system can take advantage of; sometimes it may be a matter of reducing concurrency locking issues; sometimes it may be a matter of dropping excessive indexes (and later recreating needed ones); often several such factors may be involved.

Delete vs. Truncate Table

Using SQL server 2005 express, I am wish to delete data from my table.
I cannot use TRUNCATE TABLE as some tables are referenced by a foreign
key constraint.
So I use
DELETE FROM myTable
This is fine, except that I would like my identification column, to
start again at 1, rather than the last ID +1.
Is there a way of achieving this (ressetting the ID column to its
default value)?
Thank you in advance.> This is fine, except that I would like my identification column, to start
> again at 1, rather than the last ID +1.
> Is there a way of achieving this (ressetting the ID column to its default
> value)?
DBCC CHECKIDENT('table_name', RESEED, 0);|||Check out DBCC CHECKIDENT in BOL|||Thank you both for your help
Emmanuel Petit a crit :
> Using SQL server 2005 express, I am wish to delete data from my table.
> I cannot use TRUNCATE TABLE as some tables are referenced by a foreign
> key constraint.
> So I use
> DELETE FROM myTable
> This is fine, except that I would like my identification column, to
> start again at 1, rather than the last ID +1.
> Is there a way of achieving this (ressetting the ID column to its
> default value)?
> Thank you in advance.

Delete vs. Truncate

If I am deleting all the rows in a table why would I use Truncate Table over Delete, or vice vera? What is the difference?
KenThe Truncate is much faster.
The reason truncate does not record in the transaction log therefore you can not role it back

Eyal|||Okay,

That makes sense. I ran across it in a DTS package that someone else created, that basically adds new data to a table.

It creates temp tables, and one of them they were doing a delete on, which didn't quite make sense to me.

Thanks for the reply!

Ken|||You cannot Truncate from a table used in replication (because it is not logged).|||Originally posted by eschapir
The Truncate is much faster.
The reason truncate does not record in the transaction log therefore you can not role it back

I am not sure if you guys have experienced this. Truncate command may not work at times. And this is not just happening in my current job but previous job too. When truncate command was used, old records were not removed and when new set of data was loaded, it caused duplicates in the table.

So for peace of mind, I usually placed both truncate and delete commands (though delete will do the job) in some of my missing critical processes.

Sound really silly but it actually happened to me.

I am on SQL7 SP4.

Eyal|||TRUNCATE TABLE is aimed at administrative tasks.(hard come, light gone)

1. You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.

2. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

3. TRUNCATE TABLE may not be used on tables participating in an indexed view.

4. Cannot be used on linked server.

5. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable.

For more see BOL Topic "TRUNCATE TABLE" and other sources :)

Good luck !|||Thanks guys!

Now I know when to use each one. For this particular task I can use truncate. Actually I could just drop the table and recreate it. Delete take a lot longer to run for this particular process.

Doesn't really cause any issues either way, but when you don't have much time for jobs at night, speed and time saved is always a consideration!

Thanks again! Ken