SQL Sat 194 – Exeter Pre Con

sqlsat194_whitebackground_web

Hi all!

As you may or may not know, Dave Ballantyne and myself are lucky enough to be doing a pre con session at the up coming SQL Sat 194 in Exeter on the 8th of March 2013.

This is my first ever pre con session and I don’t mind telling you that, from my side at least, it has been a lot of work to put together. I’m not sure what I expected when Dave and I agreed to do it but it has certainly taken up a few evenings and weekends Smile

That being said, it’s been and labour of love and I’m genuinely really excited about spending a day with a group of people that have spent real cash monies to come and see Dave and myself. It’s genuinely a humbling experience when you think about it like that, so to all that are attending, thank you! Open-mouthed smile (There may also be some Haribo as a thank you as well on the day)

So, as a teaser for the people that are attending I thought I’d give you a sneak peak of what you’ve got to look forward to during the day:

  • Query Plan Deep Dives
    • Taking a deep look into statistics, plan caching and query plan internals and more
  • Procedural vs set based code
    • Looking at the performance implications of cursors, UDFs, TVFs, Recursive CTE’s and more
  • TSQL Myths and Misconceptions
    • Taking a look at common mistakes people make in sql server and TSQL coding and also challenging common misconceptions and misunderstandings
  • SQL Server 2012 TSQL Improvements
    • A good look at some of the new TSQL and other functionality of sql 2012

So there it is, I hope that will whet your appetite for getting stuck in on the day. If there’s anything any of you attendees want to take a specific look at then please either drop a comment on this post or drop me an email (davidmorrison2009 at googlemail dot com) and we will see what we can do

Look forward to seeing you in Exeter!

Dave

SQL Saturday 194 – I’m Speaking! (Yay!)

Hi all,

ImSpeaking_SQLSatExeter_web

Bit of a belated post but I wanted to just share the news! Smile  As the post title implies I have had a session selected to present at the upcoming PASS SQL Saturday Exeter event.

Not only that but myself and Dave Ballantyne were also lucky enough to be offered the opportunity to do a whole days pre-con session as well! (http://sqlsouthwest.co.uk/SQLSaturday_precon.htm#pc1)

This will be my first ever pre-con session so if you’re coming, be gentle with me! Winking smile There may also be some haribo and other goodies in it for you when you come along!

I’m always genuinely excited and also very humbled whenever I get a session chosen to present, there are a whole host of very smart people out there who have a lot of really interesting and clever stuff to say, so its always genuinely an honour. So thank you to everyone that voted for my session Open-mouthed smile

If you are coming along to SQL Sat Exeter then please find me and see me and say hi! It’s always good to put faces to (twitter) names. I’ll also have the lovely miss Ninja is tow as well as I have convinced here that being a SQL geek is where it’s at! Winking smile

Anyway, hope to see you in Exeter!

Dave

 sqlsat194_whitebackground_web

What’s your leash?

Hi all, not a technical post today but more food for thought. I’m going to relate it mainly to career progression but to be honest it applies to all aspects of life (Get me being deep! Smile)

Anyway . . .

So, the other day I was walking back from my local super market, I came to a long stretch of path, quite wide with greenery on either side. Coming up from the other direction was a guy with quite a large dog. The guy looked respectable enough and to be honest big dogs don’t really bother me that much so I thought nothing of it.

As we got a little closer to passing each other I noticed this was a Rottweiler, it was a big strong and proud looking animal and looked quite happy to be plodding along by its owners side. Again I thought nothing of it as I spent a fair few hours around rescue Rottweiler’s as a kid and know first hand that the majority of them and big soppy softies! Smile

Just as we went to pass each other the dog decided it wanted to come and say “hi” to me (There was no malice in its action, I’m pretty sure it was just being friendly), the owner noticed this and simply gave the dogs leash (lead) a gentle tug and said something along the lines of “come on” to the dog and we all went on our way.

As I was walking back to my place I started to think about the dog and its owner and that little scenario I just described. As I thought about it some more I realised was that the reality of the situation was if that dog really wanted to make its way to me, be it to just say hi or to cause me harm, there was very little myself or the owner could have done about it, due to the large and muscular nature of the animal. Yet all the owner had to so was give the littlest of tugs and a gentle word and this strong and proud animal continued on its way.

Again this got me thinking as to how this situation could come about, if this was a “wild” animal on a lead it wouldn’t of happened, so I mentioned it to a friend of mine who trains dogs for a living. He said its all to do with conditioning from when they are pups, basically when they are small, that gentle tug on the lead and the gentle word are much more forceful. He went on to explain that effectively this idea of you being bigger and stronger than them just kind of sticks in there head and this is why the behaviour I saw was possible.

All interesting stuff, you might be thinking, but what has this got to do with me?

Mental Leash

Although we as human being obviously don’t get lead around on leash on a day to day basis, we are however held back by our own mental leashes.

As part of my job, I get to travel around a lot and go into a whole host of companies of differing size and ethos and the one common thing I can say every where I go is that I see is people being lead around by their own mental leashes, being held back by their own insecurities, fears and in some cases even laziness and lack of motivation. People stuck in a rut or stubborn in their ways. All these things have been built around experiences from the past, both positive and negative, just like a puppy is trained that its pointless to pull on its leash or that no means don’t do that.

People that allow themselves to be lead totally around by their leashes, generally speaking, are the ones who’s qualifications are from years ago, that haven’t been on a course or attended a user group / seminar or brought a book on their particular area of study for years. They end up stuck in the same role for the same company for umpteen years and get institutionalised.

All of these things can be a metaphorical leash around your neck being given a gentle tug, or the metaphorical firm word directing you away from what you want.

Now at this stage, so I don’t start to sound like a holier than thou tool, I’d like to point out that I’m far from perfect, I have a lot of my own “mental leashes” and over the last year or so I’ve been slowly trying to remove them, some more successfully than others. I still have quite a few but I try not to let them pull me around quite so much

So, what’s your leash?

My challenge to you all reading this now is to think about your leashes, what are the things that might be holding you back. Is it that little voice inside that says “cant” or maybe that fear you get when something new comes along?

Whatever they are, identifying them is the first step, and then pulling against them / removing them so you can approach that metaphorical hansom stranger and say “hi” without getting that tug around your neck

Final thoughts

Anyway this was just something that’s been on my mind and I wanted to share it, I hope you’ve found it interesting. Please feel free to drop me any comments as I welcome all feedback

Enjoy!

Dave

Running year number across date ranges

Hi guys,

I recently had an interesting sql problem passed to me and I thought I’d share the solution with you all as I couldn’t find anything nice online about it.

Background

So the issue was that we had a set of data that recorded peoples membership dates (lets say it was a gym memberships). The table looked like this (roughly Smile):

  • Person_ID
  • DateFrom
  • DateTo

The requirement was to run a query that would return a year number for each row that indicated how many contiguous years the person had held a membership at that point in time. The additional complexity with this was that memberships that had a gap of less than or equal to 365 days should also be considered contiguous.

I thought about all kinds of approaches to this (and I’m sure there are some I didnt think of) but I eventually I decided that I needed to do the following:

  • Assign every record a unique ID
  • Assign every every record a parent id which will be the above created unique id of the record that comes before this one, where applicable.
  • Use a recursive CTE to work out the year number

The Code

So lets set up the test data:

CREATE TABLE Memberships
(
    MembershipID INT NOT NULL IDENTITY,
    ParentID INT NULL,
    PersonID INT NOT NULL,
    StartDate DATE NOT NULL,
    EndDate DATE NOT NULL
)

GO

Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(10154997, '1999-10-02', '2000-10-01')
Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(10154997, '2000-10-02', '2001-12-28')
Insert into Memberships(PersonID,  StartDate, EndDate)
Values(10154997, '2002-01-10', '2003-02-10')
Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(10154997, '2003-04-08', '2003-09-15')
Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(10154997, '2003-11-26', '2004-03-15')
Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(10154997, '2004-10-05', '2004-10-06')
Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(10154997, '2005-02-02', '2005-02-09')
Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(10154997, '2005-02-24', '2005-03-01')
Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(10154997, '2006-09-28', '2007-01-07')
Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(10154997, '2008-11-11', '2009-01-12')
Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(613274, '1982-08-06', '1983-08-05')
Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(613274, '1983-08-06', '1984-08-05')
Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(613274, '1984-08-06', '1985-08-05')
Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(613274, '1985-08-06', '1986-08-05')
Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(613274, '1986-08-06', '1987-08-05')
Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(613274, '1987-08-06', '1988-08-05')
Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(613274, '1988-08-06', '1989-08-05')
Insert into Memberships(PersonID,  StartDate, EndDate) 
Values(613274, '1989-08-06', '1990-08-05')

GO

So firstly we need to get the id of the any date records that fall behind each record by no less than 365 days and set the parent id with it:

So as you can see here, we join the table to itself twice, firstly to get a list of all memberships that ended no more than 365 days before this one started and secondly to ensure if more than one record meets that requirement that only the closest one is returned as the “parent”.

UPDATE M
SET M.ParentID = M2.MembershipID 
FROM dbo.Memberships AS M
JOIN dbo.Memberships AS M2 ON 
        M.PersonID = M2.PersonID

        -- Where the enddate is less than 365 days away from my start date
        AND DATEDIFF(dd,M2.EndDate, M.StartDate) <= 365

        -- Stops dates before the start date coming through
        AND DATEDIFF(dd,M2.EndDate, M.StartDate) > 0 

        -- This little sub query max sure that if there are multiple 
        -- start dates within 365 days of my end date that only 
        -- the closest one is returned. 
        AND M2.StartDate =
        (
            SELECT Max(X.StartDate) 
            FROM dbo.Memberships AS X 
            WHERE X.PersonID = M.PersonID
            AND DATEDIFF(dd,X.EndDate, M.StartDate) <= 365
            AND DATEDIFF(dd,X.EndDate, M.StartDate) > 0
         );

Once this bit is done then its simply a matter of a reasonably simple recursive CTE:

WITH DateGroups (ParentID, MembershipID, PersonID, 
    StartDate, EndDate, YearNum)
AS
(
    SELECT  
        M.ParentID,
        M.MembershipID,
        M.PersonID ,
        M.StartDate ,
        M.EndDate ,
        1 AS YearNum
    FROM dbo.Memberships AS M 
    WHERE M.ParentID IS NULL

    UNION ALL 

    SELECT  
        M2.ParentID,
        M2.MembershipID,
        M2.PersonID ,
        M2.StartDate ,
        M2.EndDate ,
        DG.YearNum + 1
    FROM dbo.Memberships AS M2 
        JOIN DateGroups AS DG ON 
        M2.ParentID = DG.MembershipID
)

SELECT  DG.ParentID ,
        DG.MembershipID ,
        DG.PersonID ,
        DG.StartDate ,
        DG.EndDate ,
        DG.YearNum
FROM DateGroups AS DG
ORDER BY DG.PersonID, DG.StartDate, 
    DG.YearNum

and this then gives us the result set:

image

So that’s it really, dates can be a real pain to work with but I found this to be a nice elegant solution and its nice and quick too

A quick mention and thank you to my colleague Richard Thomas (@TR5AD) for passing this original problem my way

Enjoy!

Dave

Smile

Shredding CSV strings in SQL Server

Hi all!

So today I thought I’d go over how to break out a comma separated string stored in sql server into individual rows.

This can be a real PITA, if you’ve ever had to do this you’ll know why. Now before I start this post I want to just say that this is a simplistic example and has one main assumption, which is the each of the values within the string you are wanting to split out are of a fixed width. The number of values can be totally variable but the values within the string must be of a static length.

If you have a string with variable with values this approach isn’t what you need, however it might give you the starting point Smile

So lets assume we are working with a single field called “version” for example, and it contains strings such as:

image

What I want to do is get a line per version, now I’ve seen people use while loops, cursors and all sorts of craziness to solve this issue, when in fact it can be done in a single statement, using our old friend the numbers table!

So the first challenge is to determine how many values we are working with, I find the simplest way to do this is to count the number of delimiters and add one, something like the following:

(LEN([Version]) - LEN(REPLACE([Version], ',', ''))) + 1

What I’m doing here is taking the original length of the column we want to split then subtracting the length of the same column but with the delimiters removed, giving me the number of delimiters in the string, I simply then add one to that number to get the count of unique values I have to get, simples! Open-mouthed smile

Ok so now we have the number of values, how do I go about breaking them out? well firstly, as mentioned earlier, we are going to need a numbers table. Now if you have one of these already in your database great, if not then there is actually a built in one you can use! You can see this table for yourself by running the following:

SELECT N.number
FROM master..spt_values AS N
WHERE N.type = 'P'

Cool, no? So we have a number table and the number of items we need to work with so lets start building the query:

SELECT S.[Version], N.number
FROM dbo.Software AS S,
master..spt_values AS N
WHERE N.type = 'P'
AND N.number < (LEN(S.[Version]) - LEN(REPLACE(S.[Version], ',', '')))

Firstly, yes I KNOW comma “joins” are the devils play thing but in this case its fine, honest! Open-mouthed smile 

This will give us a row per item, in the list. You’ll notice that I’ve left off the +1 on the end if the LEN statements at the bottom. This is because the number table is base 0 and this is very important to how this process works.

So now we add the following to the select clause:

SUBSTRING(
    REPLACE(REPLACE(S.[Version], ' ', ''), ',', ''),
( 4 * N.number ) + 1, 4) AS VersionNum

This might look a bit crazy but stick with it. So the middle line of the three is removing all spaces and commas (you need to remove anything that is not part of the values you wish to split out)

Then we use the result of that as the source for the substring, now the “4 * N.number” part is important because 4 is the length I know each of my values are, so modify this number to suit you. The “+ 1” part is due to the fact that the number table is base 0.

The final “,4” is how many characters you wish to extract from that value, as I wanted the whole value I have used 4, if for example I only wanted the part in front of the decimal place (referring back to my original example at the beginning of the post) then I’d change this value only to “,2”.

The end result looks like this:

image

So we’ve taken our original string “Version” and created a row per individual version number. All in a set based manner without cursors or while loops or anything else, which will always be more performant. 

Hope this helps you out of string shredding hell Smile

Enjoy!

Dave

RedGate SQL Prompt Competition Winners!

Well we’ve had some really cracking entries for this competition and I have really hard time choosing only five, you’ve all made smile in one way or another Smile

Red Gate logo

But the lucky five winners are (in no particular order):

  • Andy Cutler for his plasticine rabbit
  • Alan Dykes for “SQL Prompt gives you super powers”
  • Koen Verbeeck for little bobby tables (an oldie but a goodie!)
  • Szymon Wojicik for his Charlie and the chocolate factory motivational poster
  • Dave Green for his need for the “yell” SQL Prompt Easter egg Smile

Pop onto the comments of the main post to see everyone’s entry.

Well done all, please drop me a PM on twitter and I’ll give you all the information you need to go claim your licence! Thanks to all that took part and happy sql prompting! Open-mouthed smile

Competition – RedGate SQL Prompt!

Hi all, so following my recent post about the whole host of great tools available to help us work with SQL Server and the BI stack, the awesomesauce guys at RedGate (@RedGate | http://www.red-gate.com/) got in touch.

Red Gate logo

They enjoyed my post so much they have honoured me by giving me four SQL Prompt licences to give away to you, my readers (you lucky lucky people!) Open-mouthed smile

As its a Friday and as RedGate as such cool people as their tools will make you smile, I thought the best way to choose who gets these is simple, simply tweet a link to this blog post and then put a comment on this post, including your twitter handle, with something that will make us smile! Smile

It can be anything, a nice comment, a joke, a link, a picture, anything! The only conditions are that it’s family / work friendly (I’m looking at you Mladen! Winking smile), legal, not breaching any kind of copyright laws and isn’t massively offensive.

The best four will be chosen by myself and the guys at RedGate so please be sure to leave your twitter handle in your comment so we can get in touch with the winners

Good luck!

 

Dave