Yea, thats right. I am not worried about the condition, which I can change any time. I want the query to work first, then thinking of keeping the rownumber between 500 and 800... thanks
–
JosephSep 23 '09 at 17:31

1

@Joseph: Why are you trying to avoid using a CTE?
–
OMG PoniesSep 23 '09 at 17:32

@rexem - I am not an expert in SQL Server. I'm trying to help a team in a big project where they are facing lots of issues with performance. They are using UDFs and CTEs. In one of the table, they have just 5000 records, and if 5 users accessing a search, it take more than a minute to retrieve. Some time, it fails and time out. So, I'm trying to avoid CTE and UDFs and trying to come up with a straight forward SQL query which can solve the performance issues.
–
JosephSep 23 '09 at 17:36

Hi all, Please see the link I've posted below which answers using row_number() in a different way. Can somebody compare my initial query with the one in the link? Appreciate the help..
–
JosephSep 23 '09 at 17:37

In response to comments on rexem's answer, with respect to whether a an inline view or CTE would be faster I recast the queries to use a table I, and everyone, had available: sys.objects.

WITH object_rows AS (
SELECT object_id,
ROW_NUMBER() OVER ( ORDER BY object_id) RN
FROM sys.objects)
SELECT object_id
FROM object_rows
WHERE RN > 1
SELECT object_id
FROM (SELECT object_id,
ROW_NUMBER() OVER ( ORDER BY object_id) RN
FROM sys.objects) T
WHERE RN > 1

The query plans produced were exactly the same. I would expect in all cases, the query optimizer would come up with the same plan, at least in simple replacement of CTE with inline view or vice versa.

Of course, try your own queries on your own system to see if there is a difference.

Also, row_number() in the where clause is a common error in answers given on Stack Overflow. Logicaly row_number() is not available until the select clause is processed. People forget that and when they answer without testing the answer, the answer is sometimes wrong. (A charge I have myself been guilty of.)

Thx Shannon. What version of SQL Server were you using?
–
OMG PoniesSep 23 '09 at 18:48

So that means, the answer provided in that link is wrong? But, the person who posted the question agreed that its working.. Surprising.. :-)
–
JosephSep 23 '09 at 20:45

1

@Joseph, but if you look at another answer posted by the OP in the linked question, you will see that he link's to a version of the code that is not the same as in the accepted answer. I don't know why he accepted the answer, even though it would not run as entered. Maybe it was edited at some point after being accepted, maybe it was enough to get him going, even without being totally correct.
–
Shannon SeveranceSep 24 '09 at 2:48

"method 1" is like the OP's query from the linked question, and "method 2" is like the query from the selected answer. You had to look at the code linked in this answer to see what was really going on, since the code in the selected answer was modified to make it work. Try this:

DECLARE @YourTable table (RowID int not null primary key identity, Value1 int, Value2 int, value3 int)
SET NOCOUNT ON
INSERT INTO @YourTable VALUES (1,1,1)
INSERT INTO @YourTable VALUES (1,1,2)
INSERT INTO @YourTable VALUES (1,1,3)
INSERT INTO @YourTable VALUES (1,2,1)
INSERT INTO @YourTable VALUES (1,2,2)
INSERT INTO @YourTable VALUES (1,2,3)
INSERT INTO @YourTable VALUES (1,3,1)
INSERT INTO @YourTable VALUES (1,3,2)
INSERT INTO @YourTable VALUES (1,3,3)
INSERT INTO @YourTable VALUES (2,1,1)
INSERT INTO @YourTable VALUES (2,1,2)
INSERT INTO @YourTable VALUES (2,1,3)
INSERT INTO @YourTable VALUES (2,2,1)
INSERT INTO @YourTable VALUES (2,2,2)
INSERT INTO @YourTable VALUES (2,2,3)
INSERT INTO @YourTable VALUES (2,3,1)
INSERT INTO @YourTable VALUES (2,3,2)
INSERT INTO @YourTable VALUES (2,3,3)
INSERT INTO @YourTable VALUES (3,1,1)
INSERT INTO @YourTable VALUES (3,1,2)
INSERT INTO @YourTable VALUES (3,1,3)
INSERT INTO @YourTable VALUES (3,2,1)
INSERT INTO @YourTable VALUES (3,2,2)
INSERT INTO @YourTable VALUES (3,2,3)
INSERT INTO @YourTable VALUES (3,3,1)
INSERT INTO @YourTable VALUES (3,3,2)
INSERT INTO @YourTable VALUES (3,3,3)
SET NOCOUNT OFF
DECLARE @PageNumber int
DECLARE @PageSize int
DECLARE @SortBy int
SET @PageNumber=3
SET @PageSize=5
SET @SortBy=1
--SELECT * FROM @YourTable
--Method 1
;WITH PaginatedYourTable AS (
SELECT
RowID,Value1,Value2,Value3
,CASE @SortBy
WHEN 1 THEN ROW_NUMBER() OVER (ORDER BY Value1 ASC)
WHEN 2 THEN ROW_NUMBER() OVER (ORDER BY Value2 ASC)
WHEN 3 THEN ROW_NUMBER() OVER (ORDER BY Value3 ASC)
WHEN -1 THEN ROW_NUMBER() OVER (ORDER BY Value1 DESC)
WHEN -2 THEN ROW_NUMBER() OVER (ORDER BY Value2 DESC)
WHEN -3 THEN ROW_NUMBER() OVER (ORDER BY Value3 DESC)
END AS RowNumber
FROM @YourTable
--WHERE
)
SELECT
RowID,Value1,Value2,Value3,RowNumber
,@PageNumber AS PageNumber, @PageSize AS PageSize, @SortBy AS SortBy
FROM PaginatedYourTable
WHERE RowNumber>=(@PageNumber-1)*@PageSize AND RowNumber<=(@PageNumber*@PageSize)-1
ORDER BY RowNumber
--------------------------------------------
--Method 2
;WITH PaginatedYourTable AS (
SELECT
RowID,Value1,Value2,Value3
,ROW_NUMBER() OVER
(
ORDER BY
CASE @SortBy
WHEN 1 THEN Value1
WHEN 2 THEN Value2
WHEN 3 THEN Value3
END ASC
,CASE @SortBy
WHEN -1 THEN Value1
WHEN -2 THEN Value2
WHEN -3 THEN Value3
END DESC
) RowNumber
FROM @YourTable
--WHERE more conditions here
)
SELECT
RowID,Value1,Value2,Value3,RowNumber
,@PageNumber AS PageNumber, @PageSize AS PageSize, @SortBy AS SortBy
FROM PaginatedYourTable
WHERE
RowNumber>=(@PageNumber-1)*@PageSize AND RowNumber<=(@PageNumber*@PageSize)-1
--AND more conditions here
ORDER BY
CASE @SortBy
WHEN 1 THEN Value1
WHEN 2 THEN Value2
WHEN 3 THEN Value3
END ASC
,CASE @SortBy
WHEN -1 THEN Value1
WHEN -2 THEN Value2
WHEN -3 THEN Value3
END DESC

@rexem, both method 1 and 2 use CTEs, the way they paginate and order rows is different. I'm not sure why this actual question is so different from the question that the OP links to (in the answer to this question by the OP), but my answer creates working code based on the link that the OP refers to
–
KM.Sep 23 '09 at 19:03

Thanks, I'm trying to compare the old post and this answers. [I don't know how to format this] Here is the answer provided by Tomalak. stackoverflow.com/questions/230058?sort=votes#sort-top Is this wrong? If he posted only half of the answer, how will I go ahead with his better performance way of doing my query? Please give me some more light to proceed.. thanks
–
JosephSep 23 '09 at 20:52

Joseph, if you look at that page, Leandro Lopez has an answer where he links to his version and Tomalak's version. Tomalak's version is different than what was giving in Tomalak's answer, and has the row_number() function moved into a CTE.
–
Shannon SeveranceSep 23 '09 at 18:28