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.
SELECT country, region, city FROM HR.Employees
SELECT country, region, city FROM Sales.Customers;
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
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
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.
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
The APPLY operator operates on left and right table expressions as inputs same like join.
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.
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 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.