(This is part of the Study Guide series, 70-457)
Microsoft’s Measured Skill description: This objective may include but is not limited to: identify problematic elements in query plans; pivot and unpivot; apply operator; cte statement; with statement
What I see:
· pivot and unpivot
· APPLY operator
PIVOT and UNPIVOT
In my opinion the best resource for learning and reviewing PIVOT and UNPIVOT is BOL. They have written a great explanation with very helpful examples. Please see this reference for further reading.
The APPLY operator is used with a table-valued function to pass parameters to the TVF and these are then “applied” to the result set. Below shows an example of this:
create table NumbersTable
id int identity(1, 1) not null,
MyNumber int not null
insert into NumbersTable(MyNumber)
create function AddMore
@original_num + @add_by as NewNum
from NumbersTable nt
cross apply AddMore(nt.MyNumber, 27) ao;
As you can see in the above example, by using the CROSS APPLY clause with the AddMore()TVF, the result set now contains the additional column NewNum with the calculated values from the function.
A Common Table Expression (CTE) is a temporary result set that has the scope of and can be used for a single, subsequent DML statement. Its definition starts out with the WITH keyword. It is commonplace to put a semi-colon in front of the WITH, as SQL Server needs to know it’s not part of a previous statement.
;with PersonCTE as
count(*) as EmailPromoCount
group by EmailPromotion;
If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at firstname.lastname@example.org.