tag:blogger.com,1999:blog-69811069144412967192024-03-13T03:12:48.380-07:00SQL SaltRide the SQL Server wave of Data...Anonymoushttp://www.blogger.com/profile/10345205579327231089noreply@blogger.comBlogger4215tag:blogger.com,1999:blog-6981106914441296719.post-89067993065300830022012-09-13T14:17:00.002-07:002012-09-13T14:17:49.847-07:00Blog MigrationI will be migrating this blog to WordPress sometime this week. I will do my best to ensure this is a seamless transition. <br />
<br />
The new blog address will be <a href="http://sqlsalt.wordpress.com/">HTTP://sqlsalt.wordpress.com</a><br />
<br />
Thank you for reading this blog and please continue to do so!Anonymoushttp://www.blogger.com/profile/10345205579327231089noreply@blogger.com0tag:blogger.com,1999:blog-6981106914441296719.post-69488053417493822322012-08-22T20:12:00.002-07:002012-08-22T20:12:29.995-07:00Implement Error Handling<br />
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<i>(This is part of the </i><a href="http://sqlsalt.blogspot.com/p/study-guides.html"><i>Study Guide series</i></a><i>,
70-457)<o:p></o:p></i></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<b><i>Microsoft’s
Measured Skill description</i></b>: This objective may include but is not
limited to: implement try/catch/throw; use set based rather than row based
logic; transaction management</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<b><i>What
I see</i></b>:</div>
<div class="MsoListParagraphCxSpFirst" style="margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l1 level1 lfo1; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;">·<span style="font-family: 'Times New Roman'; font-size: 7pt;">
</span></span><!--[endif]-->try/catch/throw</div>
<div class="MsoListParagraphCxSpLast" style="margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<b>TRY/CATCH/THROW</b></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
There are times that as a
database developer you may want to catch errors and handle them accordingly. This could include just letting them silently
fail, or logging the parameters of the error, or re-throwing an error. SQL Server allows us to do this very
operation with the TRY…CATCH block. If
the code inside the TRY block throws an error, the CATCH block will be executed
to handle the aforementioned error.
Below is an example:</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">begin</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">try</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">select</span> 1<span style="color: grey;">/</span>0<span style="color: grey;">;</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">end</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">try</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">begin</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">catch</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">select</span> <o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: magenta;">error_message</span><span style="color: grey;">()</span> <span style="color: blue;">as</span> <span style="color: magenta;">error_message</span><span style="color: grey;">,</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: magenta;">error_number</span><span style="color: grey;">()</span> <span style="color: blue;">as</span> <span style="color: magenta;">error_number</span><span style="color: grey;">,</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: magenta;">error_severity</span><span style="color: grey;">()</span> <span style="color: blue;">as</span> <span style="color: magenta;">error_severity</span><span style="color: grey;">,</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: magenta;">error_state</span><span style="color: grey;">()</span> <span style="color: blue;">as</span> <span style="color: magenta;">error_state</span><span style="color: grey;">,</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: magenta;">error_line</span><span style="color: grey;">()</span> <span style="color: blue;">as</span> <span style="color: magenta;">error_line</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">end</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">catch<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
I use a
blatant error (dividing by zero) to shift execution to the CATCH block, which
simply selects the specific error parameters for viewing. The built-in system functions of the CATCH
block make available the specific (and appropriately named) portions of the
error. They are extremely useful if you
do want to get the finer view of what happened to cause the CATCH block to
execute.</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
The THROW
statement is the successor to the RAISERROR() function. It allows us to do just that: THROW errors.
The syntax is as follows:</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">throw</span><span style="font-family: Consolas; font-size: 9.5pt;"> 50001<span style="color: grey;">,</span> <span style="color: red;">'My Example Error Message'</span><span style="color: grey;">,</span>
1<span style="color: grey;">;<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
If the THROW statement
is within a CATCH block, then parameters don’t need to be supplied:</div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">begin</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">try</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">select</span> 1<span style="color: grey;">/</span>0<span style="color: grey;">;</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">end</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">try</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">begin</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">catch</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">throw</span><span style="color: grey;">;</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">end</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">catch<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
This allows us to
re-THROW the error that caused the CATCH block to execute.<span style="color: grey; font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<b><span style="font-size: 14.0pt; line-height: 115%;">References<o:p></o:p></span></b></div>
<div class="MsoListParagraphCxSpFirst" style="margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l0 level1 lfo2; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;">·<span style="font-family: 'Times New Roman'; font-size: 7pt;">
</span></span><!--[endif]--><a href="http://msdn.microsoft.com/en-us/library/ms175976.aspx">BOL reference on TRY…CATCH</a></div>
<div class="MsoListParagraphCxSpLast" style="margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l0 level1 lfo2; text-indent: -.25in;">
<!--[if !supportLists]--><span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;">·<span style="font-family: 'Times New Roman'; font-size: 7pt;">
</span></span><!--[endif]--><a href="http://msdn.microsoft.com/en-us/library/ee677615.aspx">BOL reference on
THROW</a></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in; text-indent: .25in;">
If there are any comments, questions, issues, or suggestions please feel
free to leave a comment below or email me at <a href="mailto:sqlsalt@gmail.com">sqlsalt@gmail.com</a>.</div>
Anonymoushttp://www.blogger.com/profile/10345205579327231089noreply@blogger.com0tag:blogger.com,1999:blog-6981106914441296719.post-31809908776067293052012-08-21T20:03:00.001-07:002012-08-21T20:03:53.142-07:00Evaluate the Use of Row-Based Operations vs. Set-Based Operations<br />
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<i>(This is part of the </i><a href="http://sqlsalt.blogspot.com/p/study-guides.html"><i>Study Guide series</i></a><i>,
70-457)<o:p></o:p></i></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<b><i>Microsoft’s
Measured Skill description</i></b>: This objective may include but is not
limited to: when to use cursors; impact of scalar UDFs; combine multiple DML
operations</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<b><i>What
I see</i></b>:</div>
<div class="MsoListParagraphCxSpFirst" style="margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l1 level1 lfo1; text-indent: -.25in;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;">·<span style="font-family: 'Times New Roman'; font-size: 7pt;">
</span></span>when to use cursors</div>
<div class="MsoListParagraphCxSpLast" style="margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l1 level1 lfo1; text-indent: -.25in;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;">·<span style="font-family: 'Times New Roman'; font-size: 7pt;">
</span></span>impact of scalar UDFs</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<b>When to Use Cursors</b></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
Cursors are a funny thing in SQL
Server. Many times, data professionals
come from software development backgrounds.
And as programmers, we like to think row-by-row with data. We are extremely comfortable with for loops,
while loops, and other cursory language features. And then we step into the world of the
RDBMS. We try to transfer our
programming knowledge directly to database development and administration. So often times, in the infancy of our data
careers we opt to go with cursors because they are familiar ground. While this is an understandable route, it is
often the wrong one. We need to think of
data as a set-based entity, as opposed to a collection of rows. Cursors treat the data just like that…row-by-row. But the optimizer and SQL Server in general are
much more streamlined to deal with sets instead of looping through individual
rows. As a general rule of thumb, I tend
to only use cursors when set-based operations and DML statements are absolutely
impossible, or when the set-based workaround is so cumbersome and
unmaintainable that it because of SQL nightmare. There is no hard and fast rule, as there is
definitely going to be a time when you run into a situation when a cursor is
appropriate, but it definitely should not be a daily occurance.</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<b>Impact of Scalar UDFs</b></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
The performance impact of scalar
UDFs is the performance implication that comes along with the optimizer calling
the UDF each time for ever row returned.
This could lead to a notoriously bad performance problem that often
comes with scalar UDFs. For further
information, read <a href="http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/04/21/not-all-udfs-are-bad-for-performance.aspx">this
informative post on SQL Blog by Alexander Kuznetsov</a>.</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<b><span style="font-size: 14.0pt; line-height: 115%;">References<o:p></o:p></span></b></div>
<div class="MsoListParagraph" style="margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l0 level1 lfo2; text-indent: -.25in;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;">·<span style="font-family: 'Times New Roman'; font-size: 7pt;">
</span></span><a href="http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/04/21/not-all-udfs-are-bad-for-performance.aspx">Blog
post on why Scalar UDFs can hurt performance, by Alexander Kuznetsov</a></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in; text-indent: .25in;">
If there are any comments, questions, issues, or suggestions please feel
free to leave a comment below or email me at <a href="mailto:sqlsalt@gmail.com">sqlsalt@gmail.com</a>.</div>
Anonymoushttp://www.blogger.com/profile/10345205579327231089noreply@blogger.com0tag:blogger.com,1999:blog-6981106914441296719.post-64802836849653736772012-08-19T17:40:00.003-07:002012-08-19T17:42:37.686-07:00Manage Transactions<br />
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<i>(This is part of the </i><a href="http://sqlsalt.blogspot.com/p/study-guides.html"><i>Study Guide series</i></a><i>,
70-457)<o:p></o:p></i></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<b><i>Microsoft’s
Measured Skill description</i></b>: This objective may include but is not
limited to: mark a transaction; understand begin tran, commit, and rollback;
implicit vs. explicit transactions; isolation levels; scope and type of locks;
trancount</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<b><i>What
I see</i></b>:</div>
<div class="MsoListParagraphCxSpFirst" style="margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l1 level1 lfo1; text-indent: -.25in;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;">·<span style="font-family: 'Times New Roman'; font-size: 7pt;">
</span></span>mark a transaction</div>
<div class="MsoListParagraphCxSpMiddle" style="margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l1 level1 lfo1; text-indent: -.25in;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;">·<span style="font-family: 'Times New Roman'; font-size: 7pt;">
</span></span>begin tran, commit tran, rollback tran</div>
<div class="MsoListParagraphCxSpMiddle" style="margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l1 level1 lfo1; text-indent: -.25in;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;">·<span style="font-family: 'Times New Roman'; font-size: 7pt;">
</span></span>implicit vs. explicit transactions</div>
<div class="MsoListParagraphCxSpMiddle" style="margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l1 level1 lfo1; text-indent: -.25in;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;">·<span style="font-family: 'Times New Roman'; font-size: 7pt;">
</span></span>isolation levels</div>
<div class="MsoListParagraphCxSpLast" style="margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l1 level1 lfo1; text-indent: -.25in;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;">·<span style="font-family: 'Times New Roman'; font-size: 7pt;">
</span></span>@@trancount</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<b>Mark a Transaction</b></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
SQL Server allows us to mark
transactions in order to leverage specific point recovery to a particular
transaction. For instance, with the
AdventureWorks database say you mark a transaction when you modify particular
data:</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">use</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">AdventureWorks2012</span><span style="color: grey;">;</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">go</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">begin</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">tran</span> <span style="color: teal;">ProductionUpdate</span> <span style="color: blue;">with</span>
<span style="color: teal;">mark</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: magenta;">update</span> <span style="color: teal;">HumanResources</span><span style="color: grey;">.</span><span style="color: teal;">Department</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">set</span> <span style="color: teal;">name</span> <span style="color: grey;">=</span> <span style="color: red;">'Production Modified'</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">where</span> <span style="color: teal;">DepartmentID</span> <span style="color: grey;">=</span> 7<span style="color: grey;">;</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">commit</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">tran</span> <span style="color: teal;">ProductionUpdate<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
And then you
further modify this same data:</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: magenta; font-family: Consolas; font-size: 9.5pt;">update</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">HumanResources</span><span style="color: grey;">.</span><span style="color: teal;">Department</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">set</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">name</span> <span style="color: grey;">=</span> <span style="color: red;">'Production after Mark'</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">where</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">DepartmentID</span> <span style="color: grey;">=</span> 7<span style="color: grey;">;<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
Once the log
is subsequently backed up, you now have the option to restore to the committed
transaction named “ProductionUpdate”.
You can accomplish this by doing the following (provided you have the
correct full recovery model backups available):</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">restore</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: magenta;">log</span> <span style="color: teal;">AdventureWorks2012</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">from</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">disk</span> <span style="color: grey;">=</span> <span style="color: red;">'C:\YourBackupDir\AW_postMT.trn'</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">with</span><span style="font-family: Consolas; font-size: 9.5pt;"> <o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">recovery</span><span style="color: grey;">,</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">stopatmark</span> <span style="color: grey;">=</span> <span style="color: red;">'ProductionUpdate'</span><span style="color: grey;">;</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">go<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
Now by
running the following query, you can see that we have restored the database to
the committed portion of the marked transaction:</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">use</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">AdventureWorks2012</span><span style="color: grey;">;</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">go</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">select</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: grey;">*</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">from</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">HumanResources</span><span style="color: grey;">.</span><span style="color: teal;">Department</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">where</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">DepartmentID</span> <span style="color: grey;">=</span> 7<span style="color: grey;">;<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<b>BEGIN TRAN, COMMIT TRAN, and ROLLBACK TRAN</b>
</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
These three T-SQL statements are
used with explicit transactions. BEGIN
TRAN tells SQL Server that an explicit transaction is starting. It can be a named transaction, and marked (as
explained above). Subsequently, COMMIT
TRAN signifies the end of a transaction by doing just that; committing it. ROLLBACK TRAN will undo the data modification
that happened during the transaction. These
explicit transaction statements are used in order to adhere to the ACID
principle, particularly atomicity. You
can ensure that transaction integrity leads to data integrity. Take the following example:</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">begin</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">tran</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: magenta;">update</span> <span style="color: teal;">HumanResources</span><span style="color: grey;">.</span><span style="color: teal;">Department</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">set</span> <span style="color: teal;">Name</span> <span style="color: grey;">=</span> <span style="color: red;">'Production'</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">where</span> <span style="color: teal;">DepartmentID</span> <span style="color: grey;">=</span> 7<span style="color: grey;">;</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">if</span> <span style="color: magenta;">is_rolemember</span><span style="color: grey;">(</span><span style="color: red;">'db_owner'</span><span style="color: grey;">,</span> <span style="color: magenta;">user_name</span><span style="color: grey;">())</span> <span style="color: grey;">=</span> 1<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">commit</span> <span style="color: blue;">tran</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">else</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">rollback</span> <span style="color: blue;">tran<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
The above is
a relatively useless example, but it shows through the use of explicit transactions
how BEGIN TRAN, COMMIT TRAN, and ROLLBACK TRAN function. It does an UPDATE of data, and if the current
database user isn’t in the db_owner role, it rolls back the modified data. Otherwise it commits the UPDATE.</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<b>Implicit vs. Explicit Transactions</b></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
We have already talked briefly
about using explicit transactions (see above), but conversely SQL Server allows
us to utilize implicit transactions.
When you are operating with IMPLICIT_TRANSACTIONS ON for a particular
connection, there are a handful of statements that automatically start a
transaction, and that transaction will be open until either committed or rolled
back. To show an example of implicit transactions,
see below:</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">use</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">AdventureWorks2012</span><span style="color: grey;">;</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">go</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">set</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">implicit_transactions</span> <span style="color: blue;">on</span><span style="color: grey;">;</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">go</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: magenta; font-family: Consolas; font-size: 9.5pt;">update</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">HumanResources</span><span style="color: grey;">.</span><span style="color: teal;">Department</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">set</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">Name</span> <span style="color: grey;">=</span> <span style="color: red;">'Eng'</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">where</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">DepartmentID</span> <span style="color: grey;">=</span> 1<span style="color: grey;">;</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: green; font-family: Consolas; font-size: 9.5pt;">-- now disconnect this connection</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: green; font-family: Consolas; font-size: 9.5pt;">-- (i.e.
close the query window)<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: green; font-family: Consolas; font-size: 9.5pt;">-- open a new query window and execute
the below code.</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: green; font-family: Consolas; font-size: 9.5pt;">-- you will notice that the initial
transaction was</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: green; font-family: Consolas; font-size: 9.5pt;">-- never committed. This is because with IMPLICIT_TRANSACTIONS ON</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: green; font-family: Consolas; font-size: 9.5pt;">-- you need to commit the transaction
in order for that to reflect</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">use</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">AdventureWorks2012</span><span style="color: grey;">;</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">go</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">select</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: grey;">*</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">from</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">HumanResources</span><span style="color: grey;">.</span><span style="color: teal;">Department</span><span style="color: grey;">;<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<b>Isolation Levels</b></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
SQL Server transaction isolation
levels are a relatively in depth portion of locking and transactions. You should have a thorough understanding of
all the pessimistic and optimistic isolation levels. <a href="http://msdn.microsoft.com/en-us/library/ms173763.aspx">Please see BOL for
reference</a>.</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<b>@@TRANCOUNT</b></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
The system function @@TRANCOUNT
returns the current open transactions.
It will be incremented by one for BEGIN TRAN, decremented by one for
COMMIT TRAN, and appropriately set to zero for ROLLBACK TRAN. See below for an example in order to view the
return of @@TRANCOUNT with different variations of explicit transactions:</div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">begin</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">tran</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">select</span> <span style="color: magenta;">@@trancount</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">begin</span> <span style="color: blue;">tran</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">select</span> <span style="color: magenta;">@@trancount</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">begin</span> <span style="color: blue;">tran</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">select</span> <span style="color: magenta;">@@trancount</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">commit</span> <span style="color: blue;">tran</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">select</span> <span style="color: magenta;">@@trancount</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">commit</span> <span style="color: blue;">tran</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">select</span> <span style="color: magenta;">@@trancount</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">begin</span> <span style="color: blue;">tran</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">select</span> <span style="color: magenta;">@@trancount</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">commit</span> <span style="color: blue;">tran</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">select</span> <span style="color: magenta;">@@trancount</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">commit</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">tran</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">select</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: magenta;">@@trancount<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<b><span style="font-size: 14.0pt; line-height: 115%;">References<o:p></o:p></span></b></div>
<div class="MsoListParagraphCxSpFirst" style="margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l0 level1 lfo2; text-indent: -.25in;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;">·<span style="font-family: 'Times New Roman'; font-size: 7pt;">
</span></span><a href="http://msdn.microsoft.com/en-us/library/ms187014.aspx">BOL reference on
Marked Transactions</a></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l0 level1 lfo2; text-indent: -.25in;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;">·<span style="font-family: 'Times New Roman'; font-size: 7pt;">
</span></span><a href="http://msdn.microsoft.com/en-us/library/ms188929.aspx">BOL reference on
BEGIN TRANSACTION</a></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l0 level1 lfo2; text-indent: -.25in;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;">·<span style="font-family: 'Times New Roman'; font-size: 7pt;">
</span></span><a href="http://msdn.microsoft.com/en-us/library/ms190295.aspx">BOL reference on
COMMIT TRANSACTION</a></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l0 level1 lfo2; text-indent: -.25in;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;">·<span style="font-family: 'Times New Roman'; font-size: 7pt;">
</span></span><a href="http://msdn.microsoft.com/en-us/library/ms181299.aspx">BOL reference on
ROLLBACK TRANSACTION</a></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l0 level1 lfo2; text-indent: -.25in;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;">·<span style="font-family: 'Times New Roman'; font-size: 7pt;">
</span></span><a href="http://msdn.microsoft.com/en-us/library/ms187807.aspx">BOL reference on
SET IMPLICIT_TRANSACTIONS</a></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l0 level1 lfo2; text-indent: -.25in;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;">·<span style="font-family: 'Times New Roman'; font-size: 7pt;">
</span></span><a href="http://msdn.microsoft.com/en-us/library/ms173763.aspx">BOL reference on
SET TRANSACTION ISOLATION LEVEL</a></div>
<div class="MsoListParagraphCxSpLast" style="margin-bottom: .0001pt; margin-bottom: 0in; mso-add-space: auto; mso-list: l0 level1 lfo2; text-indent: -.25in;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;">·<span style="font-family: 'Times New Roman'; font-size: 7pt;">
</span></span><a href="http://msdn.microsoft.com/en-us/library/ms187967.aspx">BOL reference on
@@TRANCOUNT</a></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in; text-indent: .25in;">
If there are any comments, questions, issues, or suggestions please feel
free to leave a comment below or email me at <a href="mailto:sqlsalt@gmail.com">sqlsalt@gmail.com</a>.</div>
Anonymoushttp://www.blogger.com/profile/10345205579327231089noreply@blogger.com0tag:blogger.com,1999:blog-6981106914441296719.post-59000037656451675342012-07-18T17:36:00.001-07:002012-07-18T17:39:11.821-07:00The EVENTDATA() Function The use of the EVENTDATA() function within SQL Server allows us to extract valuable and necessary information pertaining to auditing and triggers, such as Event Notifications and DDL triggers. The Database Engine strategically provides the capturing mechanism with a handful of well-structured data.<br />
<br />
<b>What is the format of this data?</b><br />
The format of the provided data is XML. When working with EVENTDATA(), one of the best tools that you can use is the XML Schema Definition to reference when looking for the elements you'd like to query and capture. This XSD can be found at the following link: <a href="http://schemas.microsoft.com/sqlserver/2006/11/eventdata/events.xsd">SQL Server EVENTDATA() XSD file</a>. This definition is dauntingly vast, but there's a neat little trick. In order to quickly search through this XML Schema Definition file, you simple need to search for the keyword "EVENT_INSTANCE_<i>EventType</i>". For instance, say you are creating a DDL Trigger for the CREATE_PROCEDURE event. Search for the text "EVENT_INSTANCE_CREATE_PROCEDURE", and you will be brought to your desired event and containing elements:<br />
<br />
<div class="line" style="font-family: monospace; font-size: 13px;">
<br />
<div class="line">
<br />
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"><xs:complexType name="EVENT_INSTANCE_CREATE_PROCEDURE"></span><span style="font-family: 'Courier New'; font-size: 10pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <xs:sequence></span><span style="font-family: 'Courier New'; font-size: 10pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <!-- Basic Envelope --></span><span style="font-family: 'Courier New'; font-size: 10pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <xs:element name="EventType" type="SSWNAMEType"/></span><span style="font-family: 'Courier New'; font-size: 10pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <xs:element name="PostTime" type="xs:string"/></span><span style="font-family: 'Courier New'; font-size: 10pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <xs:element name="SPID" type="xs:int"/></span><span style="font-family: 'Courier New'; font-size: 10pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <!-- Server Scoped DDL --></span><span style="font-family: 'Courier New'; font-size: 10pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <xs:element name="ServerName" type="PathType"/></span><span style="font-family: 'Courier New'; font-size: 10pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <xs:element name="LoginName" type="SSWNAMEType"/></span><span style="font-family: 'Courier New'; font-size: 10pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <!-- DB Scoped DDL --></span><span style="font-family: 'Courier New'; font-size: 10pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <xs:element name="UserName" type="SSWNAMEType"/></span><span style="font-family: 'Courier New'; font-size: 10pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <!-- Main Body --></span><span style="font-family: 'Courier New'; font-size: 10pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <xs:element name="DatabaseName" type="SSWNAMEType"/></span><span style="font-family: 'Courier New'; font-size: 10pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <xs:element name="SchemaName" type="SSWNAMEType"/></span><span style="font-family: 'Courier New'; font-size: 10pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <xs:element name="ObjectName" type="SSWNAMEType"/></span><span style="font-family: 'Courier New'; font-size: 10pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <xs:element name="ObjectType" type="SSWNAMEType"/></span><span style="font-family: 'Courier New'; font-size: 10pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <xs:element name="TSQLCommand" type="EventTag_TSQLCommand"/></span><span style="font-family: 'Courier New'; font-size: 10pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> </xs:sequence></span><span style="font-family: 'Courier New'; font-size: 10pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"></xs:complexType></span><span style="font-family: 'Courier New'; font-size: 10pt;"><o:p></o:p></span></div>
</div>
</div>
<div class="collapsible-content" style="font-family: monospace; font-size: 13px; margin-left: 1em;">
<div class="collapsible" id="collapsible298">
<div class="expanded">
<div class="collapsible-content" style="margin-left: 1em;">
<div class="line">
<br /></div>
</div>
</div>
</div>
</div>
<div class="line">
This is the bulk of the information you'll need in order to start utilizing the EVENTDATA() function.</div>
<div class="line">
<br /></div>
<div class="line">
<b>Using the EVENTDATA() function</b></div>
<div class="line">
Say you want to create a DDL Trigger in order to gather event information for the CREATE PROCEDURE command. In the aforementioned paragraph, we already have laid out the elements that we can use and capture, as well as their names and types. In the interest of an example, let's create a database and some basic objects to illustrate this trigger.</div>
<div class="line">
<br /></div>
<div class="line">
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
</div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">use</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">master</span><span style="color: grey;">;</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">go</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">create</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">database</span> <span style="color: teal;">EventDataDemo</span><span style="color: grey;">;</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">go</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">use</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">EventDataDemo</span><span style="color: grey;">;</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">go</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">create</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">table</span> <span style="color: teal;">DdlAudit</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: grey; font-family: Consolas; font-size: 9.5pt;">(</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">EventType</span> <span style="color: blue;">nvarchar</span><span style="color: grey;">(</span>128<span style="color: grey;">)</span> <span style="color: grey;">null,</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">DatabaseName</span> <span style="color: blue;">nvarchar</span><span style="color: grey;">(</span>128<span style="color: grey;">)</span> <span style="color: grey;">null,</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">SchemaName</span> <span style="color: blue;">nvarchar</span><span style="color: grey;">(</span>128<span style="color: grey;">)</span> <span style="color: grey;">null,</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">ObjectName</span> <span style="color: blue;">nvarchar</span><span style="color: grey;">(</span>128<span style="color: grey;">)</span> <span style="color: grey;">null,</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">LoginName</span> <span style="color: blue;">nvarchar</span><span style="color: grey;">(</span>128<span style="color: grey;">)</span> <span style="color: grey;">null,</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">UserName</span> <span style="color: blue;">nvarchar</span><span style="color: grey;">(</span>128<span style="color: grey;">)</span> <span style="color: grey;">null,</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">SqlText</span> <span style="color: blue;">nvarchar</span><span style="color: grey;">(</span>1024<span style="color: grey;">)</span> <span style="color: grey;">null,</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">AuditDateTime</span> <span style="color: blue;">datetime</span>
<span style="color: grey;">null</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: grey; font-family: Consolas; font-size: 9.5pt;">);</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">go<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;"><br /></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
Now that we have the audit table setup, we can create the DDL Trigger that will be capturing and handling the CREATE PROCEDURE event. This is one of those times where SQL Server XML knowledge comes in handy, as we'll be relying heavily on the use of the XML value() method and an XQuery to extract our desired data.</div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">create</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">trigger</span> <span style="color: teal;">DdlCreateProc</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">on</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: grey;">all</span> <span style="color: blue;">server</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">for</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">create_procedure</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">as</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">declare</span> <span style="color: teal;">@eventdata</span> <span style="color: blue;">xml</span> <span style="color: grey;">=</span> <span style="color: magenta;">eventdata</span><span style="color: grey;">();</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">insert</span> <span style="color: blue;">into</span> <span style="color: teal;">EventDataDemo</span><span style="color: grey;">.</span><span style="color: teal;">dbo</span><span style="color: grey;">.</span><span style="color: teal;">DdlAudit</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">select</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">@eventdata</span><span style="color: grey;">.</span><span style="color: teal;">value</span><span style="color: grey;">(</span><span style="color: red;">'(/EVENT_INSTANCE/EventType)[1]'</span><span style="color: grey;">,</span> <span style="color: red;">'nvarchar(128)'</span><span style="color: grey;">),</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">@eventdata</span><span style="color: grey;">.</span><span style="color: teal;">value</span><span style="color: grey;">(</span><span style="color: red;">'(/EVENT_INSTANCE/DatabaseName)[1]'</span><span style="color: grey;">,</span> <span style="color: red;">'nvarchar(128)'</span><span style="color: grey;">),</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">@eventdata</span><span style="color: grey;">.</span><span style="color: teal;">value</span><span style="color: grey;">(</span><span style="color: red;">'(/EVENT_INSTANCE/SchemaName)[1]'</span><span style="color: grey;">,</span> <span style="color: red;">'nvarchar(128)'</span><span style="color: grey;">),</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">@eventdata</span><span style="color: grey;">.</span><span style="color: teal;">value</span><span style="color: grey;">(</span><span style="color: red;">'(/EVENT_INSTANCE/ObjectName)[1]'</span><span style="color: grey;">,</span> <span style="color: red;">'nvarchar(128)'</span><span style="color: grey;">),</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">@eventdata</span><span style="color: grey;">.</span><span style="color: teal;">value</span><span style="color: grey;">(</span><span style="color: red;">'(/EVENT_INSTANCE/LoginName)[1]'</span><span style="color: grey;">,</span> <span style="color: red;">'nvarchar(128)'</span><span style="color: grey;">),</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">@eventdata</span><span style="color: grey;">.</span><span style="color: teal;">value</span><span style="color: grey;">(</span><span style="color: red;">'(/EVENT_INSTANCE/UserName)[1]'</span><span style="color: grey;">,</span> <span style="color: red;">'nvarchar(128)'</span><span style="color: grey;">),</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">@eventdata</span><span style="color: grey;">.</span><span style="color: teal;">value</span><span style="color: grey;">(</span><span style="color: red;">'(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]'</span><span style="color: grey;">,</span> <o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: red;">'nvarchar(1024)'</span><span style="color: grey;">),</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: magenta;">getdate</span><span style="color: grey;">()</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">go<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
To test out our audit, simple create a stored procedure and query the DdlAudit table.</div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
</div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">use</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">EventDataDemo</span><span style="color: grey;">;</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">go</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">create</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">procedure</span> <span style="color: teal;">dbo</span><span style="color: grey;">.</span><span style="color: teal;">MyTestProcedure</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">as</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">select</span> 1<span style="color: grey;">;</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">go</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">select</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: grey;">*</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">from</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">DdlAudit</span><span style="color: grey;">;<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"><span style="color: grey;"><br /></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
The above example shows a good use of the EVENTDATA() function, and how you can extract valuable information from it under given circumstances. If there are any comments, questions, or issues please feel free to leave a comment below or email me at sqlsalt@gmail.com.</div>
<br />
<br /></div>Anonymoushttp://www.blogger.com/profile/10345205579327231089noreply@blogger.com0