Alternative to use function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Alternative to use function

Author

Topic

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-03-23 : 02:59:37

Hi,

i have used a query as follows

select col1,(select top 1 col2 from table2 where table2.newid=table1.id)
from table1

i have used something like this as alternative

select col1,col2
from
table1
leftouterjoin
(
select col2,newid,[partno]=Row_Number() over (partition by newid order by newid)
) t
on table1.id=t.newid and t.partno=1

is this a good approach then prev one. i hope so. please let me know. so that i can implement in other queries

Iam a slow walker but i never walk back

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-23 : 03:04:51

try this..Its all depends on the index on the column.in this case
ordercol is to be indexed,you can have better performance.


select t1.col1,t2.col2,t2.col1 from tab1 as t1
outer apply(select top 1 col1 from tab2 where tab2.id=tbl.id order by ordercol)t2

edit:Code tag added

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-03-23 : 03:12:44

quote:
Originally posted by haroon2k9

try this..Its all depends on the index on the column.in this case
ordercol is to be indexed,you can have better performance.

select t1.col1,t2.col2,t2.col1 from tab1 as t1
outer apply(select top 1 col1 from tab2 where tab2.id=tbl.id order by ordercol)t2

edit:Code tag added

Thanks it works. But i feel both has equal amount of performance. And why do i have to create an index on order by column instead of foreign key column.

Iam a slow walker but i never walk back

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-23 : 03:19:27

quote:
Originally posted by dineshrajan_it

quote:
Originally posted by haroon2k9

try this..Its all depends on the index on the column.in this case
ordercol is to be indexed,you can have better performance.

select t1.col1,t2.col2,t2.col1 from tab1 as t1
outer apply(select top 1 col1 from tab2 where tab2.id=tbl.id order by ordercol)t2

edit:Code tag added

Thanks it works. But i feel both has equal amount of performance. And why do i have to create an index on order by column instead of foreign key column.

Iam a slow walker but i never walk back

please test with large set of data and see and also have a look at index on BOL.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources