InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 12:05:16 GMT20http://www.sqlservercentral.com/Forums/Topic555650-8-1.aspx[quote][b]karthikeyan-444867 (8/20/2008)[/b][hr]Mark,you are right. But OP mentioned like[quote]Query to delete, all the records from a table [/quote]Thats why i preferred TRUNCATE.[/quote]But that's only a part of what the OP wants. You cut off the other very important part that makes TRUNCATE the wrong choice in this case...[quote]Query to delete, all the records from a table [font="Arial Black"]before half an hour[/font],…[/quote]Sat, 09 Jun 2012 09:07:38 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic555650-8-1.aspx[quote][b]adhiman (6/8/2012)[/b][hr]If you want to do a quick one then Create another table with same structure and insert the rows that you don't want to delete into the other table. Now truncate the original table .Now the second table has all the rows you want. Now switch the names. This way you won't block others because you are not performing any big transaction. I have an article that is worth trying if you want to perform deletes efficiently. Let me know if you have questionshttp://tsqltips.blogspot.com/2012/06/deleting-records-from-sql-table.html[/quote]This isn't necessarily correct. DELETE does not necessarily (and frequently doesn't) block anything.Sat, 09 Jun 2012 09:05:49 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic555650-8-1.aspxIf you want to do a quick one then Create another table with same structure and insert the rows that you don't want to delete into the other table. Now truncate the original table .Now the second table has all the rows you want. Now switch the names. This way you won't block others because you are not performing any big transaction. I have an article that is worth trying if you want to perform deletes efficiently. Let me know if you have questionshttp://tsqltips.blogspot.com/2012/06/deleting-records-from-sql-table.htmlFri, 08 Jun 2012 14:33:37 GMTadhimanhttp://www.sqlservercentral.com/Forums/Topic555650-8-1.aspxHi kumar99ms ,Can you please elaborate your question clearly?Thanks,Amit KhannaWed, 20 Aug 2008 05:35:13 GMTAmit Khanna-455738http://www.sqlservercentral.com/Forums/Topic555650-8-1.aspx[quote][b]karthikeyan (8/20/2008)[/b][hr]Andras,[quote]t would probably be a poor choice if you do not want to get rid of every row in your table.[/quote]You mean we need to use TRUNCATE if we dont want the log for those deleted records. Am i correct ?...[/quote]Hi Karthik,Basically yes, when you can use TRUNCATE, it is a good way to empty the whole table, and the individual row deletes will not be in the transaction log, so it will be fast. But TRUNCATE deletes all the rows, and this is the reason it may be less applicable to the OP (assuming I understood the question right).Regards, AndrasWed, 20 Aug 2008 05:17:02 GMTAndras Belokosztolszkihttp://www.sqlservercentral.com/Forums/Topic555650-8-1.aspxOn first reading I thought your delete was taking over half an hour - so TRUNCATE may be an answer instead of DELETE * for a particularly large table.However I'm guessing you really mean you want to delete records older than half an hour, in which case you'll need something like this[code]DELETE FROM tableWHERE dateColumn < DATEADD(mi, -30, GetDate())[/code]you will need a suitable dateColumn containing the records creation timeWed, 20 Aug 2008 05:10:15 GMTTom Brownhttp://www.sqlservercentral.com/Forums/Topic555650-8-1.aspxAndras,[quote]t would probably be a poor choice if you do not want to get rid of every row in your table.[/quote]You mean we need to use TRUNCATE if we dont want the log for those deleted records. Am i correct ?[quote]Since you specify a where clause, TRUNCATE is likely not what you are looking for.[/quote]Yes, He has to use DELETE.Wed, 20 Aug 2008 05:08:29 GMTkarthik Mhttp://www.sqlservercentral.com/Forums/Topic555650-8-1.aspxMark,you are right. But OP mentioned like[quote]Query to delete, all the records from a table [/quote]Thats why i preferred TRUNCATE.Wed, 20 Aug 2008 05:00:22 GMTkarthik Mhttp://www.sqlservercentral.com/Forums/Topic555650-8-1.aspx[quote][b]karthikeyan (8/20/2008)[/b][hr]TRUNCATE may help for your need.You have to frame your question better. Also please post your requirement clearly.[/quote]While I agree with Karthik that TRUNCATE [b]may [/b]help you, it would probably be a poor choice if you do not want to get rid of every row in your table. Since you specify a where clause, TRUNCATE is likely not what you are looking for.Regards, AndrasWed, 20 Aug 2008 05:00:14 GMTAndras Belokosztolszkihttp://www.sqlservercentral.com/Forums/Topic555650-8-1.aspxtruncate won't work if you are only trying to delete some of the records... it will empty the table completely.Wed, 20 Aug 2008 04:58:40 GMTSuperDBA-207096http://www.sqlservercentral.com/Forums/Topic555650-8-1.aspx[quote][b]kumar99ms (8/20/2008)[/b][hr]Query to delete, all the records from a table before half an hour,… Delete * from table where condition=100 and …..what we have write here…for time…Regards,[/quote]What is the schema of your table? Could you list us the columns that contain the time information? If you have nothing in the table to keep track of the time information, then you will not be able to do this, and will need to rely on your backups (there is point in time recovery in the enterprise edition of SQL Server, so you could restore the information as of half an hour ago into a separate database)Another thing to do: first use a select statement to see if the rows deleted are the rows you indeed want to delete.Regards, AndrasWed, 20 Aug 2008 04:58:17 GMTAndras Belokosztolszkihttp://www.sqlservercentral.com/Forums/Topic555650-8-1.aspxTRUNCATE may help for your need.[quote]before half an hour,…[/quote]You have to frame your question better. Also please post your requirement clearly.Wed, 20 Aug 2008 04:56:20 GMTkarthik Mhttp://www.sqlservercentral.com/Forums/Topic555650-8-1.aspxQuery to delete, all the records from a table before half an hour,… Delete * from table where condition=100 and …..what we have write here…for time…Regards,Wed, 20 Aug 2008 04:42:27 GMTkumar99ms