Friday, August 27, 2010

Blog Moved

After using the wordpress platform for another Blog i have decided to move this blog there. All content and comments here, to date, will appear over there as well

Please point your browser to http://sqljohn.wordpress.com, where you can also subscribe via a button or by using the RSS feed from here http://sqljohn.wordpress.com/feed/

There will be a new post series that will be popping very shortly that is all about performance monitoring.

Tuesday, April 6, 2010

Oh I, oh, I'm still alive

Just a very quick note to say that, although it seems otherwise, i am still alive and wanting to contribute to this blog.

Unfortunately work and life infringed upon my blogging time to the point where i did not have the chance to write up my materials from the code camp presentation. I am now going to allocate time for this and hope to have it up in the next couple of weeks (it is 3/4s done).

As penance, once it is up, i would like to video capture the presentation as well and post that too.

Monday, October 12, 2009

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.

Wednesday, March 11, 2009

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 .

Wednesday, February 18, 2009

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.

Thursday, January 22, 2009

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.

Free SSIS Book