I don’t know if Adam Mechanic coined the acronym “POC”. However, I recently heard Itzik Ben-Gan attribute it to him, regarding an indexing technique designed to optimize queries. In the examples presented, and also in the above mentioned authors E-book, T-SQL Querying, the index is being applied to Windowing functions introduced in SQL Server 2012.
Partition
Order
Coverage
The partitioning portion of your index includes the columns used to filter your data. It helps if the order of the columns in your index match your window function PARTITION BY clause. They should match in number, and the order they are selected.
Following the Partition columns, the index can include the columns you will use to sort the data matching your Partition requirements. Again, include the columns in the same order as they will be listed in the ORDER BY clause of your window function. If you don’t have enough room in your index to include all the necessary columns, then you may have to re-factor your design. Another alternative is to include the last columns in the INCLUDE portion of your create index.
The final portion of your index takes advantage of the INCLUDE clause when creating an index. Including all of the rest of the columns not yet stated, needed to perform your WINDOW function, allows SQL Server to execute the query completely from the index, instead of having to link to the physical table for the remaining columns. This linking process slows down the query. For this reason, and index of this sort is called COVERING index, because it covers the table with a little different organization structure, unique for a specific purpose.
Try a little POC in your SQL Engine. Then share some of your results in our comments.
Cheers,
Ben