SQL SERVER 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 DocumentNo 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.