2

So I came across the following at work, and I can tell right away what it's for but I want to find any documentation for it and can't find anything online!

with details as
(
    select *,
    row_number() over (order by CREATED_DATE) as [Row]
    from
    (
        select top 10 * from MyTable
    ) t
)
select *
from details
where [Row] > @lowLimit and [Row] < @highLimit

This looks to me like its for paging functionality. However, I don't know exactly what structure I'm looking at within the sql syntax. Does anyone recognize this syntax and can you point me to where I can read more about it?

Thanks!

IWriteApps
  • 973
  • 1
  • 13
  • 30

1 Answers1

7

That's a common table expression. These are used as temporary result sets for single queries. They are treated by the following query much like a view. You can do some neat stuff with them, like recursion!

Here's a brief description of their functionality from the link:

  • Create a recursive query.
  • Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
  • Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
  • Reference the resulting table multiple times in the same statement.

Regarding semicolons, please check out this answer for a really useful tip - why you should always preface CTEs with semicolons.

Community
  • 1
  • 1
Nick Vaccaro
  • 5,428
  • 6
  • 38
  • 60
  • I'd be careful about "used as temporary tables" - inline view is a much more accurate and unambiguous description. – Aaron Bertrand Apr 22 '13 at 20:03
  • You don't need to put the semicolon in front if you *always* put them where they actually belong - at the end of a statement: https://sqlblog.org/2009/09/03/ladies-and-gentlemen-start-your-semi-colons –  Apr 22 '13 at 20:32
  • @a_horse_with_no_name I tend to err on the side of caution. Besides, if someone comes along later and deletes something above it, you're safe. I know that's not great reasoning, but it happens, like, all the time. – Nick Vaccaro Apr 22 '13 at 20:34
  • @a_horse_with_no_name And also I've been reprimanded on this very site multiple times for posting "broken" code - a CTE solution which was copied and pasted directly into an existing code block with no semi-colons in sight. Particularly here where we're offering code samples for others' consumption, we have absolutely no control over the type of code they'll be mixed with. – Aaron Bertrand Apr 22 '13 at 20:56