SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deleting batches of rows with TOP


Deleting batches of rows with TOP

Author
Message
JJ B
JJ B
Mr or Mrs. 500
Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)

Group: General Forum Members
Points: 529 Visits: 2859
Cooper: My database is set to compatibility of 90. To check this setting, right click on the database in Management Studio and choose Properties. Select the "Options" row from the list on the left. Then look at compatibility level on the right.

The 90 setting may or may not make a difference. I got the following syntax to work with the 90 setting:
. SELECT TOP (@N) * FROM table_name

I got an error when the parentheses were eliminated.
B.A. Cooper
B.A. Cooper
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 51
JJB - Thanks for your input.

The database instance I was trying this with is SQL Server 2000 and the highest compatibility level available is 80. So I have to assume that this syntax will only work with SQL Server 2005 and above?
JJ B
JJ B
Mr or Mrs. 500
Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)

Group: General Forum Members
Points: 529 Visits: 2859
Cooper: You may be right. It may be a new feature of SS05. You could probably verify that by looking for SS05 new features list somewhere.

The biggest hit we got in upgrading to SS05 was the time consuming, hair pulling, ugly SSIS package creation and on-going maintenance. But the new SS05 t-SQL syntax options were a great benefit and productivity enhancer, at least for me. Despite the SSIS sore spot, I'm glad we upgraded, because access to syntax such as the one you are asking about is worth it. Maybe you could get your company to upgrade. Though lots of people are saying that if you haven't upgraded so far, you might as well wait for SS08. That's something you could start lobbying for now.

Good luck.
- JJ
B.A. Cooper
B.A. Cooper
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 51
JJB - This DB resides at a client site so who knows when it will get upgraded. Thanks again for your input.

For others, I confirmed what JJB mentioned in an earlier post. This syntax is only available with a compatability level of 90 which is only available in SQL server 2005.

See the following for additional information: http://msdn.microsoft.com/en-us/library/ms178653.aspx
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86982 Visits: 41107
B.A. Cooper (7/7/2008)
The following code works, but either of the commented lines produce a syntax error in my environment. When the code from the article is cut and pasted it produces the same error. Is there a configuration setting that allows this to work?

DECLARE @N INT
SET @N = 5
select TOP 5 * from table_name
--select TOP @N * FROM table_name
--select TOP (@N) * FROM table_name


For SQL Server 2000, a programmable TOP is NOT available unless you do the dynamic SQL thing... you can, however, use rowcount...

DECLARE @N INT
SET @N = 5
SET ROWCOUNT @N
SELECT * FROM table_name
SET ROWCOUNT 0



--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
jteeter
jteeter
SSC Veteran
SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)

Group: General Forum Members
Points: 223 Visits: 105
This definitely has a practical application.

Quite possible this is no longer relevant with Sql 2k5, but I used to do this extensively in a Sql2k environment with immediate transactional replication. I had two problems that large deletes would cause - the blocking one mentioned earlier, and that fact that a large delete could fill up my transaction replication queue and cause more important updates to wait.

I'm not working on that system anymore, so I don't know if Sql 2k5's transactional replication is any different. Another way to solve the problem would have been to simply not replicate deletes (do the deletes on primary and subscribers), but that would have required us to write a bunch of special handling in the software we wrote that setup the replication.

A final note - I approached this with set rowcount to limit the number of deletes, and actually polled the database every minute to delete a small batch of rows until it was cleaned up. As horrible as that sounds, it kept my transaction logs from being overwhelmed with deletes and falling behind replicating more important things.
vanprimas
vanprimas
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 277
Works great. I put the code into a stored procedure to execute nightly.

Many thanks!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search