April 21, 2007 at 1:25 am
I need a function or procedure to select for me certain number of records and after that to delete them. I heard that SQl server 2005 has such functionality. Hoping that I will be able to write something custom for SQL server 2000 that provides the same finctionality.
Any idea is greatly apprecited. mj
April 21, 2007 at 5:59 am
In SQL Server 2005, you have the OUTPUT statement.
In 2000, you will have to put them temporarily in a table, SELECT them and then JOIN them back to source table and DELETE.
N 56°04'39.16"
E 12°55'05.25"
April 21, 2007 at 8:31 am
Your description is a bit vague.
When I delete large numbers of records from a table then rather than fill up the log file with one massive DELETE I do something like the following
SET ROWCOUNT 50000
WHILE 1=1
BEGIN
DELETE FROM dbo.myTable
WHERE MyField = My Condition
IF @@ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0
This works in all versions from SQL6.5 onwards
April 21, 2007 at 4:40 pm
But when I select them, I have to lock them as well, or the other processes running on the same table may modify them. And then I have deadlocks all over.
Thanks a lot. mj
April 26, 2007 at 1:36 pm
How about:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT MyCol1, MyCol2, ...
FROM MyOwner.MyTable WITH (ROWLOCK)
INNER JOIN MyOwner1.MyTable1 WITH (ROWLOCK)
ON ...
-- At this point all is locked.
IF @@ERROR 0 AND @@TRANCOUNT > 0
ROLLBACK TRAN
DELETE ...
IF @@ERROR 0 AND @@TRANCOUNT > 0
ROLLBACK TRAN
IF @@TRANCOUNT > 0
COMMIT TRAN
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
You can also loosen the SERIALIZABLE and optimize more...
April 30, 2007 at 9:12 pm
Thanks a lot for your example.
Could you, please elaborate more on the optimization part - "You can also loosen the SERIALIZABLE and optimize more".
Thanks again, mj
April 30, 2007 at 9:17 pm
Optimization is not easy and really depends on your application an needs. You can change the isolation level (see books online and refer to the SET TRANSACTION ISOLATION LEVEL command and various transaction modes). Also, you can play with the locking hints to lock rows, pages, or even the entire table.
It really depends on how many rows are being deleted each time, how the deletion is perform (i.e., what is the exact DELETE statement), and whether multiple batches of the same SQL code can be run at the same time (which you already indicated that there are multiple processes executing the same code causing dead-locks).
If you can provide answers to those questions (i.e., estimated # of rows deleted, lock requirements, etc.) then I can further assist in this task.
Hope this helps for now.
May 1, 2007 at 10:23 am
This thread may be of interest:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=349243&p=1
November 28, 2007 at 6:08 pm
Thanks a lot for your post. It took me a while as the project was on hold for several months.
What I really need is equivalent of the OUTPUT clause in SQL server 2005 that I could use in SQl server 2000. As this functionality is not available in 2000, I'm trying to develop a custom solution/procedure for that.
There are multiple instances of my application running in the same time. Each of them select n rows (user defined parameter) from a table and hen there's some processing on this data - so, I have to lock the so no other process will be able to process them. After the processing is done, these rows are not needed anymore and need to be deleted. The output clause does that in a single simple statement.
I need some help to construct a procedure that will take the number of rows as input parameter, then return the data selected to the client and finally delete the rows in a single transaction. My initial thought was to create a temp table, insert the row selected and joining to the real table to delete them.
Thanks a lot for the help,
mj
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply