Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #219

Re: Help needed to write a qury

Show key headers only | View raw

On Apr 26, 2:18 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Fred. (ghrno-goo...@yahoo.com) writes:
> > The WITH clause works in a manner similar to a derived table, but sets
> > up the syntax so that it can be referenced more than once.  In this
> > instance it allows the table to joined to itself rather than a second
> > derivation of itself as would happen using the derived table syntax.
>
> Correct. This is known as a CTE, Common Table Expression.
>
> > The OVER construct (just what is the syntax term?) specifies that row
> > numbering restarts for each new value of the field specified in
> > PARTITION BY, with the rows arranged within each partition as
> > specified in ORDER BY.
>
> Correct. It's called the OVER clause.
>
> > Do you know if the engine optimizes by indexing the WITH table on the
> > PARTTION field and the field receiveing row_number()?
>
> The CTE is purely logical. Some products are able to materialize the CTE as
> optimisation, but SQL Server is not. Thus, in some cases it can be better
> to use a temp table to hold the numbering.
>
> Lennart mention LEAD and LAG. They are also subclauses within OVER. They
> are not available in SQL Server currently, but in products that have LAG and
> LEAD, this can be written as a straight query without a self-join.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Thanks a lot for all.

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Help needed to write a qury Dinesh <dinesht15@gmail.com> - 2011-04-24 10:26 -0700
  Re: Help needed to write a qury "Fred." <ghrno-google@yahoo.com> - 2011-04-24 12:27 -0700
  Re: Help needed to write a qury Erland Sommarskog <esquel@sommarskog.se> - 2011-04-24 21:51 +0200
    Re: Help needed to write a qury Henk van den Berg <hvandenberg@xs4all.nl> - 2011-04-25 06:50 +0200
      Re: Help needed to write a qury Erland Sommarskog <esquel@sommarskog.se> - 2011-04-25 10:14 +0200
        Re: Help needed to write a qury Henk van den Berg <hvandenberg@xs4all.nl> - 2011-04-25 11:52 +0200
          Re: Help needed to write a qury Erland Sommarskog <esquel@sommarskog.se> - 2011-04-25 15:30 +0200
            Re: Help needed to write a qury Erland Sommarskog <esquel@sommarskog.se> - 2011-04-25 15:31 +0200
              Re: Help needed to write a qury Henk van den Berg <hvandenberg@xs4all.nl> - 2011-04-25 16:29 +0200
              Re: Help needed to write a qury "Fred." <ghrno-google@yahoo.com> - 2011-04-25 11:41 -0700
                Re: Help needed to write a qury Erland Sommarskog <esquel@sommarskog.se> - 2011-04-25 23:18 +0200
                Re: Help needed to write a qury Dinesh <dinesht15@gmail.com> - 2011-04-26 01:58 -0700
    Re: Help needed to write a qury "Fred." <ghrno-google@yahoo.com> - 2011-04-25 06:28 -0700
    Re: Help needed to write a qury Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2011-04-25 17:44 +0200
      Re: Help needed to write a qury Erland Sommarskog <esquel@sommarskog.se> - 2011-05-25 00:01 +0200
        Re: Help needed to write a qury Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2011-05-25 21:15 +0200
  Re: Help needed to write a qury --CELKO-- <jcelko212@earthlink.net> - 2011-05-26 06:19 -0700

csiph-web