March 24, 2009 at 6:01 am
All,
TOP and SET ROWCOUNT will give the same result set. Are they Twins? 🙂
Well, my question is, What is the internal working system of them?
karthik
March 24, 2009 at 6:07 am
I did a small R&D between TOP and SET ROWCOUNT.
Here is my R&d work.
create table test
(
no int,
name varchar null
)
go
insert into test1 values(1,'a')
insert into test1 values(2,'d')
go
create table test1
(
no int,
name varchar null
)
go
insert into test1 values(1,'a')
insert into test1 values(2,'d')
insert into test1 values(3,'d')
SET ROWCOUNT METHOD:
set rowcount 1
select a.*
from test a,test1 b
where a.no = b.no
Query Plan:
|--Nested Loops(Inner Join, WHERE:(.[no]=[a].[no]))
|--Table Scan(OBJECT:([IPStatic].[dbo].[test] AS [a]))
|--Table Scan(OBJECT:([IPStatic].[dbo].[test1] AS ))
TOP METHOD:
select TOP 1 a.*
from test a,test1 b
where a.no = b.no
|--Top(1)
|--Hash Match(Inner Join, HASH:(.[no])=([a].[no]), RESIDUAL:(.[no]=[a].[no]))
|--Table Scan(OBJECT:([IPStatic].[dbo].[test1] AS ))
|--Table Scan(OBJECT:([IPStatic].[dbo].[test] AS [a]))
Output:
Both returns the same result.
So i think it seems like TWINS but both has different behaviour(I think TOP likes coffee, SET ROWCOUNT likes TEA). Right?
karthik
March 24, 2009 at 6:17 am
mmm I don't get a hash
I get loops in both. but there is an extra operator for the top...
but like you I'm unsure which is better
I'll do some more research as well
It seems that SET ROWCOUNT is on it's way out...
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 24, 2009 at 6:46 am
I guess the other thing to remember , I have picked up from some more research is that they do have slightly different characteristics...
TOP can be used in subqueries etc.
SET ROWCOUNT can use a vairable
there are other difference , but in terms of single select statement and the actual timing over heads I still can't find a difference
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 28, 2009 at 1:05 am
They are not the same.
SET ROWCOUNT is deprecated so it doesn't even matter if they were the same - you should stop designing any code with it, and revise any existing code that already uses it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply