T-SQL Querying

by Ben-Gan Itzik

SELECT empid, orderyear, val FROM dbo.EmpYearValues UNPIVOT( val FOR orderyear IN([2013],[2014],[2015]) ) AS U;

unpivot

EXCEPT

how to find all the rows not in the other table

you specify the CLUSTERED keyword. Either way,

To watch the event information, go to Object Explorer in SSMS. Navigate through the folder hierarchy Management\Extended Events\Sessions. Expand the Sessions folder, right-click the session query_performance, and choose Watch Live Data. To show the output

Check performance

execution of the sample query against cold cache highlighted.

Inline table-valued functions Suppose you need a reusable table expression like a view, but you also need to be able to pass input parameters to the table expression. Views do not support input parameters. For this purpose, SQL Server provides you with inline table-valued functions (TVFs). As an example, the following code creates an inline TVF called GetTopOrders that accepts as inputs a customer ID and a number and returns the requested number of most-recent orders for the input customer:

CROSS APPLY allows you to apply a correlated table expression, you’re not limited to returning only one column.