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 Using APPLY
If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s