Index in a nutshell

In a databse table data stored in pages. One page is 8KB. Without an index these data pages are stored not in an order. Since there is no order of datapages accessing is slower as it required to scan through all the pages and records to find coorrect record.
To overcome this problem, an index can be created. Similar to data, indexs are too stored in pages. An index in a index page contains the index key value and the reference to the next page and at the leaf level, index key and the row locator to the actual data row or reference to the clustered key (if there is a clustered index).
Since an index refers to a row locator if there is no clustered index, still it required to scan all the records if a column is searched other than the column(s) in the index key.
If there is a clustered index, then the leaf levels of index refers to the clustered index key.
In clustered index, in the leaf levels itself contains the actual data pages and are in order. That is the differece than the non clustered index.

SQL 2012 LAG and LEAD

New TSQL functions LAG and LEAD are two amaizing features. Lets have look at a data set like below and see how these functions are important

Here it contains sales order details with customer, location, date and amount etc. To obtain the monthly total sales for 2012 follwing query can be used

SELECT MONTH(SODate) MonthOfYear ,SUM(OrderAmount) TotalSales
FROM dbo.SalesOrder
WHERE YEAR(SODATE) = 2012
GROUP BY MONTH(SODate)
ORDER BY MONTH(SODate)

So the results set will be like this

Now in each row we need to show total sales for the prevous month and the next month. In prevous versions of SQL server we need to join above results set again to obtain this results. But with the new two features LAG and LEAD this can be achived easily. Below is the query used.

SELECT MONTH(SODate) MonthOfYear ,SUM(OrderAmount) TotalSales
,LAG(SUM(OrderAmount),1) OVER( ORDER BY MONTH(SODate) ) PreviousMonth
,LEAD(SUM(OrderAmount),1) OVER( ORDER BY MONTH(SODate) ) NextMonth

FROM dbo.SalesOrder
WHERE YEAR(SODATE) = 2012
GROUP BY MONTH(SODate)
ORDER BY MONTH(SODate)

And the reslts set will be like this.