SQLRelay 2014

Well this is my first blog post in a while! Sorry I’ve been so slack with it, you know how it is, life gets in the way Smile

So anyway, I’m currently sat in my hotel in “sunny” Newcastle all set to start the second part of my SQLRelay tour tomorrow.

If you’re here for the slides and scripts, scroll to the bottom of this post Smile

Part One

Day one

The first phase of my SQLRelay tour took me to first off to Reading, a great event at the Microsoft HQ. The rooms were great too, cinema style staggered seating and big comfy chairs. I had pretty much a full room and lots of good feed back.

Also had a chat with a few people about their query plans and issues that they were having. My query plans sessions always seems to get people thinking and its great to talk to people about their issues and how they are going about them (and also to be able to help out with some tips every now and then too Smile)

 

Day Two

Then it was off to Southampton (well Winchester but I wont pour any more petrol on that subject! Winking smile). Another great venue and a event, was good to see Steph and Nick that I’ve been working with a lot recently too.

Another good full room, lots of questions and some good feedback.

 

Day three

Next up was Bristol, I travelled up to stay with a friend right after the Southampton event, was good to have an evening to chill out. The Bristol event was good, I got to catch up with a few old faces and meet some new ones too!

My session seemed to go down well, lots of questions and good feedback too.

Day Four

The final day in this leg of the relay was Cardiff, the event was great and the venue was lovely. Very decorative and across lots of floors. My session was right up on the top floor, so I thanked everyone for making the climb Smile

Another really good session, had lots of questions and a very engaged audience. Also had a nice chat with a few people after my session.

The only issue I had in Cardiff was the road system and parking places, both of which were a bit of a mockery! Winking smile 

The car parks are certainly not designed for large rear wheel drive cars Smile

 

Part two

So this week I’ll be doing Newcastle, Leeds, Birmingham and then finishing up with London. I’m really looking forward to this week and meeting lots of new faces.

My flight in to Newcastle was very quick and mostly smooth and my hotel is nice too Smile

 

 

Slides and Scripts

Below is a list of links to my slides and scripts. Although I shouldn’t have to say this, I will. Please don’t steal my stuff and pass it off as your own (yes people have done this and I’ve caught them!)

If you want to use any of this content in your own presentations then please email me TSQLNinja@Outlook.com and we can discuss it Smile

 

Daves SQL Fun Time Hourhttps://onedrive.live.com/redir?resid=D8A9ABBFB8BB65E5!13628&authkey=!APofXkPcBjWzM9k&ithint=file%2czip

Query Plans Deep Dives –  https://onedrive.live.com/redir?resid=D8A9ABBFB8BB65E5!13627&authkey=!AA2r-9UhgUNXIgA&ithint=file%2czip

Stats, Estimation and Plan Caching –  https://onedrive.live.com/redir?resid=D8A9ABBFB8BB65E5!13626&authkey=!AJuBc0MDtvaqMA0&ithint=file%2czip

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.

WP_20140321_003

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

10151179_10152014045677285_15279048_n

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):

https://onedrive.live.com/redir?resid=D8A9ABBFB8BB65E5!1796&authkey=!AK6s-AvE-vKVsDA&ithint=folder%2c.sql

Enjoy! Open-mouthed smile

10150720_10152014049407285_1643989762_n

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
WHERE object_id=OBJECT_ID(‘[YOUR TABLE NAME]‘)
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]
AS
    SET NOCOUNT ON

    DECLARE @NewRecs TABLE
    (
        ProductKey INT,
        ProductNaturalKey VARCHAR(50),
        RowHASH    VARBINARY(16)
    )  

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

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

    -- update the relevant maintence records
    UPDATE MDP
    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 ,
              RowHASH
            )
    SELECT  NR.ProductKey ,
            NR.ProductNaturalKey ,
            GETDATE() AS EffectiveDate,
            NULL AS ExpirationDate,
            1 AS MostRecent,
            NR.RowHASH 
    FROM @NewRecs AS NR

    SET NOCOUNT OFF

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!

Enjoy

Dave

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)

https://dl.dropbox.com/u/81661862/ProceduralVsSetBased.zip

https://dl.dropbox.com/u/81661862/SQL%20Sat%20Exeter.zip

https://dl.dropbox.com/u/81661862/SQL%20Server%202012.zip

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

image

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

image

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.

image

image

image

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

Thanks

Dave!

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!

image

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! ;)

image

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.

image

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

Dave

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