I was talking today to another follower of the OradeaDevs Facebook page (firstname.lastname@example.org) about querying data on a specific set, based on whatever column, e.g. Name.
Considering a Product table which has the following columns IDProduct, ProductName, one would probably try to query the set-data by using something we call a sub-query select (or sometimes inner select). Even though the expected result is correct, in my opinion it is best to stay away from any kind of sub-queries, because these are usually the reason for why SQL databases respond slow (after the use of cursors, of course). Keep in mind that even if you’re Chuck Norris and do some sort of magic indexes configuration, once the SQL query engine will scan the clustered/non-clustered index tables, spool the tables over and over again, remove the carthesian products resulted from the subquery values etc. the query result will respond slow.
In my opinion, it is best have a larger set of the data kept in a buffer memory-like area and query the last set from there. For example, if one would like to query the entries, ordered alphabetically by their name, from 21 to 30, it would be a better solution to query the data from 1 to 30 and afterwards to query the last 9 values (which is bassically the case of using a simple WHERE clause).
And since it comes to temporary data tables, considering SQL Server 2K5 is around for some time now, I would suggest the use of common table expressions, which are a great way, from the performant p.o.v., to save your temporary data you want to query on.
So, there the use of a CTE which lists the entries between 29989 and 30000 of a table called Product, ordered by their product name:
WITH TopCTE AS ( SELECT TOP 30000 ROW_NUMBER() OVER(ORDER BY ProductName) AS RowNumber, ProductName FROM Product ORDER BY ProductName ) SELECT * FROM TopCTE WHERE RowNumber > 29990
That’s it for now!