The title is just words thrown together. I’m glad you noticed. This post is the start of a “Study Blog” category, which is more or less a scribble pad for when I’m watching a video or reading stuff.

My thoughts recently have been with the 70-461 Querying Microsoft SQL Server 2012/2014 exam. I was converted into the DBA-land from an IT Operations role and so far I’ve always recognised querying as a weak area.

It’s sometimes tough keeping on-track with certification routed studies though. The main focus has always been to learn the important areas required within my role at work. I’ve been lucky enough to have moved around quickly, but that adds to the difficulty level. It’s been great experiencing massive changes to the technologies I work with on a daily basis as well as big changes to how different scales of companies operate.

Another chunk of what I’d call study time is, keeping up with email subscriptions from other blogs! It’s so easy spending an hour reading through link to link. There’s so many interesting features that I want to play with but it always comes down to how much time you have left after weekly responsibilities have been met!

To add to this, when I’m watching videos, I have to question things like, what would it be like having to work with spatial aggregates & geographical datatypes as a job? How complex does this get? For this example, I found an amazing Stack Exchange post that blew my mind. I love digressing into particular things I don’t know during videos & reading. I remember watching Microsoft SQL Server 2016: it just runs faster for the first time a few years ago, it must have taken 4 times as long to watch the video due to how much I needed to clarify before I could take it all in.

Anyway. The above is explained to give an idea of how I work. The information below contains notes taken during directed 70-461 and whatever else I’m reading. I’ll create new study blog posts every so often.

DIFFERENCES BETWEEN IMPLICIT AND EXPLICIT CONVERIONS

#– Microsoft’s documentation on data type conversions.

#– The above includes this image that would be perfectly suitable for any bedroom wall I’m sure.

DIFFERENCES BETWEEN CAST AND CONVERT

#– Microsoft’s documentation on CAST and CONVERT.

#– CAST is available so SQL Server adheres to ANSI / ISO standardisation meaning it’ll never be a problem running that code against a non SQL Server database.

#– The CONVERT function allows an optional styling parameter that CAST does not.

THE FORMAT FUNCTION

#– The performance difference is insane.

INSERTS, NULLS, ALERT, IDENTITY & PRIMARY KEYS

Opening SSMS and seeing where your mind takes you is always a good plan…
The default value will be NULL if values are unspecified.

But if we ALTER the column to not allow NULL values, what happens?

Primary Key columns cannot be NULL, but it’s fun to see errors. I also created a separate post on changing the order of columns which relates to adding new IDENTITY columns.

We don’t need to include identity columns upon insert.

As stated within the Microsoft link above, gaps may happen when using identity. You can sort this by creating another table with the same schema, import all data excluding the identity column, drop the first table and rename the new table. Note, this is a complex task if the ID column is used as a foreign key on other tables, so you may want to leave as is.