Wrox Programmer Forums
|
SQL Server 2008 General discussion of SQL Server *2008* version only - not related to a specific book.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2008 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 26th, 2012, 08:42 AM
Authorized User
 
Join Date: Aug 2006
Posts: 41
Thanks: 1
Thanked 0 Times in 0 Posts
Default Common Table Expression

Hi,
I'm wondering if someone can help explain whether my thought on the CTE below is correct. I'm using SQL to page data using the Row_Number() function. If I write a query like this:

WITH MyCTE AS (
SELECTRow_Number()OVER (ORDERBY colA) RowNumber,
colB,
colC
FROM
myTable
)

SELECT TOP(50) * FROM MyCTE WHERE RowNumber > @LastRecordId

This works great as it gets the next page's data using the last row number of the previous page as the next page's parameter.

Now, if the select from "myTable" has 100000 rows, does the top 50 select from "MyCTE" limit that query once it's found the top 50 records based on the row number? Or does it retrieve the entire 100000 rows then sub query them, because that to me kind of defeats the purpose of what I'm trying to achieve - only get the records needed by the recordnumber and limited to 50 records? Is this the case?





Similar Threads
Thread Thread Starter Forum Replies Last Post
ReportViewer - Table cell expression problem VerbatimBOT .NET Framework 2.0 0 September 25th, 2008 08:41 AM
Common value scandalous Excel VBA 1 December 21st, 2007 11:01 PM
Message> in query expression <expression>. (Error ybg1 Access 5 July 15th, 2007 05:42 AM
Regular Expression to remove <table> </table> tags mathalete CSS Cascading Style Sheets 2 January 23rd, 2006 01:59 PM
Common question, not so common answer? flyin ADO.NET 5 March 24th, 2004 06:50 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.