John McCormack's blogs

My route to achieving MCSE Data Platform

28th January 2016 By John McCormack 2 Comments

This is my first non technical blog. Rather, it is about career and personal development.

Why did I want to achieve MCSE Data Platform?

Well, at first I didn’t. It seemed like a million miles away from where I started. My initial goal was just to pass the 70-462 exam (Administering Microsoft SQL Server 2012 Databases) as my boss had suggested it would be a good thing to learn. This was the first I had heard about Microsoft certifications. From there, with each subsequent exam pass, I just extended my goal to MCSA SQL Server 2012 and then MCSE Data Platform.

Timeline

I had set the target of passing 70-462 by the end of 2013 which would be just over 1 year as a DBA. The great thing about the certification is you can do the 70-461, 70-462 and 70-463 in any order. Since most of my limited knowledge was about Database Administration and the subject I needed to learn most about initially was also Database Administration, this was a no brainer.

[Read more…]

Who is John McCormack

21st January 2016 By John McCormack Leave a Comment

John McCormack profile

Hi. I’m John McCormack and I’ve been working professionally with SQL Server since 2012. I spent 6 years as a production DBA with Monster and now work as an DBA Contractor. I love working with SQL Server but also with other cloud solutions. It’s a job that I love and I’m constantly learning.

Prior to deciding I wanted to work with computers, I had a wide and varied career. I joined RBS and worked my way through various positions including mortgage adviser and finished up as a manager in a contact centre. I was always looking for other jobs and skipped through a few other sales and management jobs with British Gas, and T-Mobile/EE but I was ultimately looking for a change.

It was in 2008 that I decided a full change of direction was required and I managed to secure myself a place on a Web Development degree at UWS. I changed to evening/weekend work at T-Mobile and started the degree. At the time, I was pretty sure I wanted to be a web developer but I found I really enjoyed the database modules. It was upon hearing a talk from a Database Architect about various career routes in the database world that I knew I wanted to be a DBA. Fast forward 4 years and I had earned my degree and had landed a job with Monster.

Next steps

Landing my first database job as an associate DBA was only step 1. I knew that the next thing I had to do was learn, learn and learn more to become a competent DBA. Coming into IT 10 years later than I perhaps could have, I felt I had some catching up to do. To do this, I’ve learned from my colleagues, taken on challenges in work, studied for and gained MCSA in SQL Server 2012, then MCSE Data Platform. My next aim is to learn more topics about SQL Server in greater detail and to share my knowledge with the community as I go about. I’m have also achieved the AWS Cloud Practitioner and AWS Certified Solutions Architect – Associate qualifications.

Personal life

I’m married to Julie Ann and have 2 great boys, Graham and Johnny. We live in Paisley, Scotland. When I’m not working, I enjoy high impact exercise classes such as Boxfit and Metafit.

You can follow me on twitter but I can’t promise I’ll only talk shop. I’ve been known to tweet quite a lot about football, in particular, Saint Mirren FC of whom I am a big fan.

The EXCEPT operator in T-SQL

19th January 2016 By John McCormack Leave a Comment

The except operator in t-sql

When I first read about the except operator in t-sql and saw how simple it was to use, it knew I would be using it a lot. And I have.

I first came across it when studying for the 70-461 exam (Querying Microsoft SQL Server 2012). I keep trying to tell my colleagues about it but it seems they already know. So the only logical thing to do was write a blog post about it.

What does it do?

The EXCEPT OPERATOR compares two sets of data and performs set difference. In other words, it returns all of the rows from query 1 EXCEPT when an identical row is returned from query 2. This makes EXCEPT a quick and easy way to compare two sets of data without using 3^rd party tools or more complicated JOIN syntax.

What is the syntax?

[Read more…]

How to write a PIVOT query in T-SQL

12th January 2016 By John McCormack Leave a Comment

In this post, I explain how to write a simple PIVOT query in T-SQL and why we might want to use the PIVOT operator. The Oxford English Dictionary definition of PIVOT is

The central point, pin, or shaft on which a mechanism turns or oscillates.

So what we are doing here is TURNING THE DATA AROUND (PIVOTING) for visual purposes. The object of the exercise is to return 3 wide rows of data, instead of the 36 unique rows in the table.

Create the table and data which we will use in out PIVOT query

Lets create a database table which records the size of different tables within a database over time.

[sql]
CREATE TABLE TableSize
(
TableSizeId int identity (1,1) Primary Key,
DatabaseName sysname,
Name sysname,
DataKB INT,
Collection_Date Date
)

[/sql]

[Read more…]

Style over substance

6th January 2016 By John McCormack 2 Comments

When it comes to managing emails, I can easily receive about 600 a day. This is probably far too many (although that’s a subject for another day). The challenge with receiving so many emails, especially so many system generated errors and warnings is that they can fail to stand out. Important information can be missed. A lack of formatting can also make it difficult to understand what the problem is, if any.

Here is an example of a boring system generated message.

To get around this, I always try to use some pretty HTML in my output just to help with the appearance and to make the message easier to read. I don’t go overboard, just a header tag, something like <h4> is usually enough (unless you want it really big) and of course I like to include my output in a table, usually with a splash of colour in the table header.

[Read more…]