September 2, 2013 at 4:10 am
I need to understand which one is better with respect to performance :
1. SQL 2012 has introducted FETCH OFFSET ...
SELECT FirstNm,LastNm
FROM Person
ORDER BY FirstNm
OFFSET 5 ROWS
FETCH NEXT 10 ROWS ONLY
2. SQL 2008 has an option of ROW_NUMBER() and then pick up specific row numbers. Sample below -
SELECT * FROM
(SELECT t1.colX, t2.colY
ROW_NUMBER() OVER (ORDER BY t1.col3) AS row
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.col1=t2.col2
)a
WHERE row >= n AND row <= m
Note: there is an index of t1.Col3.
September 2, 2013 at 5:56 am
npranj (9/2/2013)
I need to understand which one is better with respect to performance :1. SQL 2012 has introducted FETCH OFFSET ...
SELECT FirstNm,LastNm
FROM Person
ORDER BY FirstNm
OFFSET 5 ROWS
FETCH NEXT 10 ROWS ONLY
2. SQL 2008 has an option of ROW_NUMBER() and then pick up specific row numbers. Sample below -
SELECT * FROM
(SELECT t1.colX, t2.colY
ROW_NUMBER() OVER (ORDER BY t1.col3) AS row
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.col1=t2.col2
)a
WHERE row >= n AND row <= m
Note: there is an index of t1.Col3.
Best thing you could do is test them. What works best in one situation may not work the best in another.
September 3, 2013 at 8:39 am
npranj (9/2/2013)
I need to understand which one is better with respect to performance :1. SQL 2012 has introducted FETCH OFFSET ...
SELECT FirstNm,LastNm
FROM Person
ORDER BY FirstNm
OFFSET 5 ROWS
FETCH NEXT 10 ROWS ONLY
2. SQL 2008 has an option of ROW_NUMBER() and then pick up specific row numbers. Sample below -
SELECT * FROM
(SELECT t1.colX, t2.colY
ROW_NUMBER() OVER (ORDER BY t1.col3) AS row
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.col1=t2.col2
)a
WHERE row >= n AND row <= m
Note: there is an index of t1.Col3.
Having tried this on my setup, the new method is slightly better and IMO more readable.
Here is an external link that may provide more insight...
http://www.dbadiaries.com/new-t-sql-features-in-sql-server-2012-offset-and-fetch
gsc_dba
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply