Is it possible to nest SELECT statements?

Greetings community,

Another newbie’s question it is. Looking tutorials and some posts here I’ve been advised not to pull entire table through the local network, and torture client machines’ processors and memory. It’s been said that better solution is to pull just one part of the table.

So, imagine this: you want to make a part of your app that would allow your user to view list of orders. So you put one data grid view on the form, pull last 20 headers from the table into it and leave to user to choose one to be opened in another form. If user doesn’t find header they want, they press page up for example and previous 20 headers are loaded into data grid view. Of course, user could filter headers list by customer, or by distribution lane (having all customers residing in one part of the town), or whatever more, but let’s not complicate things at this moment, because I’m practically in the beginning.

I’m trying to make a stored procedure that would load penultimate 20 headers when user presses page up. So, not knowing any better, I created table variable that has the same structure as Orders table with one difference: OrderID column, which is identity (auto incremented) in Orders table, here is simply defined as bigint not null. Community member Visakh16 warned me few months ago it’s the bad practice to put self-incrementing columns in table variables.

At this moment there’s a query on my screen, which waits to become store procedure. After boring part with table variable definition, it goes like this:

INSERT INTO @OrdersTemp SELECT TOP 40 * FROM dbo.Orders ORDER BY OrderID DESC

SELECT TOP 20 * FROM @OrdersTemp ORDER BY OrderID ASC

To put that simply, I pull last 40 headers into table variable, and then pull first 20 from there. This thing works, and I just have to replace 40 with parameter, for every page up or down.

Now I have few questions.

-Is there some better way (considering performance) to achieve that? Here is the place where I wanted to ask the question from the title of the post. I don’t know much about T-SQL, and I’m not sure about the proper syntax to nest SELECT statements, if something like that is even possible

-Is there any better way (some built-in function) to find about the count of the rows in one table than

SELECT COUNT(OrdersID) FROM dbo.Orders

Thanks for any suggestions.