Poor man's LAG() and LEAD()

How to do LAG() and LEAD() in SQL Server 2008.

Recursive queries vs. nonrecursive queries; and consistent reads vs. currently reads

Recursive reads vs. non-recursive reads: best to quote Tom Kyte on this one!

non-recursive statements are statements issued by the client to the server.

recursive statements are statements executed by that non-recursive call.


non-recursive statements can be sql, or plsql - they are just "a statement submitted by client to server"

recursive statements can be plsql, or sql - they are just "a statement executed by the statement submitted by the client to the server"

Consistent reads vs. current reads - got the following from the Oracle-l mailing list from Mark Boback:

A 'db block get' is a current mode get. That is, it's the most up-to-date
copy of the data in that block, as it is right now, or currently. There
can only be one current copy of a block in the buffer cache at any time.
Db block gets generally are used when DML changes data in the database.
In that case, row-level locks are implicitly taken on the updated rows.
There is also at least one well-known case where a select statement does
a db block get, and does not take a lock. That is, when it does a full
table scan or fast full index scan, Oracle will read the segment header
in current mode (multiple times, the number varies based on Oracle version).

A 'consistent get' is when Oracle gets the data in a block which is consistent
with a given point in time, or SCN. The consistent get is at the heart of
Oracle's read consistency mechanism. When blocks are fetched in order to
satisfy a query result set, they are fetched in consistent mode. If no
block in the buffer cache is consistent to the correct point in time, Oracle
will (attempt to) reconstruct that block using the information in the rollback
segments. If it fails to do so, that's when a query errors out with the
much dreaded, much feared, and much misunderstood ORA-1555 "snapshot too old".