CODEWITHSUNDEEP
sqlsql-serverselect
IWriteApps
IWriteApps

Reputation: 993

What is the "with [alias] as" in sql?

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!

Upvotes: 2

Views: 3522

Answers (1)

Nick Vaccaro
Nick Vaccaro

Reputation: 5504

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.

Upvotes: 7

Related Questions