October 7, 2011 at 6:47 am
Please tell me the alternative of TOP clause
actually I don't want to perform TOP on Following Query.
Declare @testing Table(
id int Primary Key identity
,Agg int
)
Insert into @testing
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10 union all
select 11
set statistics io on
Select SUM(Agg) as [Agg]
From
(
select top 10 Agg
from @testing
) dt
set statistics io off
October 7, 2011 at 6:53 am
as per the information given by you,
you can use where clause instead of top
select id from @table where id<=10
if your requirement is something else and you want to implement it via set based query then you should go though http://www.sqlservercentral.com/articles/T-SQL/62867/
article by jeff.
October 7, 2011 at 7:12 am
Thanks and Nice Quote ghanshyam 🙂 but
1.) Don't want to use id column
2.) Those technique I already used but that is not having less logical count.
October 7, 2011 at 7:22 am
SELECT SUM(Agg) as [Agg]
From
(
select
id,
rn = ROW_NUMBER() OVER(ORDER BY id)
from @testing
) dt WHERE rn < 11
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 7, 2011 at 7:37 am
So I have to ask...why not just use top? That is the purpose of it. Or did your professor tell you come up with a different way? There are a ton of ways to do this but they are all way more complicated than just using top.
As a side note, the query you posted originally will return inconsistent results. You select top 10 but there is no order by clause. This means that sql is left to decide what the top 10 records are. :hehe:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 7, 2011 at 7:42 am
nice crish...
October 7, 2011 at 7:43 am
Sean is correct. Without an order by, the top 10 is semi-useless. You might get different sums each time.
October 7, 2011 at 7:51 am
Thanks to all.. 🙂
Actually when we perform "TOP" for table having very large
row set (50000 or More) ;than "TOP" gives performance issue. It happens because according to sql architecture "TOP operation" performs at last so If you want to perform even "Top 1" for any table having more records than sql go through all record than perform TOP operation. that's way I am searching for alternate option for TOP.
October 7, 2011 at 7:53 am
shivendrakumaryadav60 (10/7/2011)
Thanks to all.. 🙂Actually when we perform "TOP" for table having very large
row set (50000 or More) ;than "TOP" gives performance issue. It happens because according to sql architecture "TOP operation" performs at last so If you want to perform even "Top 1" for any table having more records than sql go through all record than perform TOP operation. that's way I am searching for alternate option for TOP.
That's a whole new question.
Please post the actual execution plan so we may propose alternatives.
BTW rownumber() won't beat top here.
Any reasons why you need to do a top here? Like deleting in batch or something like that?
October 7, 2011 at 7:54 am
You're looking at this the wrong way. You can tune a SQL statement to use less records, usually with indexing or rewriting.
This use of TOP doesn't make sense.
October 7, 2011 at 7:59 am
shivendrakumaryadav60 (10/7/2011)
Thanks to all.. 🙂Actually when we perform "TOP" for table having very large
row set (50000 or More) ;than "TOP" gives performance issue. It happens because according to sql architecture "TOP operation" performs at last so If you want to perform even "Top 1" for any table having more records than sql go through all record than perform TOP operation. that's way I am searching for alternate option for TOP.
Maybe. If you want, say, TOP 10 sellers for a report, then there isn't a quicker way to do it - I've just tried it on a 170 million row table and the result returned in a fraction of a second. If you want to return TOP n of a large number of groups within a large table and you've coded it with correlated subqueries, then of course performance will suck - but that's what the window functions are for.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 7, 2011 at 8:21 am
Maybe you should take a look at FASTFIRSTROW table or query hint, it might help you.
However, as other have noted, when your query involves huge tables and joins to other huge tables you need to look at possible poorly designed tables and/or queries.
The probability of survival is inversely proportional to the angle of arrival.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply