SQL Saturday Exeter 2014

Hi guys, so SQL Sat Exeter has once again come and gone and once again it was a fantastic, well organised event.


There were surfboards, Hawaiian shirts, palm trees, monkeys (of the inflatable kind) and even a few grass skirts!

Miss Ninja and I had a great time, so thanks for all that made it happen Smile


I was lucky enough to be asked to give a full day pre con and although it was an immense amount of work to put together it was more than worth it to spend a day with a fantastic group of attendees!

I also had a full room for my session on the Saturday too and, snoring man aside Smile (it was very hot in my defence) that seemed to go down very well too which is always great too!

So as promised I have attached my slides in pdf form and also the scripts to my demos.

Slight disclaimer on the demos, some of them have been written to work on tables and structures that I may have already had on my local database and that the scripts themselves may not set up. Also there’s are more than a few DBCC commands and changing of server and session variables so as always please use with caution! Smile

Slides and scripts available in the link below (Before clicking, please see my note at the bottom of this post):


Enjoy! Open-mouthed smile


Just a quick note: I’ve already had situations in the past where I have found people using my stuff without permission or crediting me in anyway so I’m putting this all up in good faith. Please don’t let me down internets Smile

Anyone that speaks / presents will tell you that a stupid amount of effort goes into putting these things together and I personally can’t think of anything more offensive that my hard work being stolen and passed off as someone else’s. That being said, I don’t mind if you want to use some of this stuff in your own presentations etc BUT please contact me first, I’m more than likely to say yes as long as I’m credited in where my stuff is used Smile

Record counts the easy way

I’m always surprised that so many people that use SQL Server rely on the old COUNT(*) to retrieve basic full table record counts. Doing it this way basically makes sql server read the whole table to give you a count of the records, something it actually already knows!

Try doing this instead, replacing [YOUR TABLE NAME] with … well yeah, you get it

SELECT SUM (row_count)
FROM sys.dm_db_partition_stats
AND (index_id=0 or index_id=1);

Do some comparisons of your own, do a COUNT(*) on a table and then do this.

Enjoy! :)

How do you type 2?!

Hi all!

I sometimes get asked from all different people how I deal with my slowly changing dimension tables and I thought it would be interesting to do a post on how I generally approach it and then see how everyone else does it too. That way we can all learn from the best bits of everyone’s ideas! Smile

Over the years the way I do this has evolved and changed from the experiences I have had (for better and for worse), and of course no two implementations are the same.

Now being an MS bod I generally use a combination of SSIS & SQL code for my ETL processing. Just before we crack on, I’d just like to make one assumption clear, in my opinion the SCD wizard transform / component of SSIS is pants!

It’s slow, memory hungry and clunky. I know some people do use it and don’t get me wrong it does work but, again in my opinion, it’s not great.

So, that assumption holding true we need to deal with SCD’s by hand so lets look at my simplistic template to how I approach the most common SCD, type 2.

Firstly what I’ll do is, for each dimension that needs this SCD behaviour,  I will set up what I call a MDCR table (Master Data Control Record) in the staging area (database) for my warehouse solution. Don’t ask me where this acronym came from because I honestly can’t remember!  Smile

So, if I have a dimension called dimProduct that requires type 2 behaviour I’d create a table something like this

CREATE TABLE MDCR_dimProduct( ProductKey INT NOT NULL, – The surrogate key for the dimension ProductNaturalKey VARCHAR(50) NOT NULL, – The Natural or Business key

RowHash INT NOT NULL, – A hash across all type 2 fields in the dimension EffectiveDate DATE NOT NULL, ExpirationDate DATE NULL, MostRecent BIT NOT NULL)

Ok, so what we have is the surrogate key for the dimension, the key for the customer from the source system (business key / BK), a hash value generated with the HASHBYTES() function with the MD5 hash and the dates the record is effective from and to.

Now every record in the dimension, in this case dimProduct, has a  corresponding record in here. In doing it this way I can avoid having any of the type 2 information actually in the dimension.

From here the process is quite straight forward, you bring in all the source data, ensuring that you add an MD5 hashto each row on the corresponding columns to those that you used in the target dimension.

This source data is then checked it against our MDCR table, joining on the business key and MostRecent =1. If the business key doesn’t exist then its a new record so I add a new column called IsNew or similar and tag the record as a new entity.

If the business key does exist then it progresses onto a second phase. In this second phase we compare the hashvalues for the source row to the hashstored in the MDCR table. If the hash values match then no change has been made and this record’s journey stops there. If they do not match then again I add a field called IsNew and the record gets tagged as an update.

Now I take both the new records and update records and combine them together before placing them in a pre created temporary processing table.

Data Flow

Once all relevant records have made it into this temporary processing table I then run a stored procedure.

This stored procedure takes every row in the temporary processing table and adds a new record to our dimension table, using an OUTPUT on the insert to ensure we get the newly added rows surrogate key values and business key.

This may seem strange but if you think about it, in a type two environment, new dimension members are new records but so are updates to existing members.

Once this is done we then take the surrogate key, business key and hash values for all of our new records and add them to the MDCR table.

The final step is to then simply set the MostRecent flag to 0 in the MDCR table for all previous versions of a dimension member (record) that has received an update

The code for the update SP looks something like this

CREATE PROCEDURE [dbo].[UpdateDimProduct]

        ProductKey INT,
        ProductNaturalKey VARCHAR(50),
        RowHASH    VARBINARY(16)

    -- add the new records and output the new product keys 
    INSERT INTO dbo.dimProduct
            ( ProductName ,
              ProductNaturalKey ,
        ,INSERTED.ProductNaturalKey, NULL 
    INTO @NewRecs
    SELECT  TDP.ProductName ,
            TDP.ProductNaturalKey ,
    FROM dbo.temp_dimProduct AS TDP

    -- Get the record hash
    FROM @NewRecs AS NR
    JOIN dbo.temp_dimProduct AS TDP ON
        NR.ProductNaturalKey = TDP.ProductNaturalKey  

    -- update the relevant maintence records
    SET MDP.ExpirationDate = dateadd(dd, -1, GETDATE()),
        MDP.MostRecent = 0
    FROM dbo.MDCR_dimProduct AS MDP
    JOIN dbo.temp_dimProduct AS TDP ON
        MDP.ProductNaturalKey = TDP.ProductNaturalKey
        AND MDP.MostRecent = 1
        AND TDP.IsNew = 0  

    --add the new maintenance record
    INSERT INTO dbo.MDCR_dimProduct
            ( ProductKey ,
              ProductNaturalKey ,
              EffectiveDate ,
              ExpirationDate ,
              MostRecent ,
    SELECT  NR.ProductKey ,
            NR.ProductNaturalKey ,
            GETDATE() AS EffectiveDate,
            NULL AS ExpirationDate,
            1 AS MostRecent,
    FROM @NewRecs AS NR


And that’s about it.

As I said this is a very broad description of how I generally approach type 2 dimensions. So, hope this has been helpful and I love to hear how you type 2 as well!



SQL Saturday 194 in Exeter – Part 2

HI all, so as promised here is my follow up post with the slides and demos from both the pre con and also my session on the Saturday. The files can be found here and are password with the password Dave and I gave out on the day (my Saturday session files are not passworded)




I also just wanted to take the time to share something really nice that we received from the event, the session feed back. I can’t emphasise enough how much we as speakers appreciate it when people take the time to give feed back, especially comments on top of the scores.

Its also really nice when all the scores are high numbers Smile If you have ever been lucky enough or are ever lucky enough to get to present a conference session or a pre con, you’ll know that a lot of work goes into it, and getting feedback makes it all worth while. Good feedback is awesome but also any constructive criticism is always welcomed too. I don’t personally mind if you give me a low score as long as you tell me why, no one is perfect and this kind of feed back is the best way to improve.

That being said, the two feedbacks I got from SQL Saturday in Exter where all amazing, it’s genuinely humbling and means a lot when people give nice feed back and say that it has helped them

So, firstly I want to share the scores from Dave Ballantyne and my pre con session. By the way all scores are out of 5


As you can see we got pretty much full marks in all sections, again this is great and we really appreciate it. The comments as well really made us go “wow”. Again, amazing.

Then the feedback for my session on Saturday, again scores are out of 5


Again I was really amazed at the scores and also the comments. Overall I’m totally overwhelmed by how positive everyone was and all the nice things that people said, in feedbacks, on the day and also via twitter.




Again a selection of a couple of the really nice things that people said post the event.

Now you might be asking why “Dave, why are you posting all this ego stroking material” well for a few reasons, to say thank you to everyone that took the time to leave the great feedback. Also to say that it really helps everyone leaving feedback, even the organisers, so they can gauge how the event went down.

And finally to share it so that anyone thinking about speaking for the first time can see what to expect and also that generally everyone is really nice and that all feedback is good, even constructive criticism.

Anyway that’s it for now



SQL Saturday 194 in Exeter – Looking Back

So this weekend finally saw SQL Saturday Exeter arrive, for me its felt like ages in the build up so it was great when the time finally came!
I traveled up on the Thursday night ready for the pre con Dave Ballantyne and I were doing on the Friday.

We started the Friday morning the breakfast of champions way!


As you can see, it was a good way to start the day!

We then went on to our room and waited for our awesome delegates to arrive. We actually “won” the day in terms of numbers having about 16 attendees, something we didn’t hesitate to rib the other pre con trainers about ;)

Having never done a pre con before I wasn’t sure what to expect but the whole day was great, everyone that attended was interested and engaged and asking loads of questions, which is fantastic.

It all went mostly without a hitch other than the “demo gods” messing with us once it twice. The best of these was myself “loosing” a demo I wanted to run spending a couple of minutes trying to find it to no avail and then stumbling across it about 40 minutes later, to the sound of much laughter from the attendees I might add! ;)


We broke the day up into four chunks with Dave and I alternating between sections and chipping to each others segments too, which seemed to work really well.


A great day of training followed by the speaker meal and a few beers. Miss ninja also came down and joined us for the evening with a view to coming along on the Saturday.

Saturday came and the main event was upon us, was great to see all the speakers from the sql family there and loads of engaged attendees too.

I did my session at ten fourty five and had a full room of engaged people, again asking lots of interesting questions. I was even graced by Jamie Thompson which was good who asked a few questions and kept me on my toes! :)

We did unfortunately slip off a little early as myself and miss ninja started to feel a bit under the weather but all in all an awesome weekend was had!

I’m on a train as I write this so I will update the post later today adding a password zip file containing all slides and demos from both the pre con and Saturday session

As a final thing, I want to say a massive thank you to all the organisers and helpers, you guys made this an awesome event and I hope everyone recognises your hard work

Anyway that’s it for now


SQL Sat 194 – Exeter Pre Con


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!


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

Hi all,


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!