Re: Any new analytic SQL Features in SQL2005?

From: Steve Kass (skass_at_drew.edu)
Date: 07/20/04 

Date: Tue, 20 Jul 2004 16:00:43 -0400

Leander,

  SQL Server 2005 is slated to provide several analytic functions not
part of the current version: RANK, DENSE_RANK, NTILE, ROW_NUMBER, to
name a few, as well as quite a few other new language features, like
PIVOT and UNPIVOT, OUTER APPLY and CROSS APPLY. Materialized views are
already a feature in SQL Server 2000, under the guise of user-defined
table-valued functions with parameters. I don't recall seeing plans to
add FIRST and LAST aggregates, but if those "work" in Oracle the way I
think they do, they aren't deterministic, and users who want similar
functionality will be able to define their own (deterministic) versions
in C#, VB.NET, or other languages, as they will be able to write custom
aggregates.

  Yukon won't have a native facility for nested tables, but applications
that could benefit from them will be easier to write, either with help
from APPLY, or by packing the nested table values array-style with one
of the new types varbinary(max) or varchar(max).

  There is already a GROUPING function in 2000, but I don't know what
might be in store for ROLLUP/CUBE.

  Though you didn't ask about it, SQL Server 2005 will support recursive
queries, and they will offer a way to handle hierarchical data that
Oracle uses CONNECT BY for.

Steve Kass
Drew University

Leander wrote:

>Common Table Expressions introduced in SQL2005 is realy great thing.
>Now I wonder are there plan to add any new analytic SQL features which
>Oracle has since 9i version like:
>FIRST/LAST
>GROUP BY GROUPING SETS
>Composite Columns on ROLLUP/CUBE clause
>GROUPING, GROUPING_ID and GROUP_ID
>MATERIALIZED VIEW?
>Multiset Operations for Nested Tables
>
>
>
>

Relevant Pages

  • Re: Enterprise Library - DataAccessApplicationBlock - MySql/DB2
    ... The fact that you set parameters differently for different providers isn't ... Oracle or Microsoft told me "We don't like the way you did that", ... ISDATE doesn't produce the same results in both Sql Server ... and Oracle both have a ton of specific features that others don't have. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: SQL Server 2005 vs Oracle
    ... Moving to a new platform is costly. ... familiar with either Oracle or SQL Server, ... Features: The platforms have a lot in common, ...
    (comp.databases.ms-sqlserver)
  • Features in SQL Server but Not in Oracle
    ... Part of the material will be a list of features that are in Oracle that aren't in SQL Server, and also any features that are in SQL Server but not in Oracle. ... I'm not meaning stuff like "SQL Server is easier to use", or "SQL Server is much cheaper" - I mean genuine features with no equivelant *regardless* of price or ease of use concerns. ...
    (microsoft.public.sqlserver.server)
  • Re: Pros and Cons of SQL Server 2005 vs. SQL Server 2008
    ... Edition) for our projectinstead of Oracle ... You may find the "Inside SQL Server" series useful. ... But then it depends a lot on what features you will need. ...
    (comp.databases.ms-sqlserver)
  • Re: Performance: Conditions in WHERE clause vs. conditions in INNER JOIN?
    ... which, as you point out, is equvalent to EXCEPT in SQL Server. ... I can appreciate some of Bill's frustration when it comes to things ... such as analytic functions and regular expressions but in set operators ... SQL Server and Oracle are equivalent. ...
    (comp.databases.ms-sqlserver)
Quantcast