Ebook Dissecting SQL Server Execution Plans

The SQL Server Performance Series
Dissecting
SQL Server
Execution
Plans
By Grant Fritchey
In association with
ISBN: 978-1-906434-01-4 www.simpletalkpublishing.com

Shelving: Database/SQL Server
"
#! !
!" #$ % # &'(' % '
) ) ) * * +,
- %#
- ." ) + + + ) +
/ + + + * 0* * ) .- *
) .
1 + * ) * + + + + * *
2 ) * *
3 .
- *
$% %
.................................................................................................................................................... 4
- ...................................................................................................................................... #
* .................................................................................................................................%%
............................................................................................................................................%
* .................................................................................................................................................%4
%5 67 ! ........................................................................................................%&
8 9 8 : ; .........................................................................%&
: ...........................................................................................................................%&
: < = .............................................................................................................%$
: 67 ......................................................................................................................%
6 - 67 ................................................................................%
67 > .............................................................................................................%
8 - 6 67 ? , )) ........................................
67 ......................................................................................................... %
........................................................................................................................ %
7 .................................................................................................................................. %
@?A ................................................................................................................................ %
............................................................................................................................. %
.............................................................................................................................
>3 B * 67 .................................................................
8 * 67 ................................................................................
6 .................................................................................................... (
- .......................................................................................................... (
67 .............................................................................. '
8 * 7 67 ......................................................................................... $
6 7 ...........................................................................................
- 7 .................................................................................................
7 ...........................................................................................................
8 * @?A67 ........................................................................................ #
- 6 @?A ................................................................... #
@?A ..............................................................................................................(
@?A .................................................................................(
- C :A ) ...........................................................(
67 .............................................................................................................((
* @?A .......................................................................................((
........................................................................................................................................(&
5> 67 ) ! : ................................................($
A ) 67 .........................................................................($
: ..........................................................................................................(
7 ...............................................................................................................(
7 ...............................................................................................................(#
" 7 ......................................................................................................'%
D A C .............................................................................................................................'
.................................................................................................................................'&
>,A C ............................................................................................................................'$
E .....................................................................................................................................4
9 ? /E 0....................................................................................................................4
7 ...............................................................................................................4'
" A E ...................................................................................................................44
........................................................................................................................44
? E .................................................................................................................................4&
- 8 96>6 ..........................................................................................................4
67 * ><C !F <>,6> !F ...........................................................&
.............................................................................................................................................&
9 ? /- 0.........................................................................................................&
...........................................................................................................................................&(
> >* 67 ...............................................................................................&'
+C , 67 .............................................................................&&
.....................................................................................................................&&
C ...................................................................................................................&$
, ....................................................................................................................&
........................................................................................................................................$
(5 7 @?A67 ) ! : .......................................................$%
7 67 ...................................................................................................................$%
- 7 ) : ............................................................................................$%
- 7: ...........................................................................................$(
@?A67 .................................................................................................................$$
- 6 @?A ........................................................................................................$
- - @?A ............................................................................................................... %
........................................................................................................................................ %
'5 C ? 7: .................................................................. (
........................................................................................................................ (
, .............................................................................................................................. '
- * , ................................................................................... '
-, - AF ............................................................................................. $
67 ......................................................................................................#
B * ..............................................................................................................................................#
B * ........................................................................................................................#
7 B * ..........................................................................................................................#(
7 ...........................................................................................................................................#4
7 5- ! A ...............................................................#4
7 .......................................................................................................................#
7 ............................................................................................................%
.....................................................................................................................................% %
45 67 * 9 .......................................................................% (
: 9 ................................................................................................................................% (
9- 9G<>,6> ><C ...................................................................................................% (
?6> 6 G9- 9 G <" - C" <" ...........................................................................% 4
A<< G?6> 6G9- 9 E< " .........................................................................................% &
- ....................................................................................................................................% #
<> 6 <>,6>..................................................................................................................%%
?-@,< ..............................................................................................................................%%%
< ? H6 <>..................................................................................................................%%(
->-?6 6> H- <" ? A6G <> 6, ..............................................................%%&
>6 <? A6........................................................................................................................%%&
><!C A-"....................................................................................................................%%
D66 A-" .........................................................................................................................%%
D66 @6, A-" ...........................................................................................................%%
6@ -", B 68 ................................................................................................................%%
?-@>6 C> <" ..............................................................................................................%%#
C 6 A-".............................................................................................................................%%#
E 9 ....................................................................................................................................%
9 ..................................................................................................................................%
9 7..................................................................................................................% (
"<6@ -", ........................................................................................................................% (
",6@/0.................................................................................................................................% '
- > ><8 .................................................................................................................% 4
.....................................................................................................................................% $
&5 < ...........................................................................................................%
...........................................................................................................................%
A < ..................................................................................................................% #
< .................................................................................................................%('
? < ...........................................................................................................%('
- ......................................................................................................................%('
D6F 6 ....................................................................................................................%($
>6-,I<"AF ..........................................................................................................%(#
) .........................................................................................................%(#
......................................................................................................................................%''
$5 @?A 67 .................................................................................................%'&
<> @?A...................................................................................................................................%'&
< 6"@?A................................................................................................................................%4%
@: ........................................................................................................................................%4'
C 7 .........................................................................................................%4'
C 3 .......................................................................................................%4&
......................................................................................................................................%4$
5- ..............................................................................................................%&
> A 67 .....................................................................................%&
67 .................................................................................................%&'
? 7, ) ...................................................................................................%&'
) ............................................................................................%&4
- ! ;.........................................................................................%&&
67 67 ..................................................................................%&&
9 * = )) 67 .......................................................%&#
C ? ) 67 .....................................................................%$
< 1 ...............................................................................................................%$
:A ...................................................................................................................%$(
...........................................................................................................%$'
- ..................................................................................................%$4
.................................................................................................................................%$4
C ? ) 67 ....................................................................%$4
......................................................................................................................................%$$
7......................................................................................................................................................%$
- #
&$' ' $(
* ,!- ) ? +
. ,!-+ *
) + 1 .9
B!+ J E * :A )
)&. + .9 * K ,!-K. 9
)) + .
) ) - ) :A C /- 0
* :A + :A
+ - * + :A :A 8 * C
.9 ) ) )) ) " *6 :A C
/"6 C 0.
< * + + ) ) + * * + *
+ ) .
%
* %%
)%$* + %
* * ) . + + , )
)) 1 * . L
* . " 7+ * *) M * * * *
) * .F .
* * * 3 ) :A
. ) . + *
* * ) ) * ) +
3 N .
* 5 ) L .-
L * ) .
O
%
,% ($ ' ,$%
6 + ? ) :A +
)3 5
• 8 3 *;
• 7 ;
• 8 L 7 ;
• 8 3 ) 3 ;
• - .
)) + *
3 5 ;
67 * * L :A .
* * ) ) * 3 7 :A
+ 5
• 8 7 * 7 .
• 9 * + 1 +) ) 3 .
• 9 * 3 .
• + +
.
- ) 7 ) )
+ + * + +
* * :A :A .
) + L L * )
) 1 . + ) ) )
+ L ) ) +
) * 7 .
* . ? * ) )
7 + = *
1 + ) ) +
+ * * =
:A3 + 7 + .
) + 5
• 9 * 7 + * 7 @?A)
• - ) 7 +
) * ) *
• 9 * :A :A 1 P
7 + *+ P 7
• 9 * )
Q 7
• 9 * 7 * + + *
*
• 9 * @?A 7
• - +) = ) .
- * + :A + ) + 7
= .9 * + ) * ) 7 +
* ) . ) ) )
%(
) * = :A+ )) 7 +
. 9 * + ) * *
* 7 + ) .
%'
* %4
$( *$(
:A ) + *
) .< ) )
) * ) )
, + :A , - . +
* . + * + *
= ,!-+ * * ) 1 .
" + ,!- 5*
* ,!- ) + * + .
* + ,!-+
* . ) ) *+
.9 * + + ,!- ,!- * 5* *
* * * .
< ) ) ,!-L ) . +
) ,!- )
.< 7
+ * * * ) ) * :A * .
8 )* ) :A +
L * .< ) ) *
:A 7 .
L * . )
* ) * :A 7 + * +
* ) ) ) )
:A .
* * :A 7 *
* . ) :A
7 + + *
.< ) ) :A +
) + :A 7
) 67 ,!-.
- + * * 3 )
.< * * )
* )* 7 )) .
, ),!- 6 +> )*
%&
(-. / ' ,$% %& ,
- 7 + + )
)) * 3 :A3 .
67 * 3 * 7 + * 3 * 7 .
+ ) + ,!-L ) ) 3 .
> * 3 ) ) + Q<
)+ 7 ) 7 ) :A
. 7 + * ) * +
* 7+ * .-
7 .
) 7 +
+ 7 @?A) + ) * .
+*L ) * 5
! " 7 )
= L L ) * *
= + * *
# $ % # "* * ))
& ! ! ' #% # P*L
+ 7 @?A 7 ) 6A6 3
! % # " :A )
* "" * ! # 0
8 3 :A + )
* 3 . )
* + + + *
) .
) 3 .8
) )) * :A +*L )
:A. * * 5
%.
. .
3 : < = +
* 7 . / ) 0 +
* . *
+ 7 . % #
+ L * *L ) .
!
8 :A 3 :A + )
.%
% - :A: 3 ) 3 )
+ :A* + * K <K
.
%5 67 ! %$
- :A + :A *
+ L * ) . * 3 . )
+ 3 / 3 0.
7 3 3 .
) :A /,?A0 + *
= +) 7 + K * K) :A
R ) + ) ) )
) . ) :A ,?A +
#! . = ) 1 +
+ ) * 3 . = ) +
+ /' ()*+ ' (,)+ 0 )
1 . ) / +
-0 * 3 + . =
3 + L 7
+ + 3 ) 1 .
= +*
.
!$" 1
3 = ) )* K K * *
* . C 3
+ + * * * *
7 3 P + 7 .
* + = ) * 3
:A3 . ) 7 +*
)1 . = *
) 7 + ) 3 C
Q<+ *) * 7 .9 + * . .
= * / 0
+ +* * .. * 7
3 ) ) + C Q<.
) 7 = *
C ) * ) . +
= * )) ) *
)) .
) 3 P) 7 + * 7 *
* 3 P
+ = * + ' ##
)3 .
) 3 + = * ) .
+ :A .
7 * +
3 + + ) . )
! + ) ) )
+ ) . L K
K ) ) =
.
) 7 ) 7+ = *
. + ) + * )
7 ) + ) /0$ $ 1 23 3
. + *
%
= *+ ) =.
+) * =.
= + * 3 +
. * ) + ))
)1 + 1 + )) 7 + +
* * ) ., +
) * + = L ) )
* . * * ) .
) ) ) 7 ).
L 1 P . )
) + + = * ) )
) 7 3 .9 * +
) + 3 )
. ) ) +
) .
< = 7 +
* # P 7
/ + ! 0. - =
+ . ) )
+ * .
!
< 7 + * +*
3 7 + .
8 * + 7 ) 7
1 7 . 7 + )5
• - 7 ) 7
/ 7 ) P
7 (0.
• * ) +
7 * =.
) 3 ) )
3 :A + * 4$5$&6.
- + * )7 . +
) =. * $ %
# . + + * * + L
) = L * ) .
" 7 ) 3 7 . )
* +) + #% # . * *
* 3 7 .
(
7 ) 7 :A *
* .- + )
# / 0.
8 3 + 7
=. < + ) +
%5 67 ! %#
= 7 7
. ) ) + = *
7 + L ) 3 .
) 7 ) 73
) 3 ) .
6 + ) = *
7 ) / 0. )
= + *
)) ) . + 3 * .
67 ) . * )
K K) ) )
/. . * )% ) 4 K K
) ) 4 0. =* + * ) )
/ 0+ 1
.
) ) * + ) 5
• ? 3
• K K ) =
• L ) 7
67 .
. 7
7 . ) * ) 7
5
• ) ) 3
• 7 3
• , 7 3
• C 3
• ) + 7 #
• 1 ) 3 )
• * + ) * ) #
• ?7 ,,A ,?A* 3 + ) )
• 4$6 * 7 ) 3
• ) 3
• * 3
• * 3
• * + *
• 8 + ) /4$
* 7 + )*
) 3 * * . ))+ * + *
* + * 1
)) . + 5
DBCC FREEPROCCACHE
F L * 1 * * =
. 8 *
) + * 3 )
) 7 5
SELECT [cp].[refcounts]
,[cp].[usecounts]
,[cp].[objtype]
,[st].[dbid]
,[st].[objectid]
,[st].[text]
,[qp].[query_plan]
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp ;
8 3 * :A @?A 7 )
:A. F @?A 7 .
* ! +
+ * L )) *
7 . 9 * + )) *
7 .
/ 4 4 #
) )) * )) *
. .
) 7 + / *
)* 0 . ) + L
+ ) )
. + +
) ) ." )) * +
7 .
/ $ # 2'#
+ * L * . 7 +
) ) 5
CREATE TABLE TempTable

(
Id INT IDENTITY(1, 1)
,Dsc NVARCHAR(50)
);
INSERT INTO TempTable ( Dsc )

SELECT [Name]
FROM [Sales].[Store];
SELECT *
FROM TempTable;
DROP TABLE TempTable;
F * 5
Msg 208, Level 16, State 1, Line 7

Invalid object name 'TempTable'.
- 7 * )) 7
7 ) '.
%5 67 ! %
=+* * 6 67 + L
7 :A. = +
) ) ) 1 . 3
7 + 7 . )
.> ) - 7 * *
) ) .
/ ###
8 ) / 0 *
. 8 7 3 .
* + * + 7 .
8 3 7 + )) ) 3
L 3
3 ).
:A )) ) 7 / 0+
)) ) * * 7 .
•
• 7
• @?A
* ) * +
,!-L ) .
"
3 ) .! 6
- 7 * ) .
+ ) .
7 ) 5
• 40 / 5 37 555 ) * 6
7 ) 3
• 40 / 5 376$-65 ) ) *
#8 % . * 6 7
• 46 62462&4 25$9 40 / 5 37 55 7
) - 7
/+
@?A ) +
@?A) . * )@?A 5
• 40 / 5 37- 55 = 7 .
• 46 62462&47- 55 @?A) ) - 7 .
67 * )) :A +
7 :A .9 *
* + ) )
* * .< ) * 7
+ L .
+ * * 7 +
)) ) . *
)) ) :A / )) )7 0+ *
)) ) - 8 + ) *
- 8 ) .
* ) * * + L
) ))
"
) * 7+ L :A L
) ).- ) ) * ) 4 #6 # # !
* / 5QQ***. . 0.
7 * ) :A 4 + ' : .F
) ) ' : ) 5
5QQ***. 7. Q? ,!
) * * +
* 7 .
) ) .,
* )- 8 + )) +
7 )) + )) . *
* L * ) 7 .
7 * )
7. - 3 + *
+ 7 )@?A+ *
. + + 7 +
3 )O
( 2 34
7 ) ) * 3
* .< L + L +
7 : + L 4 : # *
. + L ) 3 )
* .> 5
GRANT SHOWPLAN TO [username]
* 7 ) .
* 5 4 "
) ) 6 - 7 + )
3 * ) 3 + *L ) . <
? + ) * 3 * *5
SELECT *
FROM [dbo].[DatabaseLog];
%5 67 ! (
8 L * % # 3
= + L 3 .
8 ) * = )) *
* 5
• K, 6 67 K .
• > 3 * * ) .
• : .
• >A A .
) + * +* ) $
% # + %.
" #
8 L 7 * + ) + L - 7 .
- 7 + 6 7 + )
=. 7 * * * 3 *
7 . * * ) * )) +
7 .
- + * ) - 67 5
• K - 67 .K
• > * 3 * * K - 67 K
.
• : .
• ?.
6 ) ) K K * 7 * )
3 ) 3 * * )) .
+ 7 ) 7 3 .F
7 .
"
- 6 .
'
, " "
% ) * ) 7 $
7 .<
) 6A6 + L 3 )
. L ) ) ) .
# ;. ) + ) + )
* * ) .
C + 7 ) ) .F L
* * * . *
* + . + +*
/ 0.
) * ) ) + *.
* ) .F *
* * * ) * .
7 + ) 3 * *+ 7 * *
* + L .
! * .
3 ) . + ) = +
7 ) . + * .
8 S % S+ + +
+ S * * .
- ) 3 * ) K /
0K )% S. E 3 + ) *
3 + 3 * 7
) . * * )) ) * .
6 #6
6 ) * + * + * * 6 #6 +
* .
6 7 + 4$5$&6 +
* * * (.
" $
9 * = ) * 5
( - # * ) * + *
*+ + + *L /0$ $
*L 7/ 7 )
3 ) 0+ ) *
.- + ) * *+
7 .
%5 67 ! 4
• & # P * 3 *
. ) *
) L *
.
• $ & "*L
%.
• $ 4 & P =
+ ) ).
* + 3
= ) 3 R
) = * .
• $ : P
= ) 7 3 .
! * ) + * 3 *L
. )* ) ) * )
'.
" %
6 ) )) * ) . '
) * ) )) (+ * )) )
. + A < .
) = L ) * * 3 7 .
* .
+ * P .
-) +* ) Q<+ C+ < .
) 7 * )+* )
+ .-
7 .
Q< C +
: < = . ) *
* ) Q< / 0+ )* L C.
- .- +
+ *
.
&
F L + + +
. 7 +* + L
) .F
) ) ) ) 3 + 4 #
.
- * ) *. )
* )) ) . 8 *
7 + L ) * )
* * .D * * *
) * 3 ) *
7 .
- ) ) + * )
+ ! ) . *
* * . + ) 7 +
<$ 4$5$&6 + 3
+ ) .D * *
** 7
.
+3 2< +* + ) )+
) + )
).
- * L * 3
3 .F L * + *
+ * ) * + .8 L
) 7 ) C
7 + 7 + .
? ) .> *
7 K K )
) . 4 * ) .
%5 67 ! $
" &
? ) ) ) + ) *. ) +
< =# ) .
) 3 + +
) 3 + ) )
+ ) ) ) .
C < =# +* )
) $ & . -) * 5
• $ : + * )
2 (+ .
• 2 % * 6 * 3 )
7 * 3 . :A ) 3
* * 3 7 .:
'.
• 3 $% 0 7+
7 *.
! 7 > + 1 3 .
5 ) ) .F L ) *
+ /* C
0.
* 5 4
7 ) L .9 * +
) .
) * *+
* *. 8 L ) * * ) ;
) 3 * 7 )
+ * L ) )
) + ; 8 + . *
7 5 7 67 @?A67 .
? ) 7 67 + *L
.
7 ) 6 7 7 + ) *
) 3 5
SET SHOWPLAN_ALL ON;
L +* 9<8 A-"I-AA <"+ 7 )

) 3 :A +
7 . 9 + * . L
40 / 5 37 55 ) ) 3 . )
) + & $ 6$+ < 6$ <$5$6$ *
40 / 5 37 55 + ' ( 7 +
7 7 +) 7 +* .
40 / 5 37 55 ))+ 5
SET SHOWPLAN_ALL OFF;
8 3 ) 40 / 5 376$-6. 7 *
) * #8 % + *
* 7 + +
40 / 5 37 55 ) .
8 ) 40 / 5 37 55 .
7 ) - 7 + 5
SET STATISTICS PROFILE ON
- 5
SET STATISTICS PROFILE OFF
, "
8 L * 3 * * + 7
) * 5
GO
SELECT *
%5 67 ! #
GO
SET SHOWPLAN_ALL OFF;
GO
8 7 3 + * .9 )
) 5
" )
* 7 . 7
. ) * 4$5$&6
* . * ) * * 3
. *.. .
- * * 7 7 + + L 3 =
. L
3 + * * K )K .F
* + ) )
/G0 .
) + *
* * ) .? ) ) L
+ . +* 3 2 *
+ ) ) + * 3 )
) . 40 / 5 37 55 * * " .
- L ) + 6 #4 6 & +
$ : . ) + , ) A
/ 0+ *
1 .
* 5 4 /+
@?A * * ) 7 :A
4. )) ) .
/+
@?A ) 6 7 + 5
SET SHOWPLAN_XML ON
…
SET SHOWPLAN_XML OFF
- ) 40 / 5 37 55+ 40 / 5 37- 5
7 :A ) *+ 7
) ) + ) ) @?A .- + L
9<8 A-"I@?A )) ) ) +
3 :A 7 .
@?A ) - + 5
(
SET STATISTICS XML ON

…
SET STATISTICS XML OFF
, " /+
< + L 7 * * 7 .
GO
SET SHOWPLAN_XML ON;
GO
SELECT *
SET SHOWPLAN_XML OFF;
GO
+ ) + * ) $5
" *
@?A) 5
\Microsoft SQL Server\90\Tools\Binn\schemas\sqlserver\2003\03\showplan\showplanxml.xsd
7 @?A ) * * **
:A ? / ? 0. F * 7+ )
/ ) 0. F ) ) ? G # 6
.
- ) ) ) * @?A P )* * * L
* 7 + * + 7 .
" + * ) ) @?A) .
+ ) 3 + . L
* . ) 5
http://schemas.microsoft.com/sqlserver/2004/07/showplan/.
A ) 4 + 4 . 7 +*L
! + . " 7+
7 *L * )+* 3 3 )
4 4 # .8 +* ) ) )+
) ? # 5
<StmtSimple StatementText="SELECT *
FROM

[dbo].[DatabaseLog];
" StatementId="1" StatementCompId="1"
%5 67 ! (%
StatementType="SELECT" StatementSubTreeCost="0.108154" StatementEstRows="389"

StatementOptmLevel="TRIVIAL">
<StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true"
CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false"
ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false" />
<QueryPlan CachedPlanSize="9">
) * )
40 / 5 37 55. " = 7 + *
7 . ) & # 4 * )+ )
7 + 3 7 + 5 '
.
-) +* # +* ) *L ) * +
+ .
<RelOp NodeId="0" PhysicalOp="Table Scan" LogicalOp="Table Scan"

EstimateRows="389" EstimateIO="0.107569" EstimateCPU="0.0005849"
AvgRowSize="8569" EstimatedTotalSubtreeCost="0.108154" Parallel="0"
EstimateRebinds="0" EstimateRewinds="0">
" ) 7 + L
7 /
)* 0. 7 + +
, ) A + )) + 5
* )& # + )
5
<OutputList>
<ColumnReference Database="[AdventureWorks]" Schema="[dbo]"
Table="[DatabaseLog]" Column="DatabaseLogID" />
Table="[DatabaseLog]" Column="PostTime" />
Table="[DatabaseLog]" Column="DatabaseUser" />
Table="[DatabaseLog]" Column="Event" />
Table="[DatabaseLog]" Column="Schema" />
Table="[DatabaseLog]" Column="Object" />
Table="[DatabaseLog]" Column="TSQL" />
Table="[DatabaseLog]" Column="XmlEvent" />
</OutputList>
@?A +
3 .
! + ) # ) * 5
<TableScan Ordered="0" ForcedIndex="0" NoExpandHint="0">
* ) ) ) 5
(
<DefinedValues>
<DefinedValue>
Table="[DatabaseLog]" Column="DatabaseLogID" />
</DefinedValue>
<DefinedValue>
…<output cropped>……..
/+ "
F 7 * *
K - .K F ) KT.TK *
) ) * 7 K. 3 .K * ! <
@?A 7 . ) +* * *
! # % # ) . ) ) .
7 + @?A ) + )
*/ 0 ) .
< ) ) )7 @?A )
* . 7 + @?A ) * 3
,!- ) * * 3 .< )
@?A + ? *
7 .
@?A @?A+ )
@?A * *. ) @?A ) * *
* * *. - *
+ ) + .
" '
, * 7 ) :A +
) 3 .F * 7 +
3 3 + .
9 * + ) +
)) .- 1 ) 7
3 + * .
* * * )
. :A 4 ) @?A 7 +
3 7 .F 7 + ) 3
* + ) + ) 7 +
L .
:A 4 ) * ) * +
7 ) :A + * :A . )
+ C ) + )
:A/ C0 ) .
* ) .'
' ) ) ) ) + )
) ! < / 5QQ . ). Q Q Q %$($4$. 70.
%5 67 ! ((
* 7 ) *5
• 4 : # 6 %5 * ) 7 ) 3 *
) 40 / 5 376$-6 :A .
* 7* * :A 4 + * )
) * @?A. 8 L
) 7 7 + ) ) .
• 4 : # 6%( +5 + * )
) . ) ) .
• 4 : # ##5 ) 3 7 *
) 7 40 / 5 37 55 :A .
* 7+ ) ) .
• 4 : # ## ? & #5
* - + ) * 3 .
) ) .
• 4 : # 4 #5 7
* :A 46 62462&4 25$.
) 7 + )
46 62462&4 - 5 :A 4 : # - 54 #
:A ) . 4 : # 4 # )
.
• 4 : # - 55 ) * 7 ) 3
7 * 40 / 5 37- 5.
• 4 : # - 5 ? & #5 A * @?A + )
) 3 .
• 4 5 * @?A : +
7 ) ) 3 * .
@?A ) + ) * . )
) + + ) .
• 4 : # - 54 #5 * 7
) 3 + .
) 7 + * @?A * @?A
) + = . *
.6 ) )3 * @?A ) : *
) . C * )
.
" 4" /+
:A 4 ) * @?A @?A 7
3 = 7 3 . ) + *
7 + ) + * 4.
* K6 K K * K 7.
* @?A * ) + /U0
7 . * @?A .
8 * @?A ) ) +
) ) * + 5
4 ! ) + -+ ) F -,? "
) P * -A 6> >- 6 .
('
• > 5
• :A5!
• :A5!
"
7 ) @?A 7.
7 + * 1 ) ) .
< * @?A + ) @?A +
$' $% 4 !. < + @?A L
) +) ." ) ) +
* @?A * ) )) + 3
7 .
" +
K> K .8 +
* % .
%5 67 ! (4
" #
" * @?A .C 6 %< +

@?A .8 L ) +
) . * *+
7 +* * ) = 7 . +
)) + * @?A ) * @?A +
7 .
- + ) * @?A
) . * @?A * + K67
6 , .K
" ##
7* ) ) @?A
* . ) @?A * @?A 7 +
7 . :A .! 7 +* ) )
(&
* 8 * 67 + @?A * ? ) )
7 .
8 6 7 - 7 +
* :A 3
* ** ? . ))
)3 +) + .
!
*L * = *
3 . 7 7
7 .F * ) ) )
+ + 7+ @?A. 6 @?A
* + L *
* L * .
5> 67 ) ! : ($
( 6. ( ,% ( , / ' ,$% % $(
& , ' (,
) 7 +
* + 7 ) 4$5$&6+ < 6$+ 234$ 6 <$5$6$ 3 +
* )* 1 ) . +*L
) * 7 5
• P + * L
• 1 P* L * 1 *
• /0$ $ P ) )) 7
• !! ! P * 7
• 2 + < # 7
"
* + * 7 *
+ 7 + )* / 0*
.6 ) * 7 .8 *
L L L L .
+* * * /4 # 6 #4 0. 9 * +
)$# . ) +* L = $# )
) * 7 .? 3 )
+ * ) . )
+ ) ) ! < 5
http://msdn2.microsoft.com/en-us/library/ms175913.aspx
) 7 5
• 5 ! # # + +
3 7 , ? A /,?A0 .
• ### #
. + ) +
)) )7
.
• & * :A
• 5 ! ! # :A +
- +, + )+ /> 0+ 8 + .
*L ) +
.! < +
* + * * ) K K . +* * )
. <) +* *
) ,!- ,!-+ ) * * *
+ ) ) + )
5
4 # ( #+ 4 &# &# 3 . #
2 %4 2 %4 2 %4
(
3 . # 6 #4 2< 5 @ 5 0
2 %4
3 5 ! 1 6 & & 4
4 #
# A= $ ! 4 #
-
, > !
. * * *
73 .
< * . P +
( !! ! + > P 3 )
7 .! ) + 3 * ) * )
7 + )) ) .?
)* * + .-
.-
.- +
) .
#
A L + 3 .
) * / )) O
03 8&
- 8 5
SELECT *
FROM Person.Contact
* 7 5
5> 67 ) ! : (#
" #
8 7 ) 3 . )
7 +
* *+ * 7 * @7& 7& 2<
) * * %##$ .
7 :A ! / ) 0. -
7 + 7+
+* * 7 .
- + 7 .
7+ ) + + * *+ )
3 .
- 7 ) + +* 77 =
* 3
7 7.
- 3 ) 7 7 *
* . ) ) *
7 + L ) /0$ $ )
3 * .>
* * :A ) .
, 5
8 3 )) /0$ $ 5
'
SELECT *
FROM Person.Contact
* * ) 5
"
7 )) ) +* * *
) * . - 7 + + * =
7 3 . ) +
) 7. 7 :A
! / ) 0. - # % 1
+ 7+ +*
* 7 .
8 7 + 3 ) *+
*+ ) . * 7 )
. ) 7 +
7 7 7 + 7
3 7.
7 +* &# 2 %4
8& + ) @7& 7& 2+ *
7) .
5> 67 ) ! : '%
" * *) 7
* + * =.
% , 5
A L )) 3 8& R
75
SELECT ContactID
FROM Person.Contact
WHERE EmailAddress LIKE 'sab%'
8 7 ." * ) (
7+ 2-7& 7$ # .
3 6$5 7 7
7 *. - + * ? ) ) * )7
." + ) * ).
'
" $
A 7 + 7 7 *
.C 7 + 7
7 ) ., 3 7+ 3
= ) 7+
7+ )
Q< 3 ) 7 P 7 .
)! 5'"
A L 3 ) 1 )*
5
SELECT ContactID,
LastName,
5> 67 ) ! : '(
Phone
FROM Person.Contact
F * ) '5
" %
+* O> )
) + ) * 7
2-7& 7$ # 7. 3 + 7 +
) 3 + , .- 7 7
) 3 +) 3 =
7+ 7
.
8 * *) - ) 7 + ) 4+
* * $ # & 2< .
''
" &
@ 5 @7& 7& 2<
7 ) *+* 5 3
+ * ) &.
5> 67 ) ! : '4
" )
-D A & * 7.
- D A = *
+ / * ,0 *
) 7/ ) )0.
) D A 3 ) ) )
) 7. ! 7 )
3 + ) * )
7+ D A
.
-D A C * " A 1
) * .
& + * * 7 +*
A C ) .
'&
" *
+ " A 1 )1 )
) . + D A 3 + "
A 1 * ) 7 D A . ) D
A * / 7* 0+ " A
* * 7 .
) )7 * %.
3 * * + * ) .
F 7 ) * 3 5
SELECT *
FROM [dbo].[DatabaseLog]
5> 67 ) ! : '$
"
- ) + L ) ) 7
+ 3 = * )
* .- * * * )
+ 7 .8 / 1 0 ) * )
+ * 7 7 + ) ) ) 3
= * * 7. -
+ 3 = ) *
7 *. * )* * .
- ) * +
.< + ) * +
* * * 3 )* *+
7 ) .
(, 5'"
'
)* ) ) ) < 5 !3
+ * )) ) 7 2<
5 .
SELECT *
FROM [dbo].[DatabaseLog]
WHERE DatabaseLogID = 1
" +
) 3 + 3 = ) ) 7
.8 7 ) ) * /0$ $
+ 3 7. 9 * * * ;
5> 67 ) ! : '#
" #
) ) 7 +* K! % K < .
K! % K 7 ) 3
.
" 7+ 3 = ) >,A C +* )
/ L 70+ * )
) * . * + L 7/
* 0+ * ) 7 .
Q< * )) ) )
+* * " A .
4
" ##
) >,A + K! % K +
. / ) >,
A 0* ) 3 . + *
+* L ) ) .! ) >,
A *+ 3 *
) Q< P * 3 +
7+ 7.
# 7
C *+ * * * .A L 1
3 . ) * 3 ) +
3 5 3 )
.
SELECT e.[Title],
a.[City],
c.[LastName] + ', ' + c.[FirstName] AS EmployeeName
5> 67 ) ! : 4%
FROM [HumanResources].[Employee] e
JOIN [HumanResources].[EmployeeAddress] ed ON e.[EmployeeID] = ed.[EmployeeID]
JOIN [Person].[Address] a ON [ed].[AddressID] = [a].[AddressID]
JOIN [Person].[Contact] c ON e.[ContactID] = c.[ContactID];
7 ) 3 * ) % .
" #
8 3 + *
. 7 ) ).
* +* )
+ 5
%. 7 8 /'4S0
. 9 ? 1 * 0 8$ #
8 / S0
(. 7 8& /% S0
A L ) * .
) % + ) * 7
0 8$ # + * 7
8 . * 7 +
L ) .! + * %(+ *
* 7 2-7 7 5 7 5 ,7& 7.
4 ' 27 #& * %#+&%' *
* .
4
" #$
3 = 2 & + *
. = + ) 7
+ * * * 7. 8
%#+&%' * '4S ) 3 )
.% '%(. 3 = ) 7
) +* 3 =
) ) . * + ))
.
+ 8
7 9
* 7 + ) * 7
0 > + 7 ) 7 .
) * %'5
5> 67 ) ! : 4(
" #%
!) * * 9 ? 1 + * * *
5 ! #. 9 3 *
) ) 3 . 7 +
* ) 3
) *. * * ) .A
+ .9 )
* :A ) )) * * +
+ )) .
- + + ) 3
= + + *3 . )
* . 7 + *) +
+ .
" * * + 0 1 * :A 1 *
* ) ) * 1 +
+ *
+ ) * * 1 .! )
+ = +
) + 3 .-
+ 3 .< + )
+ 9 ? 1 ))
)1 .
7 + ) 0 8$ # 8 2
* 8 .
9 ? 1 ) )) * + ) )
.9 ? 1 * * )
1 + )) * 7 .
< + 9 ? 1 )) 1
/" A ? 0 . 7 + 9 ? 1
7 5
4'
• 7
• /0$ $
• /0$ $ * /
+K K L 0.
* L 7 7.
8 9 ? 1 )) * ) 3 = 1 *
+ )) * 1 * +
7 1 + )
8 96>6 + 8 96>6 . * +
9 ? 1 ) ) 1
. ) + . ) + +
9 ? 1 * ) 1 .
8 7 * ) *
+ . %& / L . %& * 0+
) *+ # . - )) * * +
) .-
)) )) 6 - .
3 ) * 7 0 .
8$ # 9 ? * ) ) 9 ?
7 .
, 5
-) 9 ? E +* &# 2 %4
8& + ) @7& 7& 2+ *
7) . 7
. * %4.
" #&
5> 67 ) ! : 44
" ) 4 ) %4 + * 1
* & 2 0 8$ #
8& .
% "7
* 7 + 1
* ) + 3 5 1 + * %&.
" #)
# > . )
* ) 1 /
7 0 ) * . ) * )
* * + )) .-
+ + 7 + 7 ))
1 .C + )1
* 7 .
"
+ 7 * ) % + ) +*
. ) * %#.
4&
" #+
) + ) +
) P + $ # 3 *
& 85 3 & 8 3 * * .
8 * = + . + L 7 ) 3
) ) .
+ 7
! 9 " A E + 3 = ) !
1 . 7 ) ? E + * ) *
- 8 5
SELECT c.CustomerID
FROM Sales.SalesOrderDetail od
JOIN Sales.SalesOrderHeader oh
ON od.SalesOrderID = oh.SalesOrderID
JOIN Sales.Customer c
ON oh.CustomerID = c.CustomerID
3 7 * ) %$.
" #*
- 7 + 3 = ) 7
& 7 4 # 0 .
5> 67 ) ! : 4$
/0$ $ * ) 3 + * )
* .
" 7+ * ) & 4 # 0 1
! 1 . - ? E * 1
. 7 + *
*) ? E + * ) % +*
1 4 # & 2<. + 1
.-? E )) * 1 * +*
1 ) 1 + 3 =
) 0 1 ) + ) ? E + 0
) )) 9 E . 3 =
7 .
! AB
< ? E 1 * ) + 1 ) *
9 ? E + * * . - ) + 1 *
.
) ) ? E 1 . )
+ 3 = ) ) ?
E + ? E * 1 +
)) 7 .
4
* (
< )3 * 3 * )
5R * + /0$ $ .8 L * +
3 7 .
> ) * 3 - 8 + 7 .
3 * * ) . / + *
/0$ $ .
SELECT e.[Title],
a.[City],
c.[LastName] + ',' + c.[FirstName] AS EmployeeName
JOIN [HumanResources].[EmployeeAddress] ed ON e.[EmployeeID] =
ed.[EmployeeID]
JOIN [Person].[Address] a ON [ed].[AddressID] = [a].[AddressID]
JOIN [Person].[Contact] c ON e.[ContactID] = c.[ContactID]
WHERE e.[Title] = 'Production Technician - WC20' ;
* 7 ) 3 5
"
) +* = ) /0$ $
7 + . /0$ $
) * +* *
) &# 2 %4 / ) %0.
5> 67 ) ! : 4#
" #
=+ +* ) + *
* .
8 * 7 8& +
3 + = * )) 3 5 1 .
= * * 1 +
7 1 . L * ) +
7 * 1 )
0 8$ # . ) 7
) .- )) 1 *
* /0$ $ + 3 *
/0$ $ .
3 + * ) * :A *
K K* * + *
1 * + * /0$ $ . *
3 * ) + 7 + *
) * ) . ) ) = ) 3 +
* * /0$ $ + * .%% ' 4. .' 4 )
&
3 . L ) ) + 3 .E *
* 3 .
4 ($' &: $( ( &:

8 3 + ))
7 .
* <$ 7 5
SELECT *
FROM [Production].[ProductInventory]
ORDER BY [Shelf]
7 * ) .
"
&# 2 %4 4 . )
7 + ) * . *
* 3 = * 7 . ) <$
) + ) + * ) )
4 / ) ( *0.
5> 67 ) ! : &%
" $
) * *) / ) '0+ L
% &# *. % &# * )
7 + + % &# * .
" %
$&S ) ) 3 .
7 + * 3 7 .
&
- ) + * * 4 S ) 3 L
7 + ) * = .
* * ) ) * 5* /0$ $
.? + 3 * .
9 * + ) /0$ $ 7 + )
* 3 ) * + * * .
< 5
• ; ) + .
• L ; 7 +
7 ; * +
) + * ) 7.
• ) 7 + * 3 ) +
) * )* ) 3 .
)* 3 ) * 5
SELECT *
FROM [Production].[ProductInventory]
ORDER BY [ProductID]
8 7 * ) 45
" &
- 3 3 + <$
+* L 7 . *
+ * 7 +*
+ )
7. 3 = =
+ . )
) + :A 4 ) ) 8
. ) + :A 4 )
) . >-? ) .! )
+ :A + +
* . 8 + :A
8 +* ) . ) )
) + 8 + >-?
+ .
+ 8 9
6 +* 9 ? ) 1 . 9
? * * 3 . A L
3 & 36 5
5> 67 ) ! : &(
SELECT [City],
COUNT([City]) AS CityCount
FROM [Person].[Address]
GROUP BY [City]
7 * *.
" )
3 7 * 7 + ) * )
3 . /0$ $ ) *. *
) 3 & 36 . ) 3
= *) + ) 9 ? .
" 9 ? 7 * K K
* . ) 9 ? ) 1 .-
* 9 ? * 1 + 9 ? * :A
) *
+* K .K < +
.
: ) + * 3 7 .- *
K K ) )
/0$ $ ) * +
) .
)* 0 =23 3 + 7
7
SELECT [City],
COUNT([City]) AS CityCount
GROUP BY [City]
HAVING COUNT([City]) > 1
7 * * ) $5
&'
" *
! 0 =23 + 7 .8
# ) +& +
%. < ) ) * * )
0 =23 ) .8
) * 0 4$4
# L (' .
"
8 0 =23 ) +
3 + 0 =23
) . ) .- * 7 +
) * ) ) 3 * + *
/0$ $ 3 ) *
.
( # ( 4 "
8 7 ) + + *
5
• - > 6 >
• - >* 6 >*
? ) + ) * = +
) 7 + +* * *
* .
* + * . 8
+ <> 7 +
.
5> 67 ) ! : &4
• + = 3 .
2 (+ . ) +
.
• + / 0 * ) .
3 % (+ ., ) +
+ 3 % (+ .
• + ) ) + )
) * . &# (+ .-
&# (+ .
- * ) ) 2 (+
.- * ) 2 (+ +
)) .
- * ) ) 1
. - * * )
.8 )
+ * + .
) + * 7 ) )
* ) .! L.8
* *
+ * . 7 + )
) ) * 7 + * 5
• " 7
• > :
• >*
•
•
• B
) ) * + * * *
4 $% ) 6 $+ *
* 3 = 3 . ? )
* .
• -
•
- ) + . + )
* * = . = = *
+1 * .- +
) . 7 * ) = ) * .
+ * * ) * )
* * ;
) * 3 * 3 = +
2 (+ * "< )
1 . ) <" ) 1 +
) * * 3 ) *
) 1
* ) ,!-; + ) *
* + Q<. ) +
* +
) . ) + * 3 + )
&&
7 + )) 3 )* * + Q<
) .
, ;'"
67 ) 3 )
) * 3 L .8 7
) 4$5$&6 3 + * * 7 )
234$ 6+ < 6$+ <$5$6$ 3 .
,
9 234$ 6 5
INSERT INTO [AdventureWorks].[Person].[Address]

(
[AddressLine1],
[AddressLine2],
[City],
[StateProvinceID],
[PostalCode],
[rowguid],
[ModifiedDate]
)
VALUES (
'1313 Mockingbird Lane',
'Basement',
'Springfield',
'79',
'02134',
NEWID(),
GETDATE()
) ;
/ L ))
* 0+ * #.
" +
7 ))+ )+ * * 5
& 4 . ) * 3 .
+ L * ) 7 *
. ) ) & 4 # ) ! .
* 3 * )
) *. " ) * +* 7 * +*
) + ) .
- * ) ) )
* + )
$6< 6$ ) .- ) &# 2 %2 +*
1 ) ) = ." ) " 6>
+ 8 84 ' 2. 7 +
3 5 1 + * ) &# 2 %4
84 ' . * +* " 6> )
5> 67 ) ! : &$
) ) )4 '
2. 1 *
7 * 7 + .- )
7 . )67 % %' 3 = .<+ * +
* 8 84 ' 2
) ) 84 ' R * )
.
'"
) * 5
UPDATE [Person].[Address]
SET [City] = 'Munro',
[ModifiedDate] = GETDATE()
WHERE [City] = 'Monroe' ;
7 * *5
" $
A L 7 +) ). )
7 +* ) * ) 7+
+ * . )) )
7 ) . )
) * /0$ $ C& DE +
7 .
7 6 . < 6$ 7 + ) *
+ ) . + ) 6 *
< 6$ 3 .
3 5 ) 6 ) 4$5$&6 + < 6$ +
) + + ) * +
6 4$5$&6 .
7 $ ! 4 #/ ) ) 0.
) *
1 .A 7 + )
* / ) " A 7 0
3 + )
/* 7 + * *
7 0. 3 + * * 3 .
7 +* * ) .
+ 7 +
$6< 6$(+ ) 3 .
" ** ) < 6$ + 7C .
+ ) 7. )
* + .
- ) +* :AA 6 +*
< 6$ .
&
) + ) * ) * *
. 7 + 7 * ) +*
)) . + 7 * ) )
) + ) * Q< ) 3
< 6$.
8 ) 7 * <$5$6$ ; 7 + L
) * 7 .
DELETE FROM [Person].[Address]

WHERE [AddressID] = 52;
(% * 7 5
" $#
* )) . 1 * * *
* .> + *+ *+
3 .- ) * *
* + ) ) ))
. + 4$5$&6 <$5$6$
.
+ +* 7,
. ) . )
) *. )
&# 2 %4 6 < # * 5
Prefix: [AdventureWorks].[Person].[Address].AddressID = Scalar
Operator(CONVERT_IMPLICIT(int,[@1],0)).
+ F A+ * 2. ) L
+ * L + + 4 . 8
) ; ) 3 3
+ ) = .
5> 67 ) ! : &#
" $
-) + ) 7 7
)" A E . ) A) E .
) 1 * * 3 )
1 .6 . + + -
+ ) E + ) *
*L + ) ) 7 . ) +
. ) + * +
<$5$6$ .
" $$
$
!
1 * 7 .
9 * + * ) + * )
) * 3 . ) =
3 7 1 + L 7
= .A * = 7
)) . ! 7 + * )
=+ ) 7 )7 .
(5 7 @?A67 ) ! : $%
(<. / % /+ / ' ,$% % $( & ,

' (,
) * 7 + +
* 7 @?A 7 .8 7
@?A 7 L + .
* 7 + * 7 @?A 7
.
) :A + 7 7 *
) + * 7 .? )
7 + )) 7 7 . )
7 7 L
,!- * ) * + 7 7 :A
* * .
7 7 $+ ? ) @?A :A 4.
A 7 + @?A )) = ) *
@?A . * ? ) 7 7 *
@?A ) ) )) ; ) .
6 + @?A ) ) + 7
7 . @?A
7 ) . + @?A
) * . 7 + @?A
) * ,!-+ ? = . 7
+ + L )) ) ) .
* 7 7 ) )
; L 3 * . ) * * :A 4
) + ) )) 7 +
) ) @?A ) ) .< + )
) :A + + +*
* 7 ) :A
+ * * ).
" !
A L 7 7 ) 3 * * . +
+* L 5
SELECT ContactID,
LastName,
Phone
FROM Person.Contact
$ :A 4 )) 7 7 + ) * .
$
" #
" *+ * L 3 7 .> 40 / 5 37 55
* * 7 . " :A )
7 + 40 / 5 37 55 )) 5
SET SHOWPLAN_ALL ON ;
GO
SELECT ContactID,
LastName,
Phone
FROM Person.Contact
GO
SET SHOWPLAN_ALL OFF ;

GO
) + * ) 5
"
> *% + 4 6% ) * 7)
:A * 7 . ) + L )
6 +* .
" $
* ) :A * 7 /6A6 +
0. * 5 37 /. 5 37 / +
4 6% ) .
-3 4 6% ) *
5 " A 1 + 7 + 7 .
C ) + L K ) K
7 . )* ) + *
) ) /G0 + .8
) 3 * * ) ? + > G> 7+
* )) * ) 7 7 .
(5 7 @?A67 ) ! : $(
) 3 2< +* , )
+ .8 + ) *
+ 7 ) . 7 + 7
) 7 .
? * *+ * * ( /" (0 * 7
.! 7 4 6% / 7 ! 0*
7 * 8& 5
OBJECT:([AdventureWorks].[Person].[Contact].[IX_Contact_EmailAddress]),
SEEK:([AdventureWorks].[Person].[Contact].[EmailAddress] >= N'sab' AND
[AdventureWorks].[Person].[Contact].[EmailAddress] < N'saC'),
WHERE:([AdventureWorks].[Person].[Contact].[EmailAddress] like N'sab%') ORDERED
FORWARD
< =# ) 5 37 / )
. *
3 / 6A6 8 96>6 0+ 5
[AdventureWorks].[Person].[Contact].[ContactID],
[AdventureWorks].[Person].[Contact].[EmailAddress]
< / 0 3
3 . ) , ) B )
3 = 7 .
+ ) $ : (+ 7
%#. *.
" 7 * ' /" , 40+ * 7
8& . * * * * 7
) . + * * +
+ ) + .8 * )
4 6% ) 5
|--Clustered Index
Seek(OBJECT:([AdventureWorks].[Person].[Contact].[PK_Contact_ContactID]),
SEEK:([AdventureWorks].[Person].[Contact].[ContactID]=[AdventureWorks].[Person].[
Contact].[ContactID]) LOOKUP ORDERED FORWARD)
" +) $ : + % *.
9 * + ) 7 ) +$ $% + L
%#. * 3 .
$ $% 7
. ) ) 3 7 ) D
A C < + ) 7 + * )
) * . % . * .- )
+ * = .
8 " * /" , 0* * " A
1 ) * .
< =# " + * +
5 * & 2<+ 5 3 3 3 .
) + $ :+ $ 2 +
6 #4 6 & + ) ) )
+ * * L .
! " !
7 ) 3 * ) * .9 * +*
73 + 3 7 .A L 6 7
) ) * 3 + )1 /0$ $ 5
$'
SET SHOWPLAN_ALL ON ;
GO
SELECT c.[LastName],
a.[City],
cu.[AccountNumber],
st.[Name] AS TerritoryName
FROM [Person].[Contact] c
JOIN [Sales].[Individual] i ON c.[ContactID] = i.[ContactID]
JOIN [Sales].[CustomerAddress] ca ON i.[CustomerID] =
ca.[CustomerID]
JOIN Person.Address a ON [ca].[AddressID] = [a].[AddressID]
JOIN [Sales].Customer cu ON cu.[CustomerID] = i.[CustomerID]
JOIN [Sales].[SalesTerritory] st ON [cu].[TerritoryID]
= [st].[TerritoryID]
WHERE st.[Name] = 'Northeast'
AND a.[StateProvinceID] = 55 ;
GO
SET SHOWPLAN_ALL OFF ;

GO
8 7 3 + * . ' *
7) ) .
" %
* ) + ) /G0
+ ). ) +*
7 2-7 74 ' 2 .
" &
* /0$ $ ) *
.
--Index Seek(
OBJECT:([AdventureWorks].[Person].[Address].[IX_Address_StateProvinceID] AS [a]),
SEEK:([a].[StateProvinceID]=(55)) ORDERED FORWARD)
(5 7 @?A67 ) ! : $4
) 2+ ) 4$5$&6 +
) ) *. 3 *
) * 3 .
7 ) * 7
7+ 2 ) 7 .- + 4 6% +
* 7 .
--Clustered Index Seek
(OBJECT:([AdventureWorks].[Person].[Address].[PK_Address_AddressID] AS [a]),
SEEK:([a].[AddressID]=[AdventureWorks].[Person].[Address].[AddressID] as
[a].[AddressID]) LOOKUP ORDERED FORWARD)
+ ) * 1 1
/ * 0.
" )
* * ) *+ * * %%+ * )
3 + 7 & 7+
@7& 7 :!
--Index Scan(
OBJECT:([AdventureWorks].[Sales].[CustomerAddress].[AK_CustomerAddress_rowguid]
AS [ca]))
%#+ U * ) 7 + 9 ?
1 * & .
" *
* * ) ? +*
/ * % 0. )* ) +* )
7 7& 7& 2.
+* ) ) * = .
L * " A .
$&
"
+* ) 3 +
) .
" +
* * + /G0 + 7
7 @72 ' #7& 2.
+ ) "
A + ) * * + ) * 7 L
) > *' > * %4+ * 7 8&
.
" #
+ > * (+ * ) " A 1
*' 7 * %4 " A
1 .
(5 7 @?A67 ) ! : $$
" ##
* * > * %&+ * ) 7
4 # 84 # 6 . ) + ) )
" A 1 3 +* ) * )
.
- ) )+ 7 7 + *
) ) 3 . A 3
+ = ) .8 L
* * 7 7 + * )
7 + * 7 7 *
.
/+
L ) ,!- * ) * 7
) .9 * + * :A +* *
K) ) K) 7 + 7 *
.
* :A 4+ * ) @?A .
+ @?A ) ) *
7 + * ,!- .
* )* * ) 7 @?A
) .9 + * * * .
+ @?A *
7 . 7 + @?A
=+ ) / *
3 0+ * = +
7 ) . + ,!- * L ) +
* ) + , 6
8= ) 7 .
+ @?A ) +
) 7 ,!- * )+
) 3 + ) + K 7 K.
@?A + $+ *
3 = ) 7
3 .
) * +* ) )@?A .
$
/+
+ L 7 . + L
7 * * 7 .
8 40 / 5 37- 5
7 @?A ) / ) * *
7 0. 8 7 3
40 / 5 37- 5 3 * * 7 .
SET SHOWPLAN_XML ON ;
GO
a.[City],
cu.[AccountNumber],
ca.[CustomerID]
JOIN [Sales].[SalesTerritory] st ON [cu].[TerritoryID] =
[st].[TerritoryID]
GO
SET SHOWPLAN_XML OFF ;
@?A + * * 5
(5 7 @?A67 ) ! : $#
" #
) + % * )
@?A ) . @?A ))
7 +* 7 + KUK KK
* ) + ) 3
.
- * ) ) )
5 5QQ . ). Q3 Q 'Q $Q * Q. )
) ) * @?A * *
.#
-) ) 4 + +4 4 4 # /
%0+ ) ) ) ? # 5
<QueryPlan CachedPlanSize="52" CompileTime="29293" CompileCPU="6277"
CompileMemory="520">
= ) + * ) + C
.
" 7 7 + * !2 % .
) 7 7
= .8 ) 7
)+ ) 7 + , 6
8= + * ) + )
) 7 .
# - %+ * @?A ) )
* @?A+ * @?A ) : + * @?A ) .
F * @?A ) * . I 7 I3 I .
<MissingIndexes>
<MissingIndexGroup Impact="30.8535">
<MissingIndex Database="[AdventureWorks]" Schema="[Sales]"
Table="[CustomerAddress]">
<ColumnGroup Usage="EQUALITY">
<Column Name="[AddressID]" ColumnId="2" />
</ColumnGroup>
<ColumnGroup Usage="INCLUDE">
<Column Name="[CustomerID]" ColumnId="1" />
</ColumnGroup>
</MissingIndex>
</MissingIndexGroup>
</MissingIndexes>
7 + > < +
) + =. ) +*
3 2 E*+ ) ) 3 5 5
<RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Inner Join"

EstimateRows="1.94953" EstimateIO="0" EstimateCPU="6.37415e-005" AvgRowSize="119"
EstimatedTotalSubtreeCost="0.376226" Parallel="0" EstimateRebinds="0"
EstimateRewinds="0">
) * ) ) * *)
. " + ) 7 + * 1
$ $% + @?A ) * .
) )* * 3 + *
* 7 .
7 + ) " V &+ ) 7 + * "
A 1 " V +* 5
<RelOp NodeId="26" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index

Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581"
EstimateRebinds="12.7784" EstimateRewinds="1.47074">
8 7 ) 3 +* * K6 67 V%4. '#%&K.
> " + 7 5 * )
& # + ) 5
<OutputList>
<ColumnReference Database="[AdventureWorks]" Schema="[Person]"
Table="[Contact]" Alias="[c]" Column="LastName" />
<ColumnReference Database="[AdventureWorks]" Schema="[Person]"
Table="[Address]" Alias="[a]" Column="City" />
<ColumnReference Table="[cu]" Column="AccountNumber" ComputedColumn="1" />
<ColumnReference Database="[AdventureWorks]" Schema="[Sales]"
Table="[SalesTerritory]" Alias="[st]" Column="Name" />
</OutputList>
@?A +
3 . ) ) K K
K K / K K0+ K- K / K K0 K K
/ K K0+ 3 /A " + +- "
" 0. ) *
*
* * * *.
(5 7 @?A67 ) ! : %
) " + +* ) "
A + + * L .
<NestedLoops Optimized="0">
<OuterReferences>
<ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]"
Alias="[cu]" Column="TerritoryID" />
</OuterReferences>
/+
8 3 + 7 @?A +
) *.8 * L +1 )) .
SET STATISTICS XML ON ;

GO
a.[City],
cu.[AccountNumber],
ca.[CustomerID]
JOIN [Sales].[SalesTerritory] st ON [cu].[TerritoryID] =
[st].[TerritoryID]
GO
SET STATISTICS XML OFF ;

GO
8 * - + * ? #
) < ! ### / $0
+ ) ) 7 ) 3 5
<QueryPlan DegreeOfParallelism="0" MemoryGrant="82" CachedPlanSize="57"

CompileTime="29293" CompileCPU="6277" CompileMemory="520">
1 )) * - @?A 7
6 2 + * +
* )7 ) ) .8
) +
3 ) .
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="4" ActualEndOfScans="1"
ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="0">…
!
- + @?A + ,!-L * L
* + ),!- * *
+ * * * . )
+ ) @?A ) ) ) @?A
.
+ ,!- ) ) ) 7
) @?A + * ,!-
. * ,!-
* .
'5 C ? 7: (
(=. '% ( % ,% +$( $+ / ' (: %
- *L + 3 * 7 . +
* 7 :A ; 7 7
. 9 * + 1 :A
* ) + 7
7 ) * 7 .
* * :A 3 . *L
7 + + + 5 +
.
) * L * 7+
)) + )7 + )) - 8
.
* . 8 L * )
- 8 5
CREATE PROCEDURE [Sales].[spTaxRateByState]

@CountryRegionCode NVARCHAR(3)
AS
SET NOCOUNT ON ;
SELECT [st].[SalesTaxRateID],
[st].[Name],
[st].[TaxRate],
[st].[TaxType],
[sp].[Name] AS StateName
FROM [Sales].[SalesTaxRate] st
JOIN [Person].[StateProvince] sp ON [st].[StateProvinceID]
= [sp].[StateProvinceID]
WHERE [sp].[CountryRegionCode] = @CountryRegionCode
ORDER BY [StateName]
GO
8 * 7 5
EXEC [Sales].[spTaxRateByState] @CountryRegionCode = 'US'
7 3 + * %5
" #
'
) + +* &# 2 %4 +*
) + F& ! & +
* *. 4 .,
) 4 #6% 2 %4 )
3 5 1 * ) 4 .
" 7+ * @ 5 . ) +
) 2 %4 @7& ! 73 7) 4 #6% +
) * + 7. 1
) 3 5 * 3 5 1 ) )
.
8 L 7+ * L
. + :A * * )*
* 4$5$&6 : * *.
#
< ) * :A ' #. )
) * + ) L )
) * 4$5$&6 .
F * 4$5$&6 * )
) 4$5$&6 3 . < + 4$5$&6
. * + * L
)3 * * + ) *
.
# 4 #
C - 8 7 + 8 5 0
) . * +* * *
7 + ) +*L
* 3 ) 1 )
5 .
SELECT [p].[Name],
[p].[ProductNumber],
[ph].[ListPrice]
FROM [Production].[Product] p
INNER JOIN [Production].[ProductListPriceHistory] ph
ON [p].[ProductID] = ph.[ProductID]
AND ph.[StartDate] = ( SELECT TOP ( 1 ) [ph2].[StartDate]
FROM [Production].[ProductListPriceHistory] ph2
WHERE [ph2].[ProductID] = [p].[ProductID]
ORDER BY [ph2].[StartDate] DESC
)
'5 C ? 7: 4
"
8 * 3 ) ) *
7 . +* * 7 8
8 5 0 . *
! 1 .
? E 3 1 + +
2. ) 7 * +
3 .
-? E * ) * .!
+ * /7 )
1 R ) * 0. *
) * )) ) * ) .-
* ) ) * *
. * .8
+ + ) ) .
- * L 7 +? E
* . ) *
) * ) 9
E +* 3 = .8 *
* .6 . / ) *0
K K.
&
" $
" 7+ * * 7 * .
+ ) &$S ) ) 3
(#4 * ) 3 + 6 (A+ ) * *
.- * * * .
* *) * 3 ) )
) * .
6 ) * *
3 + K%.K
# *
. * + 1 * C D8C D
C D8C 5 0 D + *
C4 < D 3 . '5
'5 C ? 7: $
" %
* ) 1 " A + )
.
# :
" * * * )7 * +*
* 3 . )) * +
*L * :A 4 5 *
3 + * )) 7 .
:A 4 * ) + ) * )
) 5 + & 44 5 6$ 5 . 5
* ) + + *
) * ) * ) K K.
) ) * - AF + 5 5QQ . ).
Q Q Q %$4%4&. 7.
! * 7 ) * 3 .> + 3 +
1 * )) .
SELECT [p].[Name],
[p].[ProductNumber],
[ph].[ListPrice]
CROSS APPLY ( SELECT TOP ( 1 )
[ph2].[ProductID],
[ph2].[ListPrice]
FROM [Production].[ProductListPriceHistory] ph2
WHERE [ph2].[ProductID] = [p].[ProductID]
ORDER BY [ph2].[StartDate] DESC
) ph
) *) 7 + *
45
" &
6 * * * * ) 5
) + 7 5 0 +
1 1 + . + 2 %
4 6 3 ) 3 5 .
* )* 3 )) ;< * )
3 * 4$6 46 62462&4 2 3. 8 + <
) ? 3 .
8 * ) 3 +* + 5
(293 row(s) affected)
Table 'ProductListPriceHistory'. Scan count 396, logical reads 795, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-
ahead reads 0.
Table 'Product'. Scan count 1, logical reads 15, physical reads 0, read-ahead
reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
)* 3 + 5
(293 row(s) affected)
Table 'ProductListPriceHistory'. Scan count 504, logical reads 1008, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-
ahead reads 0.
- 3 + 3 * 3
)* /$#40 3 * /% 0.
)* ) * /0$ $ )
3 5
WHERE [p].[ProductID] = '839'
8 * 3 * /0$ $ +* *
&5
'5 C ? 7: #
" )
# + + . ) 7
)) / 0 7 7 + * +
&# 2 %4 * 3 .
)* /0$ $ 3 +* * $5
" *
4+ *
&# 2 %4 &# 2 %4 + )
/0$ $ * = ) 7 )
* + ) *
.
" *+ L < ) ) 3 +*
*.
8 * 3 * +* 5
(1 row(s) affected)
Table 'ProductListPriceHistory'. Scan count 1, logical reads 4, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead
reads 0.
8 * 3 * 3 +* 5
(1 row(s) affected)
Table 'ProductListPriceHistory'. Scan count 1, logical reads 2, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead
reads 0.
" *+ * ) /0$ $ + 3 )) +*
+ 3 * ' .
#
) :A
7 * + 7
) . ! > ) )) 3 * * * *
) + * + +* )
/0$ $ . 8 /0$ $
+ + *
5 ) .C 7 ))
* ) .
# "
:A 4 :A +*
+ 7 / 60. - 6 K K 7
* ) :A . * ) *
:A * )) +
+ + .C + 6 ) )
) * 3 .#
< ) ) 6 ' .- 8
) ) 7 +
. 3 + $ # ! + ) *5
ALTER PROCEDURE [dbo].[uspGetEmployeeManagers]

@EmployeeID [int]
AS
BEGIN
SET NOCOUNT ON;
-- Use recursive query to list out all Employees required for a particular
Manager
WITH [EMP_cte]([EmployeeID], [ManagerID], [FirstName], [LastName], [Title],
[RecursionLevel]) -- CTE name and columns
AS (
SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName],
e.[Title], 0 -- Get the initial Employee
INNER JOIN [Person].[Contact] c
ON e.[ContactID] = c.[ContactID]
WHERE e.[EmployeeID] = @EmployeeID
UNION ALL
e.[Title], [RecursionLevel] + 1 -- Join recursive member to anchor
INNER JOIN [EMP_cte]
ON e.[EmployeeID] = [EMP_cte].[ManagerID]
)
-- Join back to Employee to return the manager name
SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[EmployeeID],
[EMP_cte].[FirstName], [EMP_cte].[LastName],
[EMP_cte].[ManagerID], c.[FirstName] AS 'ManagerFirstName', c.[LastName]
AS 'ManagerLastName' -- Outer select from the CTE
FROM [EMP_cte]
INNER JOIN [HumanResources].[Employee] e
ON [EMP_cte].[ManagerID] = e.[EmployeeID]
ORDER BY [RecursionLevel], [ManagerID], [EmployeeID]
OPTION (MAXRECURSION 25)
END;
% 6 5 5QQ***.
. Q 3Q 3 4Q 3 4 7 Q.
'5 C ? 7: #%
A L 7 + @?A 5
SET STATISTICS XML ON;

GO
EXEC [dbo].[uspGetEmployeeManagers] @EmployeeID = 9;
GO
SET STATISTICS XML OFF;
GO
8 ) 77 L *
.8 * 7 @?A .
) 5
"
- 3 5 1 ) &# 2 %4 0 .
8$ # 8& . 4 # K K
) 3 ) + 5 ' #+ 3
) 7 . +* +
) ) & .
) & .
. 32 3 55
) :A.
) #5
" +
* . 6 #
4 # . )
.- + ) *
1 .A 7 + )
* / ) " A 7 0
3 + )
.- 1 )
) ) 32 3 55 ) * 6.
#
) " %# @?A + 6 2 .
<RunTimeCountersPerThread Thread="0" ActualRows="4" ActualRebinds="1"
ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" />
* ) * . +
+* . +* * * *
R * *
) * - * .
) ) * % 5
" #
-) & * 2 %4 # .
* * +* . * 1
) 3 + 1 7
.
34
- * 1 K 3 KP * ) ) * +
* * . ) * *
/ + 7 ) + 0.
9 +* * 1 ) * * 7 * *L * *
*.
34
*+ 4 # 8'2 ' #& + ) +
) + +
) .- 3 ) * 5
SELECT *
FROM [Sales].[vIndividualCustomer]
WHERE [CustomerID] = 26131 ;
7 * %%5
'5 C ? 7: #(
" ##
8 *+ '2 ' #& + * * ) 3 ;
> * :A * + * 1
) . =+
+ 7
. )) + 3 = * 1 + *
1 ) * *.
* )3 7 )
3 . + * * + L *
) 3 = ) ) * *.
, 34
- 7 B *+ = *+ K * 7K.
- 7 * 7 +
7 * + + * . 7 * )
) ) 3 + 7 *+
1 ) 3 .
7 * + + . +
* .
? 7 * )) . ) 7 *
+ * 7 *. <
+ ) 7 * ) ) + )
* 7 *. 7 + ) )
1 234$ 6 + 234$ 6 *
7 *. - ,!-+ ) *
7 * * 7 *
) .
: ) 7 *
) +* 7 +
* 4$5$&6 3 + )
3 .
7 + ) 7 * * - 8 '4 ' .
& ! . 4 ' & !
) 3 5
#'
SELECT *
FROM [Person].[vStateProvinceCountryRegion]
7 * % 5
" #
7 * 7 *+
7 * 1 7 . + *
7 5 7 ) *+ = *
7 7 *.
7 = + 3 L
) 7 * . 7 + ) * 3 * 7
7 * % 5
SELECT sp.[Name] AS [StateProvinceName],

cr.[Name] AS [CountryRegionName]
FROM [Person].[StateProvince] sp
INNER JOIN [Person].[CountryRegion] cr ON
sp.[CountryRegionCode] = cr.[CountryRegionCode] ;
= = 7 * .
9 * + 7
. 7 + ) * 3 5
SELECT a.[City],
v.[StateProvinceName],
v.[CountryRegionName]
FROM [Person].[Address] a
JOIN [Person].[vStateProvinceCountryRegion] v ON
[a].[StateProvinceID] = [v].[StateProvinceID]
WHERE [a].[AddressID] = 22701 ;
) 7 1 * 7 * 8 +
* 5
" #$
) 7 ) = *+ * * ) % +
= ) ) 7 7 * *
'5 C ? 7: #4
*. 3 ) * ) +
) 7 * *.%%
,
- ) )) 7
. L + ) 7 * .
-)3 3 * + K * ) 7
;K
) 7 )) 3 =.
7 = ) .9 * + ) 7
+ * + ) 7 + 7
3 3 ) .
, , . & 5 5 5"
< ) * 3
5 . K K L 3 )* .- *
+ :A 4 )
+ ) * .
+ ! A * 7 ) *+
*+ ) + 7 / 3
3 0. + < = ) : 6
7+ ) 7 /@ 5 0+ * + + )/ 2<
5 0+ .
-! A + 3 )
) 7) * 7 ) 7+
+ ) .
8 * 3 5
SELECT [sod].[ProductID],
[sod].[OrderQty],
[sod].[UnitPrice]
FROM [Sales].[SalesOrderDetail] sod
WHERE [sod].[ProductID] = 897
3 7 + * %'+ * )
) .
%% * +* * 7 * * "<6@ -",
+ 9 ) 4.
#&
" #%
2 %4 ) * * + 3 )) .
C ) + 7 2.
" #&
- ) ) %4+ 7 )
7+ 4 # 2 4 # < #2 . 7
= * 7 ).
8 D A C +* = 3
3 + ? +) 7.
:A + * * ) 7 7
+ 2-74 # < #7 2+ . 9 * + :A
4+ * ) 23&5 <$ *
7.
'5 C ? 7: #$
23&5 <$ * 7 :A 4 )
) . * 7+ ) +
) 7 )+ ) )) )
7. - 3 7 7+
) . )
) 7+
.
) * +* * 7 23&5 <$ .
7 ) * *L +* 46 62462&4 - 5 +
))* * . ) * 46 62462&4 - 5 ))
7 ) ) * .
IF EXISTS ( SELECT *
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID(N'[Sales].[SalesOrderDetail]')
AND name = N'IX_SalesOrderDetail_ProductID' )
DROP INDEX [IX_SalesOrderDetail_ProductID] ON
[Sales].[SalesOrderDetail]
WITH ( ONLINE = OFF ) ;
CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ProductID] ON
[Sales].[SalesOrderDetail] ([ProductID] ASC)
INCLUDE ( [OrderQty], [UnitPrice] ) WITH ( PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY
= OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON )
ON [PRIMARY] ;
GO
SET STATISTICS XML ON ;

GO
SELECT [sod].[ProductID],
[sod].[OrderQty],
[sod].[UnitPrice]
WHERE [sod].[ProductID] = 897 ;
GO
SET STATISTICS XML OFF ;
GO
--Recreate original index

FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID(N'[Sales].[SalesOrderDetail]')
AND name = N'IX_SalesOrderDetail_ProductID' )
DROP INDEX [IX_SalesOrderDetail_ProductID] ON
[Sales].[SalesOrderDetail]
WITH ( ONLINE = OFF ) ;
CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ProductID] ON
[Sales].[SalesOrderDetail] ([ProductID] ASC)
WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] ;
GO
EXEC sys.sp_addextendedproperty @name = N'MS_Description',

@value = N'Nonclustered index.', @level0type = N'SCHEMA',
@level0name = N'Sales', @level1type = N'TABLE',
@level1name = N'SalesOrderDetail', @level2type = N'INDEX',
@level2name = N'IX_SalesOrderDetail_ProductID' ;
Run this code in Management Studio with the "Include Actual Execution Plan"
option turned on, and you will see the execution plan shown in Figure 16:
#
" #)
7 ) *
7 * + .
, !
A L * 3 )* 7 + *
= 7 .
+ L ) * ) ) * ) 7 5
7. - 7 * )
7 + .- 7 + +
7+ L .
" #*
- %+ ) 7
) 7. ) ) ) 7
# ' 8
- 7L ) * .
+ ) * )
3 ) * . -) + 3
) *. 7 ) 7.
+ ) 7 *
=.
7 + 4 # 84 # < # 7+ 2-74 # .
< #7 2<+ 2< . ) 7
,! + 40 /746 62462&45
DBCC SHOW_STATISTICS('Sales.SalesOrderDetail',
'IX_SalesOrderDetail_ProductID')
* ) .C +
5
All density Average Length Columns

------------- -------------- -------------------------------------------
0.003759399 4 ProductID
8.242868E-06 8 ProductID, SalesOrderID
8.242868E-06 12 ProductID, SalesOrderID, SalesOrderDetailID
'5 C ? 7: ##
, + * +
. 7 +* ) . ($4#(##+ *
=. * ) 7. - 7
* 7 L *
. ) 7 )+ ) )
+ . L * ) 7
) ) 7 3 .
)) ) 7 * .A L
L 7 )3 ) + L
) ). A L .
- 8 L * =
3 ) . 3 )
0 1 2 5
SELECT sod.OrderQty,
sod.[SalesOrderID],
sod.[SalesOrderDetailID],
sod.[LineTotal]
WHERE sod.[OrderQty] = 10
7 ) 3 * % 5
" #
8 &# 2 %4 #
) +* ? E A*.
A L * 7 3 5
CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_OrderQty] ON

[Sales].[SalesOrderDetail] ( [OrderQty] ASC )
WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON )
ON [PRIMARY]
C ) + ) + L L *
% R
+ * * 7 . * *
* + )+ 7 + L 7 * 7 < &&
40 /746 62462&45
All density Average Length Columns
------------- -------------- -------------------------------------------
0.02439024 2 OrderQty
2.18055E-05 6 OrderQty, SalesOrderID
8.242868E-06 10 OrderQty, SalesOrderID, SalesOrderDetailID
8 ) ? % ) 2 +
? 7 . 3 )
+ % %(%$ * 4 # < # . '%
) ? . 1 L+ )+ 3 7
)) 3 .
%
)* 3 * + * * 7
) =.F ) =
7* 3 + + * L ) )
3 / 40. > 7+ *
+ + * 7
+ ) 3 .
) L ) * - 8 + L * 7*
5
DROP INDEX [Sales].[SalesOrderDetail].[IX_SalesOrderDetail_OrderQty]
,
) )) * )) *
." )) * +
7 .
) * 7 * + *+
+ 7 3 * * )) 7 . 7 +
* + * 75
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[NewOrders]')
AND type in ( N'U' ) )
DROP TABLE [NewOrders]
GO
SELECT *
INTO NewOrders
FROM Sales.SalesOrderDetail
GO
CREATE INDEX IX_NewOrders_ProductID on NewOrders ( ProductID )
GO
/ ?@A ) 0. -) 3
+ 3 + 7
-- Estimated Plan
SET SHOWPLAN_XML ON
GO
SELECT [OrderQty]
,[CarrierTrackingNumber]
FROM NewOrders
WHERE [ProductID] = 897
GO
SET SHOWPLAN_XML OFF
GO
BEGIN TRAN
UPDATE NewOrders
SET [ProductID] = 897
WHERE [ProductID] between 800 and 900
GO
-- Actual Plan
GO
SELECT [OrderQty]
,[CarrierTrackingNumber]
FROM NewOrders
WHERE [ProductID] = 897
'5 C ? 7: % %
ROLLBACK TRAN
GO
GO
@?A ) / 32 4 +
%0+ ) .
! ) :A * )
* @?A . 5
" #+
- 7 5
"
) %# ) 7 . +
+ * 7* 4$5$&6
. + 3
7 )+ 2< 5 ) .
* ) ) *.
9 * + ) + 3
+ 7+
* + * ) .
. '(( % * %. '('. " ) + L
+ * *
.
7 . + *+ +
7 * * 7 .
* ) )
* * +* )+ +
. ) 7
7 ) = 3 * .
* ) 7 L
%
L ) .F ) .
F )
7 + .
45 67 * 9 % (
(>. $% ($ ,% / ' ,$% % *, ,%
* = + + .
5
• ? 0 = 7 )
3 .
• 1 0 = 1 3
• 6 #0 ) 7)
L * ) ) + L
) * 5 ! .- )
3 . 7 3
+ * 3 *
.
) ) ) 1 ) 3 + L
! !: !. 8 ) ) + L
L . ) 7 *
) + *
) .
!
3 )3 ) ) )) .
* )* ) .
: ) 62 3 . 7 ) *5
SELECT ...
OPTION (<hint>,<hint>...)
: L 234$ 6 7 * * 4$5$&6
.F L 3 .
!) * + * 5 1 )
O
?$( ( ($'
* P 0 40 <$ P
/ * <24623&6 & 6$ 0.
= + .
7 ** 3 )3
) ))7 L .
SELECT [c].[Suffix],
COUNT([c].[Suffix]) AS SuffixUsageCount
GROUP BY [Suffix]
3 ) L
* )3 ) )
% '
) . ) + ) + 7 +
* %5
" #
- + K ) K = . )
7 * 9 ? /- 0 .
* + ) 7
. )
.4# $+ * 5
"
L ) * +
* ) )
9 ? . <$ 3 5
SELECT [c].[Suffix],
COUNT([c].[Suffix]) AS SuffixUsageCount
GROUP BY [Suffix]
OPTION ( ORDER GROUP )
* * (5
" $
45 67 * 9 % 4
8 L = + <$ +
) + L ) 4 6 ) 4
!! ! +* * * .
- * + 3 ).4# $
)%.$$$'4 ) + . )
) 7 .
, + ) * + 7 +
= 9 ? )
- . + : 6 * = *
) + ) .8 3 *
) =+ L
. = 3 + * )) 7
) 7.
+ ( ? ? $% '%,$%
)) * C" <" 3 +
= + ) .
* * ) * ))
) * C" <" 7 .
7 3 * ) ) )
5
SELECT [pm1].[Name],
[pm1].[ModifiedDate]
FROM [Production].[ProductModel] pm1
UNION
" %
F = +
7 ) + + ) * 7 .
) . ($$.
) ) C" <" * ))
) + $ $ 32 3 5
UNION
OPTION ( MERGE UNION )
% &
" &
F ) 32 3 ! 1 )
& .9 * + ? E * * ) +
*L ) = * 4 . ) 3
) . ($$ . 4' . L * .
8 ) + 0 40 32 35
UNION
OPTION ( HASH UNION )
* 7 + * &5
" )
7 ) +* .9 * +
/. '#$0 ) 3 +)
) 3 + 9 ? L )) )
.
+ * ) ) 3 +
) ) 3 .
$$ ?+ ( ? 7
$,%
1 3 .
9 * + ) 1 / 0 ) 1 +
1 3 .
F L ) )) ) Q<+
) 3 .! ) )
) ? L ) ) * 3 * .9
3 7 5
45 67 * 9 % $
SELECT s.[Name] AS StoreName,

ct.[Name] AS ContactTypeName,
c.[LastName] + ', ' + c.[LastName]
FROM [Sales].[Store] s
JOIN [Sales].[StoreContact] sc
ON [s].[CustomerID] = [sc].[CustomerID]
JOIN [Person].[Contact] c
ON [sc].[ContactID] = [c].[ContactID]
JOIN [Person].[ContactType] ct
ON [sc].[ContactTypeID] = [ct].[ContactTypeID]
" *
- + 3 7 )3 5 0
.A L Q< ) 3 . )
+ ? G? + K
<K 7.
Table 'Contact'. Scan count 0, logical reads 1586, …
Table 'Worktable'. Scan count 0, logical reads 0, …
Table 'Address'. Scan count 1, logical reads 216, …
Table 'CustomerAddress'. Scan count 753, logical reads 1624, …
Table 'Store'. Scan count 1, logical reads 103, …
Table 'StoreContact'. Scan count 20, logical reads 42, …
Table 'ContactType'. Scan count 1, logical reads 2, …
+ &
* 3 . * 3 ) 9
E * * A
E ) 3 5
OPTION ( LOOP JOIN )
"
%
" * 0 1 5 1 . . )
3 7 L 3 +*
+ ) * .8 ) 3
+ ) 3 + +
) + ) %$ %' .
9 * +* + )) 5
" * ) + )
* + *L ) .8 )* * )
3 $ $ 1 23 ; ) ) 3 5
OPTION ( MERGE JOIN )
" +
7 ) * ) + ;
8 L * + )
.8 .9 * + ) * 7 +*
* 0 40 1 23 * .? ) ) )
3 5
OPTION ( HASH JOIN )

45 67 * 9 % #
" #
8 L ) 7 9 E . 7
* 3 Q< 5
7 + ?6> 6 E< " )

Q< ) 3 +* ) ) * .
+ *L ) ) . + *L
) ) . *
) + ) L + )
* ) .
46 ) = )
7 ) L L * ) +* L L
. ) * 3 7 5
SELECT *
JOIN [Sales].[SalesOrderHeader] soh
ON [sod].[SalesOrderID] = [soh].[SalesOrderID]
" ##
3 ) 3 + ) +
* )7 ) % * 3 5
OPTION ( FAST 10 )
%%
" #
) 0 ) 1 + = 3
5 . 1 ) * ) + )
* * * . + *
) * + L )) )
) 3 . ) 3 * %.#$(.
. % /) ) % * 0. ) +% ( )
3 % %+ $ ) 3 + ) 7 )
3 +) '. 4 . * *
) * * * +
) .
) * * ) )) +
5 1 23 +) . ) )) O
$( $( (
F L ) 3 ) . L * 3 * )*
." + = * * 1
&$ <$ = )1
3 ). * ) L ) ) 1
=. =
* +* )
3 ) 7 .9 3 3 5
SELECT pc.[Name] ProductCategoryName,

psc.[Name] ProductSubCategoryName,
p.[Name] ProductName,
pd.[Description],
pm.[Name] ProductModelName,
c.[Name] CultureName,
d.[FileName],
pri.[Quantity],
pr.[Rating],
pr.[Comments]
LEFT JOIN [Production].[ProductModel] pm
ON [p].[ProductModelID] = [pm].[ProductModelID]
LEFT JOIN [Production].[ProductDocument] pdo
ON p.[ProductID] = pdo.[ProductID]
LEFT JOIN [Production].[ProductSubcategory] psc
ON [p].[ProductSubcategoryID] = [psc].[ProductSubcategoryID]
LEFT JOIN [Production].[ProductInventory] pri
ON p.[ProductID] = pri.[ProductID]
LEFT JOIN [Production].[ProductReview] pr
ON p.[ProductID] = pr.[ProductID]
LEFT JOIN [Production].[Document] d
ON pdo.[DocumentID] = d.[DocumentID]
LEFT JOIN [Production].[ProductCategory] pc
ON [pc].[ProductCategoryID] = [psc].[ProductCategoryID]
LEFT JOIN [Production].[ProductModelProductDescriptionCulture]
pmpd ON pmpd.[ProductModelID] = pm.[ProductModelID]
LEFT JOIN [Production].[ProductDescription] pd
ON pmpd.[ProductDescriptionID] = pd.[ProductDescriptionID]
LEFT JOIN [Production].[Culture] c
ON c.[CultureID] = pmpd.[CultureID]
45 67 * 9 %%%
! * ) + L ) L 1
.9 7 7 5
" $
< ) * . *
) ) ) ) .
.4 4(.
3 &$ <$ 3 5
OPTION (FORCE ORDER)
* %'.
" %
, L %'R
) 7 %(. - 1 * 7
6A6 ) 3 . C ) + ) *
)) =. ) 3
%.%&$&. ) )) 1
) ) ) 3 . +*L )
* .
+ /$
F ) + 3 1 ) +
* .F + :A )
7 ) + +* .F )
* 7 . 7 * 3 3 5
%%
" &
8 7 *+ * / *
05
" )
9 + L * / 0
) ) + + ) .
)) +
) ) + L )). L * -<
).
-< 3 ) * 3 +
* * * )K? 7, ) K.
) * + L
) ) 3 5
sp_configure 'cost threshold for parallelism', 1 ;

GO
RECONFIGURE WITH OVERRIDE ;

GO
SELECT wo.[DueDate],
MIN(wo.[OrderQty]) MinOrderQty,
MIN(wo.[StockedQty]) MinStockedQty,
MIN(wo.[ScrappedQty]) MinScrappedQty,
MAX(wo.[OrderQty]) MaxOrderQty,
MAX(wo.[StockedQty]) MaxStockedQty,
MAX(wo.[ScrappedQty]) MaxScrappedQty
FROM [Production].[WorkOrder] wo
GROUP BY wo.[DueDate]
ORDER BY wo.[DueDate]
GO

GO

GO
* 7 ) ) +
) %& . = 7 ) .A
) &# 2 %4
? . #3 : 7
/U0 . * * )) + ) *
.
9 * +* * * 3 + * .8
) * )+ *
)) 3 5
45 67 * 9 %%(
SELECT wo.[DueDate],
MIN(wo.[OrderQty]) MinOrderQty,
MIN(wo.[StockedQty]) MinStockedQty,
MIN(wo.[ScrappedQty]) MinScrappedQty,
MAX(wo.[OrderQty]) MaxOrderQty,
MAX(wo.[StockedQty]) MaxStockedQty,
MAX(wo.[ScrappedQty]) MaxScrappedQty
FROM [Production].[WorkOrder] wo
GROUP BY wo.[DueDate]
ORDER BY wo.[DueDate]
OPTION ( MAXDOP 1 )
* 7 + + +
. * ) /. . *
)) 05
" *
- + L ) 7 L
&# 2 %4 .
* * 4 ? +
. 0 1 ) * )
) + ) 4
) 4 # .
) ) 7 . + +
) * * * 7
3 ) )
. 8 = * + L
* .
$ ,+,@ $(
F ) 3 * 3 ) + +*
* ) . 8 )
7 + ) ) +
+ 7 ) * .
+ + ) + *
7 * )
.
62 2G$ * * :A 4. *
= =3 7 ) +
) ) * 3 .
7 ) . * )
+ 7+ * +
. ) +
) + + + * *
7 .
:A + * * 5
'. > >6 <? A6
4. @$$ 2-$< 5 3
! ) / 0
$& 25$ ) 3 + 7 = )
%%'
3 + 7 ) @$$ 2-$< 5 3
* ) .
:A 4+ * )
+ 62 2G$ .
8 ) * )3 5
SELECT *
WHERE [City] = 'Newark'
SELECT *
WHERE [City] = 'London'
8 L *L * )) 7 5
"
) ! ) ) 3 + ) 3 1
7 +* ) .%#' . (.
3 7 +* * *
.
)* ) :A *L + 5
DECLARE @City NVARCHAR(30)
SET @City = 'Newark'

SELECT *
WHERE [City] = @City
SET @City = 'London'

SELECT *
8 L 7 ) 3 5
45 67 * 9 %%4
" +
L 7) 3 * L * )
F& .
A L ) . 3 +* = =
) " * 5
DECLARE @City NVARCHAR(30)

SELECT *

SELECT *
OPTION ( OPTIMIZE FOR ( @City = 'Newark' ) )
"
LA L * ) / ) 3
LA L0 * 7 7 ) 3 .
, ) 3 + L * ) ) *
3 . 62 2G$ * ) =
) * + * . 7
* ) + L" * L+
) ) + LA .L
C ) 3 * ) . *
62 2G$ * ) ) +
.F *
3 .
%%&
( + (,@ ,$% ,+ ? $(
= +) +
+ . L L 3
)* 3 + * .
( $+ ,
F 3 ) * ) .
7 * 3 = )
3 ) . 1 3 + /
:A :A 03 ) .
6 3 :A + )) +
)) . +* ) 3 + )
. 7 *
* ) ) * ) .
) ., ) 3
+ L * ) 7 . >
) ) * 3 + ) )
3 )) .
$& 25$ * :A 4. =
* 7 7 ) 3 .
) * + + L ) ) ) * .
7 + + ) :A 3 +
+ * .> ) +
)
7 * .
F +* L
+ $& 25$ 3 )) . )
* 3 ) 3
. ) ) 3 :A+
1 . 8
* + = )
.
) 3 + = *
* . . ) * )3 5
DECLARE @PersonId INT

SET @PersonId = 277
SELECT [soh].[SalesOrderNumber],
[soh].[OrderDate],
[soh].[SubTotal],
[soh].[TotalDue]
FROM [Sales].[SalesOrderHeader] soh
WHERE [soh].[SalesPersonID] = @PersonId
SET @PersonId = 288

[soh].[OrderDate],
[soh].[SubTotal],
[soh].[TotalDue]
)7 5
45 67 * 9 %%$
" #
8 ) * ) + * ) 3
)) + ) 7
7 . + ) 3 $& 25$ .
+ L 3 )
3 $& 25$ + *
$& 25$ ) 3 .
DECLARE @PersonId INT

SET @PersonId = 277
[soh].[OrderDate],
[soh].[SubTotal],
[soh].[TotalDue]
OPTION ( RECOMPILE )
SET @PersonId = 288

[soh].[OrderDate],
[soh].[SubTotal],
[soh].[TotalDue]
OPTION ( RECOMPILE )
) * )3 5
"
" 3 * 2-74 # 0 74 # 2< 7
) S ) ) 3 + )4 S.
2 %4 @ 5 * 3 5 )
7 * * * ) *.
%%
($&' %
* * * * *. 7 5
&. - * = ?-@ =
* 4
$. - * 1 /A<!0 23 +- 5 6$-6 .
+* *+ L ) +
* * ) . 46 5 3
* L =.8 *
+ * 3 = * L
K K .
) )* * .
) %
- + +
. - + 3 ) . @$$
5 3 L + =
* ) . +* +
. =
* * + )
. ) +
* + 3 + 3 .
9 * + )) *
.
) ,/ %
@$$ 2-$< 5 3 3 @$$ 5 3+ )
) + @$$ 2-$< 5 3
.
C * 7 . * ) :A
) ) 3 . ) )
= + ) .
- * @$$ 5 3+ * )
) 3 7 # 3 +)
.
/ % 3, *
F * .< ) 3 L L
. 7 ) 3
= + 7 + *. 8 ) 7 + * )
.< ) * + +*
* . 3 * ))
+ ) ) * L . + L
+ * * 3 .
$- 3< =2$/4 3 ) 7 * * 3
) = ) . = 7 *
) * * ) / * + 3 ) *0
1 * *. * *
/260 (3 $- 3<+ 7 * * 3 .
+ 7 * ) * * ) . +
.9 * + ) 7 * +
45 67 * 9 %%#
+ ) ) . L
) .
C ) 7 * * - 8 +* 3 5
SELECT *
FROM [Person].[vStateProvinceCountryRegion]
( * 7 5
" $
- 7 * 7+ 7 ) . )*
3 + 62 3 ($- 3< =2$/4++ * '5
" %
" ** L 7. 8 < = + *
7 ) * &# 2 % 4
8& ! 84 ' . 1
! 1 + ) 4 ' 4 .
) 3 ). ' % 7 * *
. ' + ) )
) 7 ) = *.
+ /( '( ,$%
8 ) 67 :A +
) 3 * . - $& 42 3
) * 3 .
B * ( +$&$. = * ) ) .
) ) % .8 +
7 . * .C
L 7 + ) + 7
.
' %
= * @?A
* . .
%
7
-> ) :A ) 1
*L + ) 3 . + 1 5
• 3 5 > 5 *) /K K0 *
/K K0 * ) 1 .
) * * .B )) )
.
• ! > 5 * + * .
) ) *.>3 3 1 . 6)) )
• 0 > 5 * ) + *+ 3
1 + ., )
*.? ) ) /
* 0
! ) 1 :A * = L
) )) 1 . + ) L
) ) % .
- ) 1 3 / + + 0* 1
.E ) * * .
? + )
- * 5
SELECT [pm].[Name],
[pm].[CatalogDescription],
p.[Name] AS ProductName,
i.[Diagram]
FROM [Production].[ProductModel] pm
LEFT JOIN [Production].[Product] p
ON [pm].[ProductModelID] = [p].[ProductModelID]
LEFT JOIN [Production].[ProductModelIllustration] pmi
ON [pm].[ProductModelID] = [pmi].[ProductModelID]
LEFT JOIN [Production].[Illustration] i
ON [pmi].[IllustrationID] = [i].[IllustrationID]
WHERE [pm].[Name] LIKE '%Mountain%'
ORDER BY [pm].[Name] ;
8 L ) * 7 5
% ) 1 + > 1 + * * )
. ) 1 ) .
)) 7 + * * L * )
.
45 67 * 9 % %
" &
) ) * . ) /0$ $ 52@$
H H * L 7R
7 # .
L 1 0 + '&S ) )
3 . < 1 + <$ 4
. * &# 2 % 4
#2## 1 * A .
* 7 2## 1
* A . )
. #' $.
8 )* *L =
" A 1 ) 9 ? 1 ;8 )
5 1 * #5
SELECT [pm].[Name],
[pm].[CatalogDescription],
p.[Name] AS ProductName,
i.[Diagram]
LEFT LOOP JOIN [Production].[Product] p
LEFT JOIN [Production].[ProductModelIllustration] pmi
ON [pm].[ProductModelID] = [pmi].[ProductModelID]
LEFT JOIN [Production].[Illustration] i
ON [pmi].[IllustrationID] = [i].[IllustrationID]
WHERE [pm].[Name] LIKE '%Mountain%'
ORDER BY [pm].[Name] ;
)* 7 *3 +*L ) * 5
" )
+* * * 9 ? +* * " A
.- + ) 1 ) A
+* ) 1 .
. - + " A 1 ) 4&S ) +
%
* 9 ? ) '&S. - +
) .%& ('.
) 5 * $ $ + L ) * 5
" *
" A ? E )
. $&'$+ )) ) ).
? E +* 3 +
9 ? " A .
) ) ) +* 3
* Q< ) 3 . ) 3 +
+ 5
Original (Hash)
Table 'Illustration'. Scan count 1, logical reads 273
Table 'ProductModelIllustration'. Scan count 1, logical reads 183
Table 'Worktable'. Scan count 0, logical reads 0
Table 'ProductModel'. Scan count 1, logical reads 14
Table 'Product'. Scan count 1, logical reads 15
Loop
Merge
* ? A 1 3 7 )
9 1 . )) * *
+ A 1 + 444 * 3 ) %4 )
? 9 1 . )) + + *
9 3 . * * ? 1 .
= * .!
7 ) +
9 1 * ) ) . ) + *
+ L ? 1 * ) +
*L 1 + * * =
+ * .
#
) * = K K *
7 . 7 + ) ) + )
7 * .
- * 3 1 + =
) . + ))
45 67 * 9 % (
+ )) .
1 .
) * . )
L )) 7 +* * L . *
7 . ) ) + )
! < * :A 4.
# !
7 :A 4 /260 * *
) 5
FROM TableName WITH (hint, hint,…)
/260 * 3 + 3 +
* 7 +
* /260
) . 6 * L 3
/260 * + ) .
%$ / %
8 7 * ) * 3 + ) 3 $- 3<
* $- 3< =2$/4 3 7 * *
) K 7 K * ) . *
) * ) 7 * )
* ) 7 ) .
:A 46 , * 7 7 * )
= 7* ) 3 . 7 *
. 3 ) * ) 5
• -" I"CAA
• -" I8->" "
• <" - I"CAAIF 6A, I"CAA
• -" I -,, "
• -> 9-!<>
• :C< 6,I ,6" 6>
• "C?6> I><C",-!<> ))
C 3 $- 3< ) = 7) 7 *.
'+ * 3 ) ) 7 B *+
'4 ' & ! I - 8 . = 7 *
* * 7 ) ( 1 . 8
3 $- 3<
SELECT a.[City],
v.[StateProvinceName],
v.[CountryRegionName]
FROM [Person].[Address] a
JOIN [Person].[vStateProvinceCountryRegion] v WITH ( NOEXPAND )
ON [a].[StateProvinceID] = [v].[StateProvinceID]
WHERE [a].[AddressID] = 22701 ;
Now, instead of a 3- table join, we get the following execution plan:
% '
"
" *+ * 7 ) *+ *L
) +* ) . # 4 . &4$.
,% /8
9
%(+ * ) 7 * .
7 7+ * 7+ ) +
) 7 5
FROM TableName WITH (INDEX(0))
9 * + ) 7
* 7 / 7* *
05
FROM TableName WITH (INDEX ([IndexName]))
F 7 ) + ) 7
* .
A L 3 , " + 6 " 5
SELECT [de].[Name],
[e].[Title],
[c].[LastName] + ', ' + [c].[FirstName]
FROM [HumanResources].[Department] de
JOIN [HumanResources].[EmployeeDepartmentHistory] edh
ON [de].[DepartmentID] = [edh].[DepartmentID]
JOIN [HumanResources].[Employee] e
ON [edh].[EmployeeID] = [e].[EmployeeID]
ON [e].[ContactID] = [c].[ContactID]
WHERE [de].[Name] LIKE 'P%'
8 7 5
45 67 * 9 % 4
" +
8 ) 7 7 1 "
A . *L * ) )*
7 0 8<
L 7+ @7< 7< 2<. 8
23<$- + ) *5
SELECT [de].[Name],
[e].[Title],
[c].[LastName] + ', ' + [c].[FirstName]
FROM [HumanResources].[Department] de
WITH ( INDEX ( PK_Department_DepartmentID ) )
JOIN [HumanResources].[EmployeeDepartmentHistory] edh
ON [de].[DepartmentID] = [edh].[DepartmentID]
ON [edh].[EmployeeID] = [e].[EmployeeID]
ON [e].[ContactID] = [c].[ContactID]
WHERE [de].[Name] LIKE 'P%'
) * 7 5
" $
8 &# 2 %4 ) 2 %4 .
7 3 + * . $(#&'(
. $(#( #. 8 7 ) + ))
)* * + .
9 * + 7 L ) ) 3 *
3 * ) ))
.
,( ($*
E - 3 + + 46 2 46 /) =
* ) * ) ) 3 .
% &
+ 46 2 46 / 3 - 3 +
.
? ) 46 2 46 / )
) :A ." +*L 7 . ) * 3
)
5
SELECT [pm].[Name] AS ProductModelName,

[p].[Name] AS ProductName,
SUM([pin].[Quantity])
JOIN [Production].[Product] p
JOIN [Production].[ProductInventory] pin
ON [p].[ProductID] = [pin].[ProductID]
GROUP BY [pm].[Name],
[p].[Name] ;
7 5
" $#
- + 2 %4 #
) ) . 1 &# 2 %4 )
+ 0 . ) 2'
&# 2 %4 1
3 5 . + ) 4
!! ! .
)* * ) 3
)) ) 3 * + 5
SELECT [pm].[Name] AS ProductModelName,

[p].[Name] AS ProductName,
SUM([pin].[Quantity])
JOIN [Production].[Product] p WITH ( FASTFIRSTROW )
JOIN [Production].[ProductInventory] pin
ON [p].[ProductID] = [pin].[ProductID]
GROUP BY [pm].[Name],
[p].[Name]
) * 7 5
45 67 * 9 % $
" $
= )) . )
# ) + L * ) ) .
3 5 * ) * )
) # .
) . + *
1 + ) * 7
+ * 1 1 .
) .% %& $ 3 . %%# # .
< + + * ) * *
3 + ) # .
) * + 3 4 ' %
# . * +
. L
3 . ) Q<
+* ) W%( .W + * *
) .
!
8 < = ) +
. ) 3 +E : *
.>
. - ) < =
* L ) 1 ) .
*. - ? )
+ ) = .!
3 ) . )
* 3 * * 3
R ) + )
.
%
(A. '( $( $ ( ,$%
? * :A
+ * * . 9 * +
* 7 * .
+ 3 ) + +*
.9 * +
* /
) 0.
) ) )) ) ) +
* 7 . + ) +
* / 0 / 0 . )
+
3 + . + )) *
. ) * *
+ +) + . *
) ) 7 .
- * 7 + ) 7+
@?A ) ) )
7 . * * )
) + .
"
7 + * + ) +
$6&0 3$-6 + )
- 8 . L * *
3 )
* * * )) 7
.
DECLARE CurrencyList CURSOR FOR

SELECT CurrencyCode FROM [Sales].[Currency]
WHERE Name LIKE '%Dollar%'
OPEN CurrencyList
FETCH NEXT FROM CurrencyList
WHILE @@FETCH_STATUS = 0
BEGIN
-- Normally there would be operations here using data from cursor

END
CLOSE CurrencyList
DEALLOCATE CurrencyList
GO
+ * %5
&5 < % #
" #
$"
C K, 6 67 K
7 ) . 3 ) 7 )
7 + * 5
"
8 L . * ) )
5
DECLARE CurrencyList CURSOR FOR

SELECT CurrencyCode FROM [Sales].[Currency]
WHERE Name LIKE '%Dollar%'
%(
" $
) *
. ) * ) + +*L
7 . +* 7
4 # 8& .
" %
7 %' *. ) *
+* 3 ) ) 3 +
) 3 ) * * /
4+ *0.
&5 < %(%
" &
8 * + * 7+
. 7 * *
) / 4+ *0. L ) K* K.
" )
-) +* ) .
?
: * ) +
7 +* 6 9 .
) * +) ) /* L L ) 05
%(
" *
+ ) < +* ) * , .
<
, ) ) )R + )
+*
+ + . *
) ) . + + * )) +
) ) 5
"
C ,?A 3 ) +* * ) :A ) +
:A ) = .
& & ##
7) ) 7 +) + )
* & & ##. + )
? ) L * .
: : (* ) $3 & 4 +
$6&0 & 4 5
OPEN CurrencyList

&5 < %((
" +
: ' * 7 * :A $6&0 & 4 *
+ * +) /025$ + & 3<
.
BEGIN
--Normally there would be operations here using data from cursor
END
" #
+: 4 : & + )
.
CLOSE CurrencyList
DEALLOCATE CurrencyList
" ##
%('
! $"
8 * 7 + K, 67 K +
7 L . * ) * 5
" #
)) + ) * )
/ * ) * + )) +
) * L 0.
< . +
+ $6&0 & 4 +
:A . *
) + $6&0 3
4$5$&6 .
9 ) + 7 *
. L ) 7 + *
+ ) )) $6&0 .
3 + +* + 7 5
" #$
+ $"
) ))
.8 L R 7*L
.
,
C , + + ) +
* . L
) ) . + ) *5
DECLARE CurrencyList CURSOR STATIC FOR
5 ! #
" * 7 .F 7 . %'
* ) ) 3 + * ) .
3 1 , 3 .
" #%
&5 < %(4
+ + * 2 %4 )
4 # 8& . , ) 4 ! .
+ + .
+ + L 4 ! A**J.
7 +* *
3 .
" #&
3 * )) + :A
7* 3 . + + )
) . +*
K%K ) 7 + 4 > .
. L *
) ) . + L *
) K7 :7 . *
) * 7.
" #)
+ * * ) + 7
: .
%(&
# ?
: K * ) *
K * +) + *
* / 70.
: * ) 7
7. " + + : )
3 + ) : . +
L ) + +
* L .
4
+* 4 +
.
+* 7
+ * ) * *
) . 7 * * * )
.
#
)* 7 3 - 67 +* * .
) 3 * +
7. * )
* ) : *+ * *
/025$ .
" ##
7 ) * . "
* * $3 & 4 * .8
7 * )
) .
&5 < %($
" #
):
+ ) 7 + $6&0
+ * +
K K * ) .
+ +
.
D ) ) ) * +
* ) ) ) ) .
7 + )) ) * 7 .
A L ) 5
DECLARE CurrencyList CURSOR KEYSET FOR
5 ! #
7 * %#5
" #$
%(
" * *L * * + L = * )
R ) ) ) ) .
) + : + 7
) ) .
) *. * D +
+ .
1 )) * : * +
) ) * . * )
5
"
) : ) +* ) )
) 7 : . + + 1 +
" A 4 # 8& . * D
* .
& 4 ) . )
) &# 2 % +
+ ) . 1 ) ) " A
) * * .
#
8 7 ) +* * %5
" #
$3 & 4 + 7
.
: + $6&0 3$-6 $6&0
& 4 )) * .8
+ L . )
) ) 3 * +
* ., ) *
* + .
&5 < %(#
( B$% :
6 ) + 7 ) + * *
. )* ) $ <7 35 K, 6 67
K5
DECLARE CurrencyList CURSOR READ_ONLY FOR
8 ) + * * *
* 5
"
+ ) *L 7 .C )
) + ) * .
* ) . + 7
& 3 7 * * . ) Q<+
) 7 + 3
* . 5 . 7
7 L * .
- + 7 *
* 3 * L .
)3 ) )3
) ) .
) * 7 * * * ) ) + * *
) + ) * )
) .
) 3 +* )
+ + +
* ) .
9 L 3 + +* 5
DECLARE @WorkTable TABLE

(
[DateOrderNumber] INT IDENTITY(1, 1)
,[Name] VARCHAR(50)
,[OrderDate] DATETIME
,[TotalDue] MONEY
,[SaleType] VARCHAR(50)
)
DECLARE @DateOrderNumber INT

,@TotalDue Money
INSERT INTO @WorkTable

(
[Name]
,[OrderDate]
,[TotalDue]
)
%'
SELECT s.[Name]
,soh.[OrderDate]
,soh.[TotalDue]
FROM [Sales].[SalesOrderHeader] AS soh
JOIN [Sales].[Store] AS s
ON soh.[CustomerID] = s.[CustomerID]
WHERE soh.[CustomerID] = 17
ORDER BY soh.[OrderDate]
DECLARE ChangeData CURSOR

FOR SELECT [DateOrderNumber]
,[TotalDue]
FROM @WorkTable
OPEN ChangeData
FETCH NEXT FROM ChangeData INTO @DateOrderNumber, @TotalDue
BEGIN
-- Normally there would be operations here using data from cursor
IF @TotalDue < 1000
UPDATE @WorkTable
SET SaleType = 'Poor'
WHERE [DateOrderNumber] = @DateOrderNumber
ELSE
IF @TotalDue > 1000
AND @TotalDue < 10000
UPDATE @WorkTable
SET SaleType = 'OK'
ELSE
IF @TotalDue > 10000
AND @TotalDue < 30000
UPDATE @WorkTable
SET SaleType = 'Good'
ELSE
UPDATE @WorkTable
SET SaleType = 'Great'
FETCH NEXT FROM ChangeData INTO @DateOrderNumber, @TotalDue
END
CLOSE ChangeData
DEALLOCATE ChangeData
SELECT *
FROM @WorkTable
8 L * 3 + L .
) 3 5
Number Name OrderDate TotalDue SaleType
1 Trusted Catalog Store 2001-07-01 18830.1112 Good
3 Trusted Catalog Store 2002-01-01 51251.2959 Great
4 Trusted Catalog Store 2002-04-01 78356.9835 Great
5 Trusted Catalog Store 2002-07-01 9712.8886 OK
12 Trusted Catalog Store 2004-04-01 702.9363 Poor
6 67 / * 0 )
+ + * ) 7 )
. 7 + + 3
+ 4 #8 0 4 # 84 +
&5 < %'%
+ ) + ) )
. $ )) %%( .
A L ) - 67 +* * ) )
) 5
" $
8 ) ) &S )
) +* ) ) ) ) + )
) % *.
) +* * ) +* 4S )
) ) .
+* * .
7 ) $6&0 & 4
) ) .
* ) +*L
) 5
DECLARE ChangeData CURSOR STATIC
" *+ * ) + L 7 ) -
67 5
" %
" * ) %S ) ) +
* L + *
) .9 * . 3 7
%'
'& . *3 7 $4 . )
.
A L * D ) . 5
DECLARE ChangeData CURSOR KEYSET
) * ) - 67
" &
- + ) %S+ -
+ ) /( 0
* ) .
A L 5
DECLARE ChangeData CURSOR READ_ONLY
" *+ ) * &5
" )
9 + $6&0 ) ) %S ) +
) + ' .
) + ) .A L
)* L ) . 5
DECLARE ChangeData CURSOR FAST_FORWARD

&5 < %'(
/ <7 35 ) * +
$ <7 35 . * &
+ / <7 35 $ <7 35 +
467 / < +* ) ) .9 * + +
L .A L )*
/ <7 35 5
DECLARE ChangeData CURSOR FORWARD_ONLY KEYSET
7 + ) L . +
) ) + D6F 6 3 *
.
9 * +* )* ;8 *
5
SELECT ROW_NUMBER() OVER(ORDER BY soh.[OrderDate])

,s.[Name]
,soh.[OrderDate]
,soh.[TotalDue]
,CASE
WHEN soh.[TotalDue] < 1000
THEN 'Poor'
HEN soh.[TotalDue] BETWEEN 1000 AND 10000
THEN 'OK'
WHEN soh.[TotalDue] BETWEEN 10000 AND 30000
THEN 'Good'
ELSE 'Great'
END AS [SaleType]
FROM [Sales].[SalesOrderHeader] AS soh
JOIN [Sales].[Store] AS s
ON soh.[CustomerID] = s.[CustomerID]
WHERE soh.[CustomerID] = 17
ORDER BY soh.[OrderDate]
3 7 . ! ) )) .
) 4 # 0 ' * *
. 7 +* L + )
* ) . ) ) 7 +*
7 5
" *
*
) 3 * .
4'S ) . L + * * .
6 * 3 ) .
7 * ) . ) * )
* * ) )
.9 * + * + * )) * )
= 1 ) * ) .
L )) *+ * * * % + * )% +
%''
* 1 * )
* .
!
? ) + )
) :A :A . 1 * .9 * +*
) * ) + * L
7 + +* .
, L ) * * * +
) + * * + )
. )) * ) )
7 . < + +
)) ) 5
* * ).. 1
) * * .
&5 < %'4
%'&
(C. /+ ,% / ' ,$% %
8 ) :A 4+ @?A
) + )@?A+ * + 7
.
F * @?A * :A ) 5
%. @?A @?A @?A+ *
) @: 3 @?A 7 .
. : @?A @:
(. @?A < 6"@?A @?A
* 3 ) + +
'. @?A <> @?A @?A
) 3
8 * )@?A + <> @?A .6 )
) <> @?A 3 )) :A * )) 7
+ * )) ) .
F @?A * :A < 6"@?A @: . < 6"@?A
* * ) @?A .8 * 7 7
) < 6"@?A 3 + * )
.
@: * * ) + 7
)* 7 7 . * 3
) * .
@?A ) )* * . + @?A +*
3 @?A+ C *
) :A. + * @?A+
+* )) :A ) )
.
+ @?A ) :A +
* @?A 3 ) 1 3 +
.
$( /+
) * ) 3 @?A) + - 5
.F <> @?A ) ) * ) 5
• 6 P @?A / 5 V
@?A 0
• /P ) * @?A +*
L : MN ) .
• $- 52&26 P * 7 ) ) @?A +
3 )
• 60 P - $- 52&26 ) +
) @?A .
6 ) 3 )) ) :A
) . 3 )) ) *
. 8 * 7 *
* .
$5 @?A 67 %'$
) 7 + 3 )
. 3 ) ) ) @?A
3 ) * + *L - 5 6 .9 L
3 5
SELECT c.[FirstName],
c.[LastName],
c.[EmailAddress],
c.[Phone],
e.[EmployeeID],
e.[Gender],
a.[AddressLine1],
a.[AddressLine2],
a.[City],
a.[StateProvinceID],
a.[PostalCode]
ON c.[ContactID] = e.[ContactID]
INNER JOIN [HumanResources].[EmployeeAddress] ea
ON e.[EmployeeID] = ea.[EmployeeID]
INNER JOIN [Person].[Address] a
ON ea.[AddressID] = a.[AddressID]
FOR XML AUTO
7 * %5
" #
)) * 7 ) K K3
. L . ) :A 6A6 +* - 5 4$5$&6
. .< * L 3 .
A L + * +3
. * -C < 5
SELECT s.Name AS StoreName,

c.ContactID,
c.ContactTypeID
FROM Sales.Store s
JOIN [Sales].[StoreContact] c ON s.[CustomerID] = c.[CustomerID]
ORDER BY s.[Name]
FOR XML AUTO
%'
"
) .% . @?A ) *5
<s StoreName="A Bicycle Association">

<c ContactID="956" ContactTypeID="11" />
</s>
<s StoreName="A Bike Store">
<c ContactID="322" ContactTypeID="11" />
</s>
+ + )* - 5 / .
- 5 $- 52&26 * 7 ) ) @?A
3 P) 7 + ) 3
) @?A ) + - 5 6 .
8 - 5 $- 52&26+ * 3 *
) @?A + ) 32 3 .9
7 5
SELECT 1 AS Tag,
NULL AS Parent,
s.Name AS [Store!1!StoreName],
NULL AS [Contact!2!ContactID],
NULL AS [Contact!2!ContactTypeID]
FROM Sales.Store s
JOIN [Sales].[StoreContact] c ON s.[CustomerID] =
c.[CustomerID]
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
s.Name AS StoreName,
c.ContactID,
c.ContactTypeID
FROM Sales.Store s
JOIN [Sales].[StoreContact] c ON s.[CustomerID] = c.[CustomerID]
ORDER BY [Store!1!StoreName],
[Contact!2!ContactID]
FOR XML EXPLICIT
7 ) 3 * 7 * (5
$5 @?A 67 %'#
" $
) . #. @?A + +
) *5
<Store StoreName="A Bicycle Association">

<Contact ContactID="956" ContactTypeID="11" />
</Store>
<Store StoreName="A Bike Store">
<Contact ContactID="322" ContactTypeID="11" />
</Store>
) - 5 $- 52&26 L +
) )@?A +
* + ) ) . )) L
7 + .8 <> @?A 6@ A @?A+ *
+ ) .
6 * 7 + *+ ) 3
+* @?A + )
+ C" <" 7 +
) )3 3
.
- 7 ) - 5 6 * ) 6 $
) 3 @?A . ) *
3 + 7 7
* :A 45
SELECT s.[Name] AS StoreName,

( SELECT c.ContactID,
c.ContactTypeID
FROM [Sales].[StoreContact] c
WHERE c.[CustomerID] = s.[CustomerID]
FOR
XML AUTO,
TYPE,
ELEMENTS
)
ORDER BY s.[Name]
FOR XML AUTO,
TYPE
$5$ $364 ) *
* + ) ) @?A5
%4
<s StoreName="A Bicycle Association">

<c>
<ContactID>956</ContactID>
<ContactTypeID>11</ContactTypeID>
</c>
</s>
7 )) + * '5
" %
) . (. * <- . C,@
7 @ - 9 @:C6>F .@ - 9
@:C6>F * )) * 3 @?A . + 7
* *+ * C,@ * )
@?A 5
" &
$% A**K+ * ) * ) 4 & 5
& 2< & 6 2<. 1 * )
7 4 . 7 C,@ * 1
$5 @?A 67 %4%
* 3 +
) @?A ) / 0+ ) )
) ) @?A.
+ - 5 60 @?A
7 .C + 3 *L *
* * 5
SELECT s.[Name] AS "@StoreName",

c.[ContactID] AS "StoreContact/@ContactId",
c.[ContactTypeID] AS "StoreContact/@ContactTypeID"
JOIN [Sales].[StoreContact] c ON s.[CustomerID] =
c.[CustomerID]
ORDER BY s.[Name]
FOR XML PATH
7 * + *
/.% 0. @?A ) *5
<row StoreName="A Bicycle Association">

<StoreContact ContactId="956" ContactTypeID="11" />
</row>
<row StoreName="A Bike Store">
<StoreContact ContactId="322" ContactTypeID="11" />
</row>
<) ) @?A +
7 * ) * :A.
* 7 ) ) @?A .
) @?A ) + ) :A
@?A .
) + @?A ) 3 ) * +
)* @: @ .8 +
+ > + ) @?A - 9 @?A -C <
* 5
9 * + ) + @?A -C < L *
+ L @?A - 9 ) .
@?A 6@ A ) * *
5
@?A -C < * F 6* ) C,@ +

) 5
$ %/+
@?A* :A + < 6"@?A @: . < 6"@?A
@?A ) +) * +
) * . * * :A . L
%4
) * ) @?A )
* = . +* @?A .
<ROOT>
<Currency CurrencyCode="UTE" CurrencyName="Universal Transactional Exchange">
<CurrencyRate FromCurrencyCode="USD" ToCurrencyCode="UTE"
CurrencyRateDate="1/1/2007" AverageRate=".553" EndOfDateRate= ".558" />
CurrencyRateDate="6/1/2007" AverageRate=".928" EndOfDateRate= "1.057" />
</Currency>
</ROOT>
+*L * + C 67 + *
* C 6. 8 7 ) C 6 C ,. 8 L
+ + + ) @?A. 9 L 5
BEGIN TRAN
DECLARE @iDoc AS INTEGER
DECLARE @Xml AS NVARCHAR(MAX)
SET @Xml = '<ROOT>

<Currency CurrencyCode="UTE" CurrencyName="Universal Transactional Exchange">
CurrencyRateDate="1/1/2007" AverageRate=".553" EndOfDayRate= ".558" />
CurrencyRateDate="6/1/2007" AverageRate=".928" EndOfDayRate= "1.057" />
</Currency>
</ROOT>'
EXEC sp_xml_preparedocument @iDoc OUTPUT, @Xml
INSERT INTO [Sales].[Currency]

(
[CurrencyCode],
[Name],
[ModifiedDate]
)
SELECT CurrencyCode,
CurrencyName,
GETDATE()
FROM OPENXML (@iDoc, 'ROOT/Currency',1) WITH ( CurrencyCode NCHAR(3),
CurrencyName NVARCHAR(50) )
INSERT INTO [Sales].[CurrencyRate]

(
[CurrencyRateDate],
[FromCurrencyCode],
[ToCurrencyCode],
[AverageRate],
[EndOfDayRate],
[ModifiedDate]
)
SELECT CurrencyRateDate,
FromCurrencyCode,
ToCurrencyCode,
AverageRate,
EndOfDayRate,
GETDATE()
FROM OPENXML(@iDoc , 'ROOT/Currency/CurrencyRate',2) WITH (
CurrencyRateDate DATETIME '@CurrencyRateDate', FromCurrencyCode NCHAR(3)
'@FromCurrencyCode', ToCurrencyCode NCHAR(3) '@ToCurrencyCode', AverageRate MONEY
'@AverageRate', EndOfDayRate MONEY '@EndOfDayRate' )
EXEC sp_xml_removedocument @iDoc

ROLLBACK TRAN
3 + * * 7 + ) 234$ 6. )
234$ 6 & + * &5
$5 @?A 67 %4(
" )
- 3 ) * @?A . - < 6"@?A
* 4 . )
,AA * :A + * * @?A ) *
) 3 . >
) 3 )+ 3
.
67 ) 7 @?A
3 5 @?A 7% #7 7% #7.
' ) :A + $.
" *
* * ) @?A < A )
4 .9 + +* < 6"@?A ) +
) @?A .
"
+ L ) ) * 3 * ) )
7 ) 7 .
7 234$ 6 & 5
" +
3 ) + ) 7 3 )
) ) * & & . F
+ * @?A > )
* * ) . + *
? E . + )
%4'
& & 6 & & )

? E + < = .
L @?A ) * 3 + )
* . - + < 6"@?A ) )
* @?A * +
. * ) . 7 +
) 1 4$5$&6 .
* < 6"@?A ) + )
* 1 * 3 ..
< * 5 @?A ) . F
@?A+ + @?A
. * ) *
.
/ !
- * ) @?A :A 4+
) @: ) 3 @?A . 6)) + )
@: * *3 :A. @?A
@: ) :A
.8 * 3 ) @?A + ) +
)* ) 7 )) * 5
• 8 (+5 3 7 7
• 8' # (+5 3 7 7
• 8 (+5 ) 7 *
• 8 % (+5 3 7 ! *
+1 $-2464 * :A
• 8 (+5 ) + @?A *
@?A .
) 3 @?A+ ) A8 <> /) + +
* + 0 * 3 + )) 7 . L
1 * 7 3 *
)7 7 . L )
* 3 ) * .
'
8 % 3 )3 * * * @?A
. ) * 7 +*L 3 ) )
* ) * 5
c.[FirstName],
e.[HireDate],
e.[Title]
INNER JOIN [HumanResources].[Employee] e ON c.[ContactID] = e.[ContactID]
INNER JOIN [HumanResources].[JobCandidate] jc ON e.[EmployeeID] =
jc.[EmployeeID]
AND jc.[Resume].exist('
declare namespace res="http://schemas.microsoft.com/sqlserver/2004/07/adventure-
works/Resume";
/res:Resume/res:Employment/res:Emp.JobTitle[contains(.,"Sales Manager")]') = 1
$5 @?A 67 %44
3 + +) * * ) +
7 % 5
" #
+ +* 7 . - &#
2 %4 1 & ) * #
> ) .-3 5 1
) ) 1 & * ) $ # +)
* * *.
+ 3 5 ) * + 6 #
=# . B K@?A > * @ ) K.
) @: .
) > ) < 6"@?A 3 . 9 * +
B+ > 7 + ) 3
.
) B * ) * * ) 5
" #%#
* # ) @ 3 *
) 3 . *) 3 5 .
L 7 + ) &
* ) .
%4&
' 2 !
8 @?A. 7 +*L 3 , )
) =. 3 +* )
@?A + + 3 @: L A8 <> 7 / + A +
8 +< + > 0. 7 )@: +
:A5
• P @?A . 7 )
+ + + 3 + 94 4 ' . *
Q6
• / P / 7 . * 1
/0$ $ :A
• P +1 <$ :A/ 0
• P ) + ) 4$5$&6
:A 7 )@?A ) ) .
# 7 +* :A 4.
7 +* )
. ) * * ) )
3 ) . )
+ * @?A . ) @?A +*L
8 .A L 7 3 7 5
SELECT s.Demographics.query('
declare namespace
ss="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
for $s in /ss:StoreSurvey
where ss:StoreSurvey/ss:SquareFeet > 20000
return $s
') AS Demographics
WHERE s.[SalesPersonID] = 279
" #
3 )* 3
• - :A3 4 * *
4 # 2 E ,OP+
• -3 .3 * L 3 )
*
* + + * * * 3
..
- * + ) % . ) &# 2 %
4 +) 4 # 4 # 2. )
) ) 3 5 + ) 1 . )
) ) 1 +* ) ) 5 &# 2 %4 . +
L @?A 7.
$5 @?A 67 %4$
" #$
F %( 7 - 574 7< ! +
* ) 7 & 2 ) ) .
! * + &# 2 %4 & 2+
4 ) . )
A )E .
+ ) 1 ) @?A )
<- ) @?A . *
* ) 4 . <) ) @: )
) * :A. ) @?A
7* * * ) + ) )
) .
!
7 L )* L * @: .
) @?A .F ) :A @:
. * * 7 L
)* )) . +
* )) ) ? ) / 5QQ . ). Q
Q Q ('4% . 70. ) <> @?A+
) .
%4
F @: ) < 6"@?A. ) @:
* * L * < 6"@?A. * :A *
) * ) * @?A * :A .-
* + * L
) .
$5 @?A 67 %4#
%&
(D. 3% $ ,
+ * * 7 + *
7 ) 73 +
) :A 1 + + *+ 7 +
. ) +* * * * * 7
* 7 * .
) +* * ) )
)7 + ) * 5
• A 7 *
• P* * + * 3
7 *
• = P *
7 = ) .C 1
) + + :A.
• C P 7 3 *
R * * .
• C P 7 + ) *
) =.
(
* L * 7
) + ) + L
. = )1 + )
7+ +* L * * * .
9 * + ) = +* ) * 7
+ L ) ) 7 .
+ 7+ * *
= +* + * + .
A L * L 7 / L
0. ) * +
* )) * . + )
)) 3 + 7 )) 3
+ )) .
DROP PROCEDURE [Sales].[uspGetDiscountRates] ;

GO
CREATE PROCEDURE [Sales].[uspGetDiscountRates]
(
@ContactId INT
,@SpecialOfferId INT
)
AS
BEGIN TRY
-- determine if sale using special offer exists
FROM [Sales].[Individual] i
INNER JOIN [Sales].[Customer] c
ON i.CustomerID = c.CustomerID
INNER JOIN [Sales].[SalesOrderHeader] soh
ON soh.CustomerID = c.CustomerID
INNER JOIN [Sales].[SalesOrderDetail] sod
5- %&%
ON soh.[SalesOrderID] = sod.[SalesOrderID]
INNER JOIN [Sales].[SpecialOffer] spo
ON sod.[SpecialOfferID] = spo.[SpecialOfferID]
WHERE i.[ContactID] = @ContactId
AND spo.[SpecialOfferID] = @SpecialOfferId )
BEGIN
SELECT c.[LastName] + ', ' + c.[FirstName]
,c.[EmailAddress]
,i.[Demographics]
,spo.[Description]
,spo.[DiscountPct]
,sod.[LineTotal]
,p.[Name]
,p.[ListPrice]
,sod.[UnitPriceDiscount]
INNER JOIN [Sales].[Individual] i
ON c.[ContactID] = i.[ContactID]
INNER JOIN [Sales].[Customer] cu
ON i.[CustomerID] = cu.[CustomerID]
ON cu.[CustomerID] = soh.[CustomerID]
INNER JOIN [Sales].[SpecialOffer] spo
ON sod.[SpecialOfferID] = spo.[SpecialOfferID]
INNER JOIN [Production].[Product] p
ON sod.[ProductID] = p.[ProductID]
WHERE c.ContactID = @ContactId
AND sod.[SpecialOfferID] = @SpecialOfferId;
END
-- use different query to return other data set
ELSE
BEGIN
SELECT c.[LastName] + ', ' + c.[FirstName]
,c.[EmailAddress]
,i.[Demographics]
,soh.SalesOrderNumber
,sod.[LineTotal]
,p.[Name]
,p.[ListPrice]
,sod.[UnitPrice]
,st.[Name] AS StoreName
,ec.[LastName] + ', ' + ec.[FirstName] AS SalesPersonName
INNER JOIN [Sales].[Individual] i
ON c.[ContactID] = i.[ContactID]
ON i.[CustomerID] = soh.[CustomerID]
ON sod.[ProductID] = p.[ProductID]
LEFT JOIN [Sales].[SalesPerson] sp
ON soh.SalesPersonID = sp.SalesPersonID
LEFT JOIN [Sales].[Store] st
ON sp.SalesPersonID = st.SalesPersonID
LEFT JOIN [HumanResources].[Employee] e
ON sp.SalesPersonID = e.[EmployeeID]
LEFT JOIN Person.[Contacct] ec
ON e.[ContactID] = ec.[ContactID]
WHERE i.[ContactID] = @ContactId;
END
--second result SET

IF @SpecialOfferId = 16
BEGIN
SELECT p.[Name]
,p.[ProductLine]
FROM [Sales].[SpecialOfferProduct] sop
ON sop.[ProductID] = p.[ProductID]
WHERE sop.[SpecialOfferID] = 16;
END
%&
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage ;
RETURN ERROR_NUMBER() ;
END CATCH
RETURN 0 ;
;
) * ) 3 .
) 3 + .
! ) + 3 )
)) 3 .
C ) + ,!- .
8 L 7 * ) * 5
EXEC [Sales].[uspGetDiscountRates]
@ContactId = 12298, -- int
@SpecialOfferId = 16 -- int
) 5
" #&
) + *
* + )
3 ) )) 3 . 7
* 5
5- %&(
"
< + * * . 9 * + )
*+ ) 3 . ) )
) 3 ) 7 ) )) . + )
3 * )) . +
) 3 +* * 4 # 2< V
%&.
8 7 + * L
* . L 1 ) . )
= ) * .
* * .
F * * * .
* ) 3 * *+ * L 7 +
L + * (5
" $
* *+ * )
7 .D + * *.
F L K * K + * '5
%&'
" %
- * + L
. * + 7
)* * 7 ) + * *
L .
- )+ * 1 ) )
3 /& S0+ * + &# 2 %4
4 # 82 ' # ." ) 7 7 & 2<
+ * = ) 3 .-
7 ) ) 3 .
8 * + @?A
@?A * 7 .!
* + + )) 7
) + * @?A * L 7 . )
L 1 * 7 + * )
@?A+ * .
+ ) 7 )) )
R 1 ) ., L .E
+ ) * * +
* * + ) .
:A ) ) *
. L
. ) )+
* + 5
• K %< ! ### K+ * ) :A
* * 7 3 .! ) K K+ *
.
• K& 6 # ### K+ * ) + +
* :A .
) * 3 * . ) K4K.
+
:A * ) 3
/ 0. ! ) + * .
) * 7 + )K%K. ) *
5- %&4
) ) ) 3 7 + )
+ &'.%(
- !<A / 5QQ . ). Q Q Q % % $. 70+
) 7 ! + ) *5
sp_configure 'show advanced options', 1;

GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 3;
GO
RECONFIGURE WITH OVERRIDE;
GO
- = * 7 +*
) ) * . ) K
) K ) .
= ) *5
• < ' ' # # Q 3
.
• ' # #Q
* + :A * <
) ) .
• / % ! Q:
+ ) 1 * +
+ ) . L
.
• : Q ) *
)) ) +*
.
• Q, + )
= + *
* ) .
8 = 3 * ) ) 7 +
+ % ! .
* ) + +
.
8 7 + ' 5
) L ) .8 +
7 ) ) . 3 + 7
+ * * +
) .
%( + )) )
3 ?-@,< 3 + 4.
%&&
& 0
.
* .
+ + 3 + ) .
F L ) +* .
<A ) + * 1 ) ) +
* * * .-3 * *
7 * .
) ) * * )+ * *
. 7 )3
+ * +
+ -< 3 .
* 7 )
) ) ) . :
7 * )
) 7 . ) * * -< % + L
. ) * )
(+ L )) 7 .
)+ + L ) * + * L
.D , )
:A ? = +2 < RP)KPO / 0.
:A ) ? + :A
.- K K+ * )
* .F . 7
+ 7 .
+ .9 * + L * ) 5 '
.
8 L * 3 + . )
3 * + ) ) .
9 L 3 5
SELECT [so].[ProductID]
,COUNT(*) AS Order_Count
FROM [Sales].[SalesOrderDetail] so
WHERE [so].[ModifiedDate] >= '2003/02/01'
AND [so].[ModifiedDate] < DATEADD(mm, 3, '2003/02/01')
GROUP BY [so].[ProductID]
ORDER BY [so].[ProductID]
)* 7 +*L ) ) *
* 45
" &
* L ) + *L * *
) * 7 * ) . )
= + %) *
5- %&$
* /4 ) 0. +* 3 7
5

GO
GO
SET STATISTICS XML ON;
GO
SELECT [so].[ProductID]
,COUNT(*) AS Order_Count
FROM [Sales].[SalesOrderDetail] so
WHERE [so].[ModifiedDate] >= '2003/02/01'
AND [so].[ModifiedDate] < DATEADD(mm, 3, '2003/02/01')
GROUP BY [so].[ProductID]
ORDER BY [so].[ProductID]
GO
SET STATISTICS XML OFF;
GO
K - 67 K @?A
) . 7 * &5
" )
) * 1 + *
+ * * * *+ *
* ) . *
. )* 7 @?A +* *
5
<QueryPlan DegreeOfParallelism="2" MemoryGrant="162" CachedPlanSize="22"

CompileTime="5" CompileCPU="5" CompileMemory="320">
) + < ! ### + 7
) 3 * * ) * . A
7 + *L ) * . - * )
+*L ) 3 * @?A 7 . * ) &#
2 %4 .
) * # &# 2 %4 5
<RelOp NodeId="5" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index

Scan" EstimateRows="4987.95" EstimateIO="0.915718" EstimateCPU="0.0668028"
EstimateRebinds="0" EstimateRewinds="0">
</RelOp>
" ### %+ / =
0. 8 + 6 2
%&
) ) 6 & 6 .
* .
7 # ### 5
<RelOp NodeId="4" PhysicalOp="Parallelism" LogicalOp="Repartition Streams"

EstimateRows="4987.95" EstimateIO="0" EstimateCPU="0.0344762" AvgRowSize="11"
</RelOp>
4 )
) + .
0 * * 2<
) + ) ) ) 5
<RelOp NodeId="3" PhysicalOp="Hash Match" LogicalOp="Aggregate"

- + 6 2 +*L )
.
4 # < 4 < ) * + )
. + * ###
5
<RelOp NodeId="0" PhysicalOp="Parallelism" LogicalOp="Gather Streams"

4 ) ) ))
) . 4
4 + + <
4 +* ) )
.
- 6 2 + )
) +*L * * .
5- %&#
< L 7 * +
* * / ) 40.

GO
GO
4 1
= ) ) = + 7
)* * * . . = . 8
) * 3 +
K- V 4 K+ 3 * .
7 * * * <$5$6$ 5
DELETE FROM [Person].[Address]

WHERE [AddressID] = 52;
&# 2 %< # )
) 4 + )
5
" *
) = ))
. = ) ) 3 .
= +
L 1 + * .
%$
= ) ) .
+ +)
) 7 ) 3 3 7. - 3 7
* = .
* = . )
+ + :A
* +* ) * 7 5
• A ) 4$5$&6
• = * :A
+ C, + * 7 ) * .
• @: *
- ) 7 7 ! < .
) ) = .6
* * = +
+
7 . * .
+ L ) = . )
= 3 - )
+ 7 . ) + )
= 7 .
* * * ;- *
) )) ) = .
9 * * ) :A +
+ . ) )
) ) = .- * ) )
) = 3 + .
" + ) = . F )
) 3 3 $6$ 2G 62 3
&$<+ +* * 7
.
7 +* ) 3 +* )
* +L L5
SELECT 42 AS TheAnswer
,c.[EmailAddress]
,e.[BirthDate]
,a.[City]
JOIN [HumanResources].[EmployeeAddress] ea
JOIN [Person].[Address] a
JOIN [Person].[StateProvince] sp
ON a.[StateProvinceID] = sp.[StateProvinceID]
WHERE c.[EmailAddress] LIKE 'david%'
AND sp.[StateProvinceCode] = 'WA' ;
> 3 + L / %) 05
SELECT [cp].[refcounts]
,[cp].[usecounts]
,[cp].[objtype]
,[st].[dbid]
5- %$%
,[st].[objectid]
,[st].[text]
,[qp].[query_plan]
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp ;
3 * 3 * * . * + =
. 7 * 5
"
A L * ) = )) *L
* 7 5
ALTER DATABASE AdventureWorks

SET PARAMETERIZATION FORCED
GO
DBCC freeproccache
GO
) 3 + L 7 *
. 9 * + 3 . *
/) ) 05
(@0 varchar(8000))
select 42 as TheAnswer
,c.[EmailAddress]
,e.[BirthDate]
,a.[City]
from [Person].[Contact] c
join [HumanResources].[Employee] e
on c.[ContactID] = e.[ContactID]
join [HumanResources].[EmployeeAddress] ea
on e.[EmployeeID] = ea.[EmployeeID]
join [Person].[Address] a
on ea.[AddressID] = a.[AddressID]
join [Person].[StateProvince] sp
on a.[StateProvinceID] = sp.[StateProvinceID]
where c.[EmailAddress] like 'david%'
and sp.[StateProvinceCode] = @0
- + ) 3 + + F *' (B***+.
+ ) * * 3 ) +
4 ' & ) . ))
) + + *
& 46 ) B-> 9->/ 0 9->/(0.
%$
9 * + + ) 3 * ))
* + * .
!) + = ) - * 5
ALTER DATABASE AdventureWorks

SET PARAMETERIZATION SIMPLE
GO
' + !
) * *L ) + )* * )
3 * :A + ) 7+ 3 +
) .
8 + * + * L * *
:A + * 7 ;
* # . * )
3 3 * :A * .
) 1 + ) :A
) 1 .
3 5 7
* * = * 3
L ) * .
F 7 + 7 7 # 7!
) 5
• > P +) ,?A
• 4?5 P :A +*
7 ) 1
• 6 # P ) ' 3 =
$#E
A L ) *L - 8 +
8 ! $ # + ) .
+ + $& 25$
7 ) .9 * +
L . + *
* .
A L L 5
EXEC sp_create_plan_guide @name = N'MyFirstPlanGuide',

@stmt = N'WITH [EMP_cte]([EmployeeID], [ManagerID], [FirstName], [LastName],
[RecursionLevel]) -- CTE name and columns
AS (
SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], 0 --
Get the initial list of Employees for Manager n
WHERE [ManagerID] = @ManagerID
UNION ALL
[RecursionLevel] + 1 -- Join recursive member to anchor
INNER JOIN [EMP_cte]
ON e.[ManagerID] = [EMP_cte].[EmployeeID]
5- %$(
)
-- Join back to Employee to return the manager name
SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[ManagerID], c.[FirstName] AS
''ManagerFirstName'', c.[LastName] AS ''ManagerLastName'',
[EMP_cte].[EmployeeID], [EMP_cte].[FirstName], [EMP_cte].[LastName] --
Outer select from the CTE
FROM [EMP_cte]
ON [EMP_cte].[ManagerID] = e.[EmployeeID]
ORDER BY [RecursionLevel], [ManagerID], [EmployeeID]
OPTION (MAXRECURSION 25) ', @type = N'OBJECT',
@module_or_batch = N'dbo.uspGetManagerEmployees', @params = NULL,
@hints = N'OPTION(RECOMPILE,MAXRECURSION 25)'
+ * F +
# ." * 7 ) +
.
F 7 3 3 = *
.8 L +
+ 6. 8 * .8 = )
+ * .
F 1 + * )
1 .
F #7 7 +* ) ) 1 + )* L
1 + .8 * .
8 F )* L ) = .
*L + L . )* * *
) .
+ F ) . 8
$& 25$ + 3 + - $& 42 3.
) F * *
. 7 62 3R )+ +*
7 62 3 ) * +* .
) * + * ) )
+ 7 ) * ) * .
A L 7 .A L *L
:A .< +*L )
*L ) )* 62 2G$ 3 +*L
7 *L .
) * 3 +* * + )
) 45
SELECT * FROM [Person].[Address]

WHERE [City] = 'LONDON'
4+ * * * ) ) 3
62 2G$ (F & E 3 : ++ 3 L )
:A 5
%$'
EXEC sp_create_plan_guide @name = N'MySecondPlanGuide',

@stmt = N'SELECT * FROM [Person].[Address] WHERE [City] = @0',
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@0 VARCHAR(8000)',
@hints = N'OPTION(OPTIMIZE FOR (@0 = ''Newark''))'
) ) ) 3 * = *
+ L 3 7! 7 7 # .
= 3 * ) * *L
* 3 * * = 5
DECLARE @my_templatetext nvarchar(max)

DECLARE @my_parameters nvarchar(max)
EXEC sp_get_query_template @templatetext = N'SELECT * FROM [Person].[Address]
WHERE [City] = ''LONDON''',
@templatetext = @my_templatetext OUTPUT,
@parameters = @my_parameters OUTPUT
select @my_templatetext
SELECT @my_parameters
* 5
select * from [Person] . [Address] where [City] = @0
@0 varchar(8000)
F * * 3 .
" ** * 3 +* ) 3 +
* 7 * * + * #5
" +
"
- ) 7 + 3 * )
= . )* *
= ) + * # # ! +
.
F * 7! 7 7 # ) * 3
* = .
F + ) *5
EXEC sp_create_plan_guide @name = N'MyThirdPlanGuide',

@stmt = N'SELECT 42 AS TheAnswer
,c.[EmailAddress]
5- %$4
,e.[BirthDate]
,a.[City]
JOIN [HumanResources].[EmployeeAddress] ea
JOIN [Person].[Address] a
JOIN [Person].[StateProvince] sp
ON a.[StateProvinceID] = sp.[StateProvinceID]
WHERE c.[EmailAddress] LIKE ''david%''
AND sp.[StateProvinceCode] = ''WA''',
@type = N'TEMPLATE',
@module_or_batch = NULL,
@params = N'@0 VARCHAR(8000)',
@hints = N'OPTION(PARAMETERIZATION FORCED)'
) * + )
*+ 8 # 7! 5
SELECT * FROM sys.plan_guides
- ) + 7 +
7 #7 # 7! +* * + ) +
+ .
7 7 #7 # 7! + F
.
EXEC sp_control_plan_guide @operation = N'DROP'

,@name = N'MyFourthPlanGuide'
!
8 7 *L ) ., L ) +
) . L *
) . ) * *
* ) 1 *
.
' + !
4$ 5 3 3 + :A 4+ *
3 7 . *
7 + @?A+ K K+ ) 7 +
3 ) ) * .
F ) 5
• " 6> + C ,- 6 ,6A6 6 3 .
• : ) I) *
• , 3 ) 73
+1 3 + ) .
4$ 5 3.
%$&
8 @?A 3 3 + @?A 7
. ) 7 = 3 +
3 7 .
+ 4$ 5 3+ * + 3 3 *
+ ) . +
3 ) * .
* 7 ) 3 ) ) )
4 # 0 5
CREATE PROCEDURE Sales.uspGetCreditInfo ( @SalesPersonID INT )

AS
SELECT soh.[AccountNumber]
,soh.[CreditCardApprovalCode]
,soh.[CreditCardID]
,soh.[OnlineOrderFlag]
WHERE soh.[SalesPersonID] = @SalesPersonID
8 ) F4 # 2< E ,OO+ 7
+ 3 .
" #
) + 7 * .
" ##
) 7 . ) 7 )
) ) + 7 .8
* 62 2G$ +*L
4$ 5 3 .
+* @?A * * * .8
4$5$&6 ) )
* . . +*L * *
46 62462&4 - 5+ * * 7 @?A5

GO
SELECT soh.[AccountNumber]
,soh.[CreditCardID]
WHERE soh.[SalesPersonID] = 288;
GO
5- %$$

GO
3 % $ @?A +* * L .8
@?A +*L /
= ) @?A+ L ) )) ) * 05
EXEC sp_create_plan_guide
@name = N'UsePlanPlanGuide',
@stmt = N'SELECT soh.[AccountNumber]
,soh.[CreditCardID]
WHERE soh.[SalesPersonID] = @SalesPersonID --288 --277',
@type = N'OBJECT',
@module_or_batch = N'Sales.uspGetCreditInfo',
@params = NULL,
@hints = N'OPTION(USE PLAN N''<ShowPlanXML…
" *+ * 3 7 5
EXEC [Sales].uspGetCreditInfo @SalesPersonID = 277
8 7 * * + * % 5
" #
- ) 5 + 4$ 5 3+
) * .-
* 7 + 7
* *.
!
8 ) + 4$ 5 3I L
= 3 .! ) +
L ) * 3 * .
)* . + *
= L
* 3 ) ) =
.E )
) .
%$
,% /
- " )> * + %% - AF+ $

- 8 96>6 +4 , + ( + %&
= + %$+ % 6 + ( + &$
- - @?A + % 6 + $
- 6 @?A +$ 6
- + &4+ &$+ &# +
- * :A ) + %&+ 6 67 + $(+
( + ((+ %%%
6 > * + #+ (%+ $(+ + %&$+ %&
! + ( + $#+ %%'
6 67 + ('
! 3 + ( + $#
67
! A + #4
- 67 + % + (+ #$+ %'%+ %' +
= + (%+ $#+ %+ %&$ %&$
7 6 67 + + (+ % #+ %(#+
%'
+ ($+ ( + (#+ 4&+ 4 + & + &%+ '+ 4+ #+ #'+
##+ % '+ % 4+ %% + %%(+ %%4+ %%$+ %%#+ % %+ 6 + ((
% 4+ % &+ %( + %44+ %4&+ %&'+ %&$
+ %
, +&
- + (
+ ($+ (#+ ' + '%+ 4%+ 4'+ &&+ & + &#+ $(+ $4+
6 + (
+ &+ #+ % &+ %(&+ %4&+ %4$
+ %+ + '+ ( + ($+ % %
C + &$+ %(
67 + '
7 + &&+ %(4
>3 B *+
>) + (%+ ( + + %
> +%
67 +#
7 + %+ $+ + $%
<? C 6+ % (
7 +
+ ( + 44+ &&+ &$+ %%(+ %( + %(4+
%( + %'% 7 +
+ #%+ # + % 4+ % & 7 +
+ ( + &&+ %( @?A + %+ #+ ( + ( + $%+ $$+ $ + %+ % %
+ %$ @?A + #
>< - AF+ $+ %$% @?A +(
+ %( 67 * ><C !F <>,6>
!F+ &
,! 9<8 I - + # + ##
- I <>8->,+ %' + %'(
+
6 9 C> <>+ %( + %((+ %('+ %( + %'%
, ) B + $+ ( + $(
6 9 "6@ + % + %( + %((+ %( + %'
, <) + %
<> !><8 6+ %#
, +&
= + %&#
, + '+ $
7 %$#
<>8->,I<"AF+ %'( " A + ( + '4+ '&+ '#+ 4(+ 44+ 4&+ 4#+ &&+
&$+ &#+ $ + $(+ $4+ $&+ $$+ + %+ '+ $+ + #%+
+ ( + %&
% $+ %% + %%$+ % + % %+ % + % 4+ % &+ % $+
9- 9 ><C + % ( %(
1 +( +% $ " A + ( + '4+ '&+ '#+ 4(+ 44+ 4&+ 4#+ &&+
&$+ &#+ $ + $(+ $4+ $&+ $$+ + %+ '+ $+ + #%+
9 ? + ( + 4%+ 4 + 4(+ 4'+ 4$+ & + &'+ $4+
% $+ %% + %%$+ % + % %+ % + % 4+ % &+ % $+
% '+ % 4+ % &+ % $+ %% + %%(+ % + % %+ % +
% &+ %& %( + %44+ %4&
E + 44+ 4&+ 4#+ &#
9 ? /- 0+ & + % '
" A E + 44+ 4&+ 4#+ &#
9 ? /E 0+ 4%+ 4 + 4(+ 4'+ $4+ %%(+ % +
% % " 7 + ( + '%
+ 4(+ &(+ % '+ % 4+ % < 6" C> <>+ %( + %(&+ %(
9- 9 C" <"+ % & < ? H6 <>+ %%(+ %%'+ %%4+ %$(+ %$'+ %$&
+ 4(+ % (+ % '+ % 4 < <" /6@ -", B 68 0+ %%#
+ '#+ #4+ ##+ % % <>,6> ><C + % (+ % '+ % 4
7 3. 7 + %+
- ! A + #4 <C 6> - AF+ $
7 + ($+ ( + (#+ 4%+ 4&+ 4 + & + &%+ &(+
&$+ & + $4+ '+ 4+ #+ #'+ ##+ % '+ % 4+ %% +
- ! ;+ %&&
%%(+ %%4+ %%$+ %%#+ % %+ % 4+ % &+ %( + %(4+
%(#+ %&$ ) + %&'+ %&4
7 + ($+ ( + (#+ ' + '%+ '(+ '&+ ' + '#+ , <) + %+ %&$
4%+ 4'+ &&+ & + &#+ $(+ $'+ $4+ + '+ &+ +
67 67 + %&&
#+ #%+ #&+ % %+ %%$+ % 4+ % &+ %(&
67 + %&'
7 +#
> + ( + %&
7 + &4+ #
> + %&
7 + +%
3 + %
+ &&
+ % + %#
1 + % &+ % + %
+ $$+ %& + %$4
E 9 + % (+ %
+ %%&+ %& + %$
D A C + ' + '4+ $(+ #&
- + %$4
A ! + (%
< 1 + %$
A E + % $+ % + %% + % %
:A + %$(
? ? + 4
+ %$'
? 7
C + %%&+ %& + %$
, ) + %% + %&'
A-"I><8+ $ + $(
? + %+ %&$
+% + 4
? E + ( + 4&+ 4$+ 4+ % &+ %%#+ % + %4(
: 67 +%
?6> 6 C" <"+ % 4
: 9 +% (
? 7 + $#+
6@ -", B 68 + %% + %%#+ % (
%
- + % #+ % 4 9<8 A-"I@?A+ %+ #+ ( + ((+ $ + %

<> 6 <>,6>+ %% + %%%
9- 9G<>,6> ><C + % ( D6F 6 + %($
D66 A-"+ %% A < + % #+ %('+ %($
D66 @6, A-"+ %%(+ %%'+ %% ) + %(#
A<< G?6> 6G9- 9 E< "+ % & < + %('+ %(&+ %(
?-@,< + %%%+ %% + %%(+ %&4+ %&& >6-,I<"AF + %(#
?-@>6 C> <"+ %%#+ %$( - + %('
?6> 6 G9- 9 G <" - C" <"+ % 4 +
< ? H6 <>+ %%(+ %%'+ %%4+ %$(+ %$'+ + %(&
%$&
+ ($+ & + &%+ & + &'+ &4+ '+ % 4+ % &+ %%(+
->-?6 6> H- <" %%#+ % %+ % + %&
? A6G <> 6,+ %%&
I I I + %$4
>6 <? A6+ %%(+ %%&+ %%$+ %$ + %$(
I I I + %$ + %$'+ %$$
><!C A-"+ %%
I I3 I + %$'
C 6 A-"+ %%#+ %$4+ %$&+ %$$
I + %#+ %%
: < = + %&+ %$+ 4
I7 I + %4 + %4(
: < +% $
I7 I + %4 + %4(
: + %&
67 + &4
3 + %$+ %
- @?A+ ( + ((+ %+ #%+ #$+ % + % %+
: + ( + (%+ $#+ %+ %&$ %&$+ %$&
> A 67 + %& + ( + $#
> >* 67 + &' 7+ $ + $(+ $4
>6 <? A6+ %%(+ %%&+ %%$+ %$ + %$( + (
> A + #%+ %$ - + ( + % 4+ % &
3 +# . I + %$4
> < + (%+ + %&$+ %& 9 + #4+ % (+ %
> + ( + %& - > ><8+ % 4+ % &
> +( +$ ",6@/0+ % '
>,A C + '$+ ' + '# "<6@ -",+ #4+ %% + % (
> ) + %+ # + %&$+ %& 9 7+ % (
+ 4 + 44 E +4 +4
.+ %$+ 4 + # + ##+ % + %%4 + 4+ (%+ ( + ($+ ( + '&+ % %
3 1 + %(4 + &4+ &$+ #%
6 - <+ B + %44
9<8 I - + # + ## + %$(+ %$'
9<8 A-"I-AA+ %+ + #+ (%+ ((+ $ + $' 7 67 + $+ + $%
9<8 A-"I-AA< + + $ + $' 7, + (4
7 % %
7 > + %4(+ %44

: + $% @?A-C <+ %'$+ %' + %'#+ %4%
7: + $( @?A6@ A + %' + %'#+ %4%
+ #+ $( @?A>-8+ %'
+ %$ @?A67 + + #+ $%+ $$
+ (+ $ + #4 @?A 67
C" <" -AA+ #%+ %' + %$ <> @?A+ %'&+ %'#+ %4$
3 ) + && < 6"@?A+ %'&+ %4%+ %4 + %4(+ %4'+ %44+ %4
C , 67 + ($+ &&+ %(4 @: + %'&+ %4%+ %4'+ %44+ %4&+ %4$+ %4 + %$
C + &$ @?A 67 + %'&
B * @:
7 B * + #(+ % ( C 7 + %4'
B *+# C 3 + %4&
@?A
%

Ebook Dissecting SQL Server Execution Plans

Transféré par

Droits d'auteur :

Formats disponibles

Ebook Dissecting SQL Server Execution Plans

Transféré par

Informations du document

Titre original

Copyright

Formats disponibles

Partager ce document

Partager ou intégrer le document

Options de partage

Avez-vous trouvé ce document utile ?

Ce contenu est-il inapproprié ?

Droits d'auteur :

Formats disponibles

Ebook Dissecting SQL Server Execution Plans

Transféré par

Droits d'auteur :

Formats disponibles

The SQL Server Performance Series

ISBN: 978-1-906434-01-4 www.simpletalkpublishing.com

(-. / ' ,$% %& ,

CREATE TABLE TempTable

INSERT INTO TempTable ( Dsc )

DROP TABLE TempTable;

Msg 208, Level 16, State 1, Line 7

GRANT SHOWPLAN TO [username]

SET SHOWPLAN_ALL ON;

L +* 9<8 A-"I-AA <"+ 7 )

SET SHOWPLAN_ALL OFF;

SET STATISTICS PROFILE ON

SET STATISTICS PROFILE OFF

SET STATISTICS XML ON

\Microsoft SQL Server\90\Tools\Binn\schemas\sqlserver\2003\03\showplan\showplanxml.xsd

<StmtSimple StatementText="SELECT * &#xD;&#xA; FROM

StatementType="SELECT" StatementSubTreeCost="0.108154" StatementEstRows="389"

<RelOp NodeId="0" PhysicalOp="Table Scan" LogicalOp="Table Scan"

<TableScan Ordered="0" ForcedIndex="0" NoExpandHint="0">

" * @?A .C 6 %< +

4 ($' &: $( ( &:

INSERT INTO [AdventureWorks].[Person].[Address]

DELETE FROM [Person].[Address]

(<. / % /+ / ' ,$% % $( & ,

SET SHOWPLAN_ALL OFF ;

SET SHOWPLAN_ALL OFF ;

SET SHOWPLAN_XML OFF ;

<RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Inner Join"

<RelOp NodeId="26" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index

SET STATISTICS XML ON ;

SET STATISTICS XML OFF ;

<QueryPlan DegreeOfParallelism="0" MemoryGrant="82" CachedPlanSize="57"

(=. '% ( % ,% +$( $+ / ' (: %

CREATE PROCEDURE [Sales].[spTaxRateByState]

EXEC [Sales].[spTaxRateByState] @CountryRegionCode = 'US'

WHERE [p].[ProductID] = '839'

ALTER PROCEDURE [dbo].[uspGetEmployeeManagers]

SET STATISTICS XML ON;

SELECT sp.[Name] AS [StateProvinceName],

SET STATISTICS XML ON ;

--Recreate original index

EXEC sys.sp_addextendedproperty @name = N'MS_Description',

All density Average Length Columns

CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_OrderQty] ON

DROP INDEX [Sales].[SalesOrderDetail].[IX_SalesOrderDetail_OrderQty]

(>. $% ($ ,% / ' ,$% % *, ,%

SELECT s.[Name] AS StoreName,

OPTION ( LOOP JOIN )

OPTION ( MERGE JOIN )

OPTION ( HASH JOIN )

7 + ?6> 6 E< " )

SELECT pc.[Name] ProductCategoryName,

OPTION (FORCE ORDER)

sp_configure 'cost threshold for parallelism', 1 ;

RECONFIGURE WITH OVERRIDE ;

sp_configure 'cost threshold for parallelism', 5 ;

RECONFIGURE WITH OVERRIDE ;

DECLARE @City NVARCHAR(30)

SET @City = 'Newark'

<StmtSimple StatementText="SELECT *
FROM