I will be migrating this blog to WordPress sometime this week. I will do my best to ensure this is a seamless transition.
The new blog address will be HTTP://sqlsalt.wordpress.com
Thank you for reading this blog and please continue to do so!
SQL Salt
Ride the SQL Server wave of Data...
Thursday, September 13, 2012
Wednesday, August 22, 2012
Implement Error Handling
(This is part of the Study Guide series,
70-457)
Microsoft’s
Measured Skill description: This objective may include but is not
limited to: implement try/catch/throw; use set based rather than row based
logic; transaction management
What
I see:
·
try/catch/throw
TRY/CATCH/THROW
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:
begin try
select 1/0;
end try
begin catch
select
error_message() as error_message,
error_number() as error_number,
error_severity() as error_severity,
error_state() as error_state,
error_line() as error_line
end catch
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.
The THROW
statement is the successor to the RAISERROR() function. It allows us to do just that: THROW errors.
The syntax is as follows:
throw 50001, 'My Example Error Message',
1;
If the THROW statement
is within a CATCH block, then parameters don’t need to be supplied:
begin try
select 1/0;
end try
begin catch
throw;
end catch
This allows us to
re-THROW the error that caused the CATCH block to execute.
References
If there are any comments, questions, issues, or suggestions please feel
free to leave a comment below or email me at sqlsalt@gmail.com.
Tuesday, August 21, 2012
Evaluate the Use of Row-Based Operations vs. Set-Based Operations
(This is part of the Study Guide series,
70-457)
Microsoft’s
Measured Skill description: This objective may include but is not
limited to: when to use cursors; impact of scalar UDFs; combine multiple DML
operations
What
I see:
·
when to use cursors
·
impact of scalar UDFs
When to Use Cursors
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.
Impact of Scalar UDFs
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 this
informative post on SQL Blog by Alexander Kuznetsov.
References
If there are any comments, questions, issues, or suggestions please feel
free to leave a comment below or email me at sqlsalt@gmail.com.
Sunday, August 19, 2012
Manage Transactions
(This is part of the Study Guide series,
70-457)
Microsoft’s
Measured Skill description: 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
What
I see:
·
mark a transaction
·
begin tran, commit tran, rollback tran
·
implicit vs. explicit transactions
·
isolation levels
·
@@trancount
Mark a Transaction
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:
use AdventureWorks2012;
go
begin tran ProductionUpdate with
mark
update HumanResources.Department
set name = 'Production Modified'
where DepartmentID = 7;
commit tran ProductionUpdate
And then you
further modify this same data:
update HumanResources.Department
set name = 'Production after Mark'
where DepartmentID = 7;
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):
restore log AdventureWorks2012
from disk = 'C:\YourBackupDir\AW_postMT.trn'
with
recovery,
stopatmark = 'ProductionUpdate';
go
Now by
running the following query, you can see that we have restored the database to
the committed portion of the marked transaction:
use AdventureWorks2012;
go
select *
from HumanResources.Department
where DepartmentID = 7;
BEGIN TRAN, COMMIT TRAN, and ROLLBACK TRAN
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:
begin tran
update HumanResources.Department
set Name = 'Production'
where DepartmentID = 7;
if is_rolemember('db_owner', user_name()) = 1
commit tran
else
rollback tran
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.
Implicit vs. Explicit Transactions
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:
use AdventureWorks2012;
go
set implicit_transactions on;
go
update HumanResources.Department
set Name = 'Eng'
where DepartmentID = 1;
-- now disconnect this connection
-- (i.e.
close the query window)
-- open a new query window and execute
the below code.
-- you will notice that the initial
transaction was
-- never committed. This is because with IMPLICIT_TRANSACTIONS ON
-- you need to commit the transaction
in order for that to reflect
use AdventureWorks2012;
go
select *
from HumanResources.Department;
Isolation Levels
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. Please see BOL for
reference.
@@TRANCOUNT
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:
begin tran
select @@trancount
begin tran
select @@trancount
begin tran
select @@trancount
commit tran
select @@trancount
commit tran
select @@trancount
begin tran
select @@trancount
commit tran
select @@trancount
commit tran
select @@trancount
References
If there are any comments, questions, issues, or suggestions please feel
free to leave a comment below or email me at sqlsalt@gmail.com.
Wednesday, July 18, 2012
The 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.
What is the format of this data?
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: SQL Server EVENTDATA() XSD file. 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_EventType". 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:
What is the format of this data?
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: SQL Server EVENTDATA() XSD file. 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_EventType". 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:
<xs:complexType name="EVENT_INSTANCE_CREATE_PROCEDURE">
<xs:sequence>
<!-- Basic Envelope -->
<xs:element name="EventType" type="SSWNAMEType"/>
<xs:element name="PostTime" type="xs:string"/>
<xs:element name="SPID" type="xs:int"/>
<!-- Server Scoped DDL -->
<xs:element name="ServerName" type="PathType"/>
<xs:element name="LoginName" type="SSWNAMEType"/>
<!-- DB Scoped DDL -->
<xs:element name="UserName" type="SSWNAMEType"/>
<!-- Main Body -->
<xs:element name="DatabaseName" type="SSWNAMEType"/>
<xs:element name="SchemaName" type="SSWNAMEType"/>
<xs:element name="ObjectName" type="SSWNAMEType"/>
<xs:element name="ObjectType" type="SSWNAMEType"/>
<xs:element name="TSQLCommand" type="EventTag_TSQLCommand"/>
</xs:sequence>
</xs:complexType>
This is the bulk of the information you'll need in order to start utilizing the EVENTDATA() function.
Using the EVENTDATA() function
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.
use master;
go
create database EventDataDemo;
go
use EventDataDemo;
go
create table DdlAudit
(
EventType nvarchar(128) null,
DatabaseName nvarchar(128) null,
SchemaName nvarchar(128) null,
ObjectName nvarchar(128) null,
LoginName nvarchar(128) null,
UserName nvarchar(128) null,
SqlText nvarchar(1024) null,
AuditDateTime datetime
null
);
go
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.
create trigger DdlCreateProc
on all server
for create_procedure
as
declare @eventdata xml = eventdata();
insert into EventDataDemo.dbo.DdlAudit
select
@eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)'),
@eventdata.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(128)'),
@eventdata.value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(128)'),
@eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)'),
@eventdata.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)'),
@eventdata.value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(128)'),
@eventdata.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'nvarchar(1024)'),
getdate()
go
To test out our audit, simple create a stored procedure and query the DdlAudit table.
use EventDataDemo;
go
create procedure dbo.MyTestProcedure
as
select 1;
go
select *
from DdlAudit;
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.
Friday, July 13, 2012
Optimize Queries
(This is part of the Study Guide series,
70-457)
Microsoft’s
Measured Skill description: This objective may include but is not
limited to: understand statistics; read query plans; plan guides; DMVs; hints;
statistics IO; dynamic vs. parameterized queries; describe the different join
types (HASH, MERGE, LOOP) and describe the scenarios in which they would be
used
What
I see:
·
understand statistics
·
query hints
·
statistics IO
·
join types (HASH, MERGE, LOOP)
Understand Statistics
Statistics are the way that SQL
Server records and uses the data distribution for tables and indexes. They allow the query optimizer to choose an
appropriate plan based off of row count, histograms, or page density. Fresh statistics are necessary for the
process to make the best possible decision, but stale statistics can fool SQL
Server into thinking it has found the best plan, when in fact it is a
sub-optimal plan. For a great read and
more information on statistics, see this Idera
post by Donabel Santos on Understanding SQL Server Statistics.
Query Hints
Query hints are a way to tell
the optimizer what to do, regardless of what the optimizer might have done
originally. A few popular query hints
are KEEP PLAN, MAXDOP, OPTIMIZE FOR, and RECOMPILE. For instance, MAXDOP will override the
configured instance max degree of parallelism.
RECOMPILE will cause SQL Server to discard the query execution plan
after the query has completed as opposed to persistently storing it for later
use. Please see BOL for a full list of
Query Hints and corresponding explanations.
All of these query hints are probably fair game on the exam, so a
cursory knowledge of what they do will benefit you.
STATISTICS IO
The set statement, SET
STATISTICS IO, is used to output statistics regarding disk activity for the
executed T-SQL queries. To see a working
example of this, execution the below T-SQL and view the Messages window to see the disk/cache activity:
use AdventureWorks2012;
go
set statistics io on;
select *
from HumanResources.Department;
set statistics io off;
This gives
us information such as scan count, logical reads (from the data cache/memory),
physical reads (from disk), read-ahead reads (read from disk to cache for
future page reads), and the LOB equivalents to the aforementioned statistics. This is a great way to see if a query or a
subset of queries is hitting the disk too often.
Join Types
There are three particular join
types the optimizer can choose to utilize:
Hash Join – this join takes the smaller
of the two sets to join and makes a hash table and fits that in the memory
grant. Then it takes the other set and
probes by computing a hash value for each row and comparing it to the hash
table. To see this join in action,
utilize the following query (notice a relatively small table that can easily
fit into memory as a hashed table):
use AdventureWorks2012;
go
select *
from HumanResources.Department d
inner join HumanResources.EmployeeDepartmentHistory edh
on d.DepartmentID = edh.DepartmentID;
The
execution plan should look like the following:
Merge Join – this join goes through the
inputted rows only once, and this can show performance gains through sorted
data:
use AdventureWorks2012;
go
select *
from Person.Person p
inner join Person.BusinessEntity be
on p.BusinessEntityID = be.BusinessEntityID;
The
execution plan will resemble the following:
Loop Join – this join does just as its
name states: one of the data sets will have every row of data iterated for each
row of the other data set:
use AdventureWorks2012;
go
select
p.LastName,
bea.AddressTypeID
from Person.Person p
inner join Person.BusinessEntityAddress bea
on p.BusinessEntityID = bea.BusinessEntityID
where bea.AddressTypeID = 5;
The
execution plan for the above query:
References
If there are any comments, questions, issues, or suggestions please feel
free to leave a comment below or email me at sqlsalt@gmail.com.
Monday, June 18, 2012
Modify Data by Using INSERT, UPDATE, and DELETE Statements
(This is part of the Study Guide series,
70-457)
Microsoft’s
Measured Skill description: This objective may include but is not limited
to: given a set of code with defaults, constraints, and triggers, determine the
output of a set of DDL; know which SQL statements are best to solve common
requirements; use output statement
What
I see:
·
OUTPUT statement
OUTPUT Statement
The OUTPUT clause can be
used to pipe affected data from a corresponding DML statement (INSERT,
UPDATE,
DELETE)
or MERGE. Through the use of this clause, the data can
be saved to a table (user table, table var, temp table, etc.) or output to the
host. The below example shows two of the
many ways you can utilize the OUTPUT clause:
use MeasureMySkills;
go
if exists
(
select *
from sys.tables
where name = 'TestDataTable'
)
drop table TestDataTable;
go
create table TestDataTable
(
id int identity(1, 1) not null,
SomeString nvarchar(128) not null,
AnotherInt int not null
);
go
-- show the output of the inserted data
to the client
insert into TestDataTable(SomeString, AnotherInt)
output inserted.id, inserted.SomeString, inserted.AnotherInt
values
('hello', 34),
('goodbye', 49),
('hola', 60),
('adios', 78);
-- create temp table to hold deleted
data
create table #DeletedData
(
id int not null,
SomeString nvarchar(128) not null,
AnotherInt int not null
);
go
-- output the deleted data to the temp
table
delete from TestDataTable
output deleted.*
into #DeletedData
where AnotherInt in (34, 49);
select *
from #DeletedData;
References
If there are any comments, questions, issues, or suggestions please feel
free to leave a comment below or email me at sqlsalt@gmail.com.
Subscribe to:
Posts (Atom)