September 25, 2006 at 4:24 pm
Comments posted here are about the content posted at temp
October 5, 2006 at 1:12 am
a word of warning concerning using a top without an order by? You might not actually get the result you expect.
I find there's often confusion between top and max ( or min ) , developers tend to use top where what they actually want is max ! ( my argument being that the value returned by a top can vary according to order by and underlying indexes whereas a max(col) will always return the required value , unlike top 1 col , and if max or min is used it's clear what is required )
As an aside the ability to update a top n needs to be used with great care I suspect.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 5, 2006 at 2:33 am
I'm not using SQL Server 2005, yet. But I've found this very usefull!
I'm also curious when you say: "Doing the update in batches doesn't guarantee no blocking of course, but it should reduce the time the query takes to complete."
How do you do these batches?
You can get the first range of rows with TOP, but the how do you select the others? Maybe you have an identity column and you are using BETWEEN? I've used this option, but I'm not sure it's the more eficient one.
Thanks!
October 5, 2006 at 3:42 am
The TOP on deletes and updates is so useful.
It's easy to forget that SET ROWCOUNT will affect triggers. ie You can end up with orphaned rows.
October 5, 2006 at 5:48 am
Josep, Im sorry I didnt elaborate a bit more in the article but you're right on track. Deletes are easy since once deleted, you can't delete them again! Updates do need a where statement that filters out the rows you've processed already. Depending on what you're doing it might well be easier to use an identity or something similar combined with a between. In some cases you may can just use the changed data as the filter, as example would be update table set column = new value where column = old value. With the top N or rowcount you'll change the first 100 rows that match, when you run a second time those rows would be excluded because the column value no longer matches the where statement.
October 5, 2006 at 6:44 am
I've found RANK() to be very useful in many scenerios especially with the PARTITION BY clause. One scenerio was where I built an SSIS package that outputs data to different worksheets in an Excel file based on some criteria. Also, because of Excel's limitation of 64K rows per worksheet I had to have a way to tell SSIS to go to a different worksheet as needed. Very handy.
October 5, 2006 at 7:23 am
Also a question
In sql 2000 in enterprise manager was right click on select table ‘return top _____’
I can’t find this in SQL 2005 beside in options menu but is not working
Ant suggestions
October 5, 2006 at 11:48 am
That option is not in SQL 2005 Management Studio. You can right click and seelct open or you can right click and select to script as a select, update, insert, or delete ..... which, by the way, I think is vastly improved because it now lists the column names in the select rather than select *.
October 5, 2006 at 12:20 pm
>>The most common reason I've had to do restrict the number of rows has been when doing operations against millions of records.
Here's another really common one: "paging" data for web output. ORDER BY and WHERE still required, of course <g>.
Thanks for the article! I had no idea this improvement had been made.
FWIW I never did it with SET ROWCOUNT previously, I did it by putting together the "SELECT TOP " statement as a string (including the casted <N> value provided by the var) and then executing the string statement...
>L<
October 10, 2006 at 12:23 am
I would say use new features of SQL 2005 i.e. ROW_NUMBER(), RANK() with OVER(). With The approach mentioned in the article, you are not sure if you've updated the right results or not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply