SDU Code Camp Material

This is just a short note to let those that are looking for the material from my SQL Code Camp 09 presentation on SSIS debugging know that it will be placed here during this week.

I will break down the presentation into separate posts on Breakpoints, Data Viewer and Error Redirection. There will also be a short post on extracting information from the filename and storing this into a variable (one of the tasks in the package) at the request of one of the attendees.

Finally, thanks to Rob Farley for his support and tips on my presentation and to Greg Low for giving me the opportunity to present.

A Storm is brewing, no wait, its SQL in the cloud

SQL Data Services (SDS) has been available as a cloud service but until now we have been told it is NOT SQL Server in the cloud and is not to be seen as a RDBMS. There has since been a big change to that with the announcement that SDS will be enchanced with TDS, yay another TLA, TDS = Tabular Data Stream, or, a full RBBMS in the cloud.

The SQL Data Services Team blog lists, among other features:
Tables?...Check

Stored Procedures?...Check

Triggers?...Check

Views?...Check

Indexes?...Check

Visual Studio Compatibility?...Check

ADO.Net Compatibility?...Check

ODBC Compatibility?...Check

so there we have it, apparently you can just point your connection string towards the gathering clouds and thats it. Time to run a demo on this i think, coming soon....

The full announcment is available here .

Rank makes things less rank

A query that has popped up a few times is to retrieve a list of people along with the first/last/top sale that they have made. If we were after the last sale ever made, this query would be simple, utilising top we would order by the relevant column and get the top 1, done. However in this case we want the top item PER person, not per the entire set, this is where Rank() comes into play.

Using Rank we can avoid iterating through our data with cursors to achieve this query in a single set operation.

Lets use adventureWorks and retrieve the last sale date made by each of our salespeople.

Our first step is to get all of our sales people and the orders they made:

select 
    SOH.OrderDate,
    SOH.SalesPersonID, 
    vEmp.FirstName + ' ' + vEmp.LastName Employee
from 
    Sales.SalesOrderHeader SOH
    inner join Sales.SalesPerson SP on SOH.SalesPersonID = SP.SalesPersonID
    inner join HumanResources.vEmployee vEmp on SP.SalesPersonID  = vEmp.EmployeeID

Now we would like to provide away to determine which OrderDate is the latest, per SalesPerson. This is where rank comes into play:

select 
    SOH.OrderDate,
    SOH.SalesPersonID, 
    vEmp.FirstName + ' ' + vEmp.LastName Employee,
    rank() over(Partition by SOH.SalesPersonID order by OrderDate desc) OrderRank
from 
    Sales.SalesOrderHeader SOH
    inner join Sales.SalesPerson SP on SOH.SalesPersonID = SP.SalesPersonID
    inner join HumanResources.vEmployee vEmp on SP.SalesPersonID  = vEmp.EmployeeID

the key section is:

rank() over(Partition by SOH.SalesPersonID order by OrderDate desc) OrderRank

this now gives us a machanism for determining, per SalesPerson, which order was the latest, it will be the order with a OrderRank of 1.

Breaking down the function, the key parts are the partition and the order by:
Partition:
Patition is analogous with 'Group By' and is used to literally Partition our dataset to allow for multiple ordering withing the one dataset, Partitioning by the SalesPersonID will let us provide subsequent ranking based on each ID
Order By
Order By is the item which will determine our ranking and is based on the set of data provided by the partition

However, this still does not give us the Last order as per our request, to get the last order, we now filter the result set by OrderRank, limiting it only to those rows with a rank of 1, giving us the last order Per SalesPerson.


As Rank() is a derived column, we must select from this result set and filter the subset:

select
    OrderDate, SalesPersonID, Employee
from
    (
    select
        SOH.SalesOrderID, 
        SOH.OrderDate,
        SOH.SalesPersonID, 
        vEmp.FirstName + ' ' + vEmp.LastName Employee,
        rank() over(Partition by SOH.SalesPersonID order by OrderDate desc) OrderRank
    from 
        Sales.SalesOrderHeader SOH
        inner join Sales.SalesPerson SP on SOH.SalesPersonID = SP.SalesPersonID
        inner join HumanResources.vEmployee vEmp on SP.SalesPersonID  = vEmp.EmployeeID
    ) RankedOrders
where
    RankedOrders.OrderRank = 1

Now we nearly have the last sale date of our salesperson. as we can see from the result, any items in the ranking which have the same OrderDate are given a ranking of 1, therefore we get 2 results for Stephen Jiang as he completed two order on June 1. Depending on your reporting requirements you can handle this with a more selective ranking order or in our case simply using distinct as we require the sales person and the last sale date.

select distinct
    OrderDate, SalesPersonID, Employee
from
    (
    select
        SOH.SalesOrderID, 
        SOH.OrderDate,
        SOH.SalesPersonID, 
        vEmp.FirstName + ' ' + vEmp.LastName Employee,
        rank() over(Partition by SOH.SalesPersonID order by OrderDate desc) OrderRank
    from 
        Sales.SalesOrderHeader SOH
        inner join Sales.SalesPerson SP on SOH.SalesPersonID = SP.SalesPersonID
        inner join HumanResources.vEmployee vEmp on SP.SalesPersonID  = vEmp.EmployeeID
    ) RankedOrders
where
    RankedOrders.OrderRank = 1

And there we have it, a much less rank way of getting the top results without having to resort to cursors.

Free E-Book ''Mastering SQL Server 2005 Profiler'

Looks like its a free book frenzy today, Brad McGehee from RedGate has made available his new book 'Mastering SQL Server 2005 Profiler' from the link below.

Brad is also available on 'Simple-Talk' and 'Twitter'

Free SSIS 2005 E-Book from MS-Press

Its not often that you can get something for nothing but the good folks at MS-Press have made SQL Server 2005 Integration Services - Step By Step available as a free download from the link below, you do have to register to receive it but im sure most people interested in its contents already receive some mails from MS.

Starting to Study for MCTS 70-445 BI exam

Thanks to Andrew Coates, who has kindly sent me a voucher for a exam, i am jumping straight back into study with 70-445. This is the 2005 flavour of "Business Intelligence - Implementation and Maintenance", i already had my eye on the 2008 version but considering the study material is not released until April 1, I will go with 2005. This is not a major issue as my MCITP is in 2005 and i think its good to keep them all in sync.

My first step is to grab a copy of the MSPress Training Kit, i have used these kits for all my previous exams so will stick with them. after reading through this and doing all the exercises plus plenty of extra hands on work ill grab a sample exam from Self-test, who i have also used before, and ensure that i get around 90-95% before booking the exam with Coatsies magic ticket.

So you can expect to see plenty of blog posts as i work through the material. Fingers Crossed :)

Thanks again to Andrew for the voucher.

Sending 'FOR XML' into work for the post office

One way that HTML based email bodys have been constructed is by iterating through a cursor and building your HTML with each row, the trouble with this method is that it does not scale very well. In order to over come the lineal growth of a cursor, we can utilise 'For XML' and build our HTML content with a set based operation. This following post will compare both methods to show how this can be done.

Firstly we will build a email body using cursors and see how long this takes to run, We are simply going to build an email that lists all customers* in New South Wales, from the Adventureworks database, along with their email address and phone number.

The following SQL will display this:

select
    distinct
    ltrim(isnull(Title,'') + ' ' + FirstName + ' ' + LastName)CustomerName, 
    Phone, 
    EmailAddress
from
    Sales.vIndividualCustomer
where
    StateProvinceName = 'New South Wales' 

This must now be built into a cursor and iterated in order to build our HTML:

Declare @Body as varchar(max)

set @Body = '<html><body>' + 
            '<h1>NSW Customers</h1>
            ' + '<table border="1">' 
            
DECLARE Customers Cursor FAST_FORWARD FOR 
    select 
        distinct
        ltrim(isnull(Title,'') + ' ' + FirstName + ' ' + LastName) CustomerName,
        Phone,
        EmailAddress 
    from 
        Sales.vIndividualCustomer 
    where
    StateProvinceName = 'New South Wales'


Declare @CustomerName varchar(200), 
        @Phone varchar(25), 
        @EmailAddress varchar(50) 

open Customers 
Fetch Next From Customers into @CustomerName, @Phone, @EmailAddress 

while @@Fetch_STatus = 0 
begin 
    set 
        @Body = @body + '<tr> ' 
                + '<td>' + @CustomerName + '</td>' 
                + '<td>' + @Phone + '</td>' 
                + '<td>' + @EmailAddress + '</td>' 
                + '</tr>' 
    Fetch Next from Customers into @CustomerName, @Phone, @EmailAddress 
end 

close Customers 
Deallocate Customers 

set @body = @body + '</table>' 

EXEC msdb.dbo.sp_send_dbmail 
    @profile_name = 'AdventureWorksProfile',
    @recipients='adventureJo@adventure-works.com',
    @subject = 'NSW Customers',
    @body = @body,
    @body_format = 'HTML' ;

The above approach using cursors works as expected and emails our report to the sales manager, however it is not going to scale very well, a more scalable solution is to generate the same html but using a set operation, utilising For XML as below

DECLARE @body           varchar(max),
          @Subject    varchar(255)
          
SET @body = 
    N'<html><body><H1>NSW Customers</H1>' +
    '<table border="1">' +
    '<tr><th>Customer Name</th>' +
    '<th>Phone</th>' +
    '<th>EmailAddress</th>' +
    (select
        Distinct
        ltrim(isnull(Title,'') + ' ' + FirstName + ' ' + LastName) td, '', 
        Phone td, '', 
        EmailAddress td, ''
    from 
        Sales.vIndividualCustomer 
    where
        StateProvinceName = 'New South Wales'
    FOR XML PATH('tr')) + 
    N'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail 
    @profile_name = 'AdventureWorksProfile',
    @recipients='adventureJo@adventure-works.com',
    @subject = 'NSW Customers',
    @body = @body,
    @body_format = 'HTML' ;

This code generates the same email many times faster with a lighter impact on our server, moreso, it is scalable as promised.