SQL SERVER 2012 FEATURES

Window Ranking Functions
With window ranking functions, you can rank rows within a partition based on specified ordering.

ROW_NUMBER – Computes a unique sequential integer starting with 1
RANK – Differ from ROW_NUMBER in the sense that they assign the same ranking value to all rows that share the same ordering value
DENSE_RANK – Function returns the number of distinct ordering values that are lower than the current, plus 1.
NTILE(100) – you can arrange the rows within the partition in a requested number of equally sized tiles

Using Set Operators

Set operators operate on two result sets of queries, comparing complete rows between the results.

• INTERSECT – The INTERSECT operator returns only distinct rows that are common to both sets

SELECT country, region, city FROM HR.Employees
INTERSECT
SELECT country, region, city FROM Sales.Customers;

• EXCEPT – The EXCEPT operator performs set difference. It returns distinct rows that appear in the first query but not the second

Filtering Data with OFFSET-FETCH
The OFFSET-FETCH option is a filtering option that, like TOP, you can use to filter data based on a specified number of rows and ordering. and also has a skipping capability, making it useful for ad-hoc paging purposes

OFFSET – OFFSET clause indicating how many rows you want to skip (0 if you don’t want to skip any) optionally
FETCH – FETCH clause indicating how many rows you want to filter
SELECT*
FROM #tmp_table
ORDER BY Document No
OFFSET 2 ROWS FETCH NEXT 5 ROWS ONLY;

Window Offset Functions
Window offset functions return an element from a single row that is in a given offset from the current row in the window partition

LAG – The LAG function returns an element from the row in the current partition that is a requested number of rows before the current row
LEAD – The LEAD function returns an element from the row that is in the requested offset after the current row
FIRST_VALUE and LAST_VALUE – functions return a value expression from the first or last rows in the window frame

Window Aggregate Functions
Window aggregate functions are the same as the group aggregate functions (for example, SUM, COUNT, AVG, MIN, and MAX), except window aggregate functions are applied to a window of rows defined by the OVER clause. One of the benefits of using window functions is that unlike grouped queries, windowed queries do not hide the detail

• UNBOUNDED PRECEDING or FOLLOWING, meaning the beginning or end of the partition, respectively
• CURRENT ROW, obviously representing the current row

CTEs
A common table expression (CTE) is a similar concept to a derived table. it’s a named table expression that is visible only to the statement that defines it.

RECURSIVE
CTEs also have a recursive form. The body of the recursive query has two or more queries, usually separated by a UNION ALL operator. At least one of the queries in the CTE body, known as the anchor member, is a query that returns a valid relational result. The anchor query is invoked only once. In addition, at least one of the queries in the CTE body, known as the recursive member, has a reference to the CTE name. This query is invoked repeatedly until it returns an empty result set

APPLY
The APPLY operator operates on left and right table expressions as inputs same like join.

• CROSS APPLY, The right table expression can have a correlation to elements from the left table
• OUTER APPLY, OUTER APPLY operator includes in the result rows from the left side that get an empty set back from the right side. NULLs are used as placeholders for the result columns from the right side.
• COMPARE WITH JOIN, With a JOIN operator, both inputs represent static relations. With APPLY, the left side is a static relation, but the right side can be a table expression with correlations to elements from the left table.

Working with Multiple Grouping Sets
you can define multiple grouping sets in the same query. In other words, you can use one query to group the data in more than one way. T-SQL supports three clauses that allow defined multiple grouping sets: GROUPING SETS, CUBE, and ROLLUP. You use these in the GROUP BY clause.

• GROUPING SETS, You can use the GROUPING SETS clause to list all grouping sets that you want to define in the query
• CUBE, The CUBE clause accepts a list of expressions as inputs and defines all possible grouping sets that can be generated from the inputs—including the empty grouping set.
• ROLLUP, The ROLLUP clause is also an abbreviation of the GROUPING SETS clause, but you use it when there’s a hierarchy formed by the input elements

Pivoting Data
Pivoting is a technique that groups and aggregates data, transitioning it from a state of rows to state of columns. In all pivot queries, you need to identify three elements
rows, or grouping element.
cols, or spreading element
data, or aggregation element

Unpivoting Data
Unpivoting data can be considered the inverse of pivoting.

XML Data with XQuery
XQuery is a standard language for browsing XML instances and returning XML text or scalar value also you can generate XML from relational data with a query and shredding XML into relational tabular format.