Implement Sub-Queries

(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

·         CTEs

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.

APPLY Operator

                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:

use MeasureMySkills;

go

create table NumbersTable

(

       id int identity(1, 1) not null,

       MyNumber int not null

);

go

insert into NumbersTable(MyNumber)

values

       (3),

       (7),

       (12),

       (5),

       (93),

       (132);

go

create function AddMore

(

       @original_num int,

       @add_by int

)

returns table

as

       return

       (

              select

                     @original_num + @add_by as NewNum

       )

go

select

       nt.id,

       nt.MyNumber,

       ao.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.

CTEs

                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.

use AdventureWorks2012;

go

;with PersonCTE as

(

       select

              BusinessEntityID,

              FirstName,

              LastName,

              EmailPromotion

       from Person.Person

)

select

       EmailPromotion,

       count(*) as EmailPromoCount

from PersonCTE

group by EmailPromotion;

References

·         BOL reference on PIVOT and UNPIVOT

·         BOL reference on Using APPLY

·         BOL reference on Common Table Expressions

If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.