Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345

Deleting batches of rows with TOP Expand / Collapse
Author
Message
Posted Monday, July 7, 2008 11:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 4:34 PM
Points: 266, Visits: 2,597
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.
Post #529515
Posted Monday, July 7, 2008 11:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 5, 2009 10:44 AM
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?
Post #529523
Posted Monday, July 7, 2008 12:06 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 4:34 PM
Points: 266, Visits: 2,597
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
Post #529530
Posted Monday, July 7, 2008 1:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 5, 2009 10:44 AM
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

Post #529594
Posted Monday, July 7, 2008 5:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 35,262, Visits: 31,745
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #529697
Posted Friday, October 30, 2009 6:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 1, 2012 8:44 AM
Points: 205, 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.
Post #811514
Posted Wednesday, February 26, 2014 1:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 26, 2014 2:02 AM
Points: 1, Visits: 237
Works great. I put the code into a stored procedure to execute nightly.

Many thanks!
Post #1545256
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse