Interviewing for a Developer Position :: SQL Server 2012

1. Describe the difference between ROW_NUMBER and RANK.
   
   
2. Describe the difference between the ROWS and RANGE window frame units.
   
   
3. Why can you not refer to a window function in the WHERE clause of a query and what is the workaround for that?

1. The ROW_NUMBER function isn't sensitive to ties in the window ordering values. Therefore, the computation is deterministic only when the window ordering is unique. When the window ordering isn't unique, the function isn't deterministic. The RANK function is sensitive to ties and produces the same rank value to all rows with the same ordering value. Therefore, it is deterministic even when the window ordering isn't unique.
   
   
2. The difference between ROWS and RANGE is actually similar to the difference between ROW_NUMBER and RANK, respectively. When the window ordering isn't unique, ROWS doesn't include peers, and therefore it isn't deterministic, whereas RANGE includes peers, and therefore it is deterministic. Also, the ROWS option can be optimized with an efficient in-memory spool; RANGE is optimized with an on-disk spool and therefore is usually slower.
   
   
3. Window functions are allowed only in the SELECT and ORDER BY clauses because the initial window they are supposed to work with is the underlying query's result set. If you need to filter rows based on a window function, you need to use a table expression like a CTE or derived table. You specify the window function in the inner query's SELECT clause and assign the target column an alias. You can then filter the rows by referring to that column alias in the outer query's WHERE clause.