• Not crazy about this question as it is misleading. LEAD / LAG are not generally used to retrieve "all rows before this one" without doing some additional work to the query.

    Really the purpose of LEAD / LAG is to provide an easy way to access 1 forward / 1 back of the current resultset without the need for a self join.

    Lead: http://msdn.microsoft.com/en-us/library/hh213125.aspx

    Lag: http://msdn.microsoft.com/en-us/library/hh231256.aspx

    They don't really make any mention of a full result set being returned as a product of this analytic function alone.