truncate delete problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

truncate delete problem

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2009-04-30 : 07:17:56

Hi all,

I want to delete 50K rows, i thought of doing a truncate, but will not work, because i got a where clause

delete from T_Static_page where dontdelete=0 and showtouser=0

how can i acomplish this without a timeout?

thanks a lot

aprichard
Yak Posting Veteran

62 Posts

Posted - 2009-04-30 : 07:37:54

make sure that you made cluster index on dontdelete and showtouser
If not, add index on those fileds using in where clause and check the querey

mike13
Posting Yak Master

219 Posts

Posted - 2009-04-30 : 07:42:16

thanks, but could you explain, how i could do this?

tnx

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2009-04-30 : 08:30:13

DECLARE @INTCOUNT INT
DECLARE @MAXCOUNT INT
SET @INTCOUNT = 1000
SET @MAXCOUNT=2000--(SELECT COUNT(1) FROM MASTERAUDITTABLE)

WHILE(@MAXCOUNT>0)
BEGIN
delete top(@INTCOUNT) from masteraudittable
SET @MAXCOUNT = @MAXCOUNT - 100
if(@maxcount<100)
set @INTCOUNT = @MAXCOUNT
END

here @INTCOUNT = delete how many records by batch i.e 1000 @ time
@maxcount = total no of records to delete

hope this helps for u

Iam a slow walker but i never walk back

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources