ORIGINAL SIN IN SQL 2005!

[Ross Presser]

from the December 2004 _Redmond Magazine_, "Finding Gold in Yukon":
http://redmondmag.com/columns/articl...itorialsID=838

"Developers will also pick up the usual new-version enhancements to T-SQL,
including error handling, recursive queries, a ROW_NUMBER function, a PIVOT
relational operator and more."

ROW_NUMBER???? What possible purpose could that have???

[Simon Hayes]

"Ross Presser" <rp******@imtek.com> wrote in message
news:12***************@rpresser.invalid...

from the December 2004 _Redmond Magazine_, "Finding Gold in Yukon":
http://redmondmag.com/columns/articl...itorialsID=838

"Developers will also pick up the usual new-version enhancements to T-SQL,
including error handling, recursive queries, a ROW_NUMBER function, a
PIVOT
relational operator and more."

ROW_NUMBER???? What possible purpose could that have???

http://www.winnetmag.com/Article/Art...302/42302.html

Simon

[Serge Rielau]

Ross Presser wrote:

from the December 2004 _Redmond Magazine_, "Finding Gold in Yukon":
http://redmondmag.com/columns/articl...itorialsID=838

"Developers will also pick up the usual new-version enhancements to T-SQL,
including error handling, recursive queries, a ROW_NUMBER function, a PIVOT
relational operator and more."

ROW_NUMBER???? What possible purpose could that have???

If it is what I think it is it's GOOD not BAD(tm):

SELECT ROW_NUMBER() OVER (ORDER BY salary PARTITION BY deptid), name, deptid
FROM employee

Returns a list of employees ranked by salary per department.
This is not to be confused with "rowid" or "rownum"
This OLAP function is part of ANSI SQL. (SQL99 I think)

Cheers
Serge

[Ross Presser]

On Tue, 04 Jan 2005 15:24:59 -0500, Serge Rielau wrote:

Ross Presser wrote:

from the December 2004 _Redmond Magazine_, "Finding Gold in Yukon":
http://redmondmag.com/columns/articl...itorialsID=838

"Developers will also pick up the usual new-version enhancements to T-SQL,
including error handling, recursive queries, a ROW_NUMBER function, a PIVOT
relational operator and more."

ROW_NUMBER???? What possible purpose could that have???

If it is what I think it is it's GOOD not BAD(tm):

SELECT ROW_NUMBER() OVER (ORDER BY salary PARTITION BY deptid), name, deptid
FROM employee

Returns a list of employees ranked by salary per department.
This is not to be confused with "rowid" or "rownum"
This OLAP function is part of ANSI SQL. (SQL99 I think)

Cheers
Serge

Whew ... I'm somewhat mollified, knowing that it's part of a group of OLAP
functions. It seems like it's kind of shimming a client-oriented
preprocessor between the real client and the relational database. I guess I
can buy into that.

I thought they were just pasting in a rownum type of function that had a
relationship to the physical table, just because people were agitating for
it.

That said, I can visualize bad programmers using OLAP functions as a crutch
for bad code ...

[Serge Rielau]

Ross Presser wrote:

On Tue, 04 Jan 2005 15:24:59 -0500, Serge Rielau wrote:

Ross Presser wrote:

from the December 2004 _Redmond Magazine_, "Finding Gold in Yukon":
http://redmondmag.com/columns/articl...itorialsID=838

"Developers will also pick up the usual new-version enhancements to T-SQL,
including error handling, recursive queries, a ROW_NUMBER function, a PIVOT
relational operator and more."

ROW_NUMBER???? What possible purpose could that have???

If it is what I think it is it's GOOD not BAD(tm):

SELECT ROW_NUMBER() OVER (ORDER BY salary PARTITION BY deptid), name, deptid
FROM employee

Returns a list of employees ranked by salary per department.
This is not to be confused with "rowid" or "rownum"
This OLAP function is part of ANSI SQL. (SQL99 I think)

Cheers
Serge

Whew ... I'm somewhat mollified, knowing that it's part of a group of OLAP
functions. It seems like it's kind of shimming a client-oriented
preprocessor between the real client and the relational database. I guess I
can buy into that.

I thought they were just pasting in a rownum type of function that had a
relationship to the physical table, just because people were agitating for
it.

That said, I can visualize bad programmers using OLAP functions as a crutch
for bad code ...

Not all. It is a lot easier for the relational engine to do OLAP than to
put it on the client or anything inbetween. Things get really
interesting when you start looking at windowing. Like the average stock
price over a week's period.
Let's assume
stock(symbol, date, price, primary key (symbol, price))

select symbol, date, price, avg(price) over(partition by symbol order by
date rows between 3 preceding and 3 following) as avg price from stock
order by symbol, date;

All this can be done with done with either an index scan (with row
fetch) or one sort over a table scan buffering 7 rows in the pipeline.

Cheers
Serge

[--CELKO--]

Error handling was part of SQL/PSM.

The WITH operator is part of SQL-99. The recursive part can be done
better with other methods in most cases. But WITH is very handy for
"creating a VIEW on the fly" and using it by name in several places

ROW_NUMBER is part of the SQL-99 OLAP extensions. Oracle and IBM
pushed the syntax in ANSI You can add an OVER() clause to the
aggregate functions give you results over a limited range of rows.
This is a way to number things within a sub-grouping. The OLAP stuff
can also be written in truly horrible SQL-92. The advantage is a
shorthand and the ability of the compiler to take advantage of an
architecture based on a contigous physical file model of storage and
access.
I'd have to look up PIVOT (why not use CROSSTABS, like we have for the
past 250 years?), but that might be pure Microsoft.