My favorite data type

I’m mostly indifferent to data types – it’s like asking what is your favorite spatula. They all each have their purpose. I admit I like legacy data types because they provide job security but they are not really my favorite.

After a bit of thinking, I’ll have to go with… *drumroll*… sql_variant.

Huh? Sql_variant? But why? I’ve met DB developers who weren’t even aware of its existence.

First of all, it can hold the majority of other data types, so it’s superior by definition. It’s at the top of the data precedence list of the system data types. It’s got a cool underscore in its name. No implicit conversion errors when converting from sql_variant, because you have to use explicit conversion (hey, it’s a feature!).

But the real reason is the SQL_VARIANT_PROPERTY function. With that I can get BaseType, Collation, MaxLength, Precision, Scale and TotalBytes of different data types.

I had an indexed view that multiplied two decimal numbers – what was the resulting size of that column? sql_variant to the rescue.

declare @number1 decimal (8,4) = 1234.4321
declare @number2 decimal (14,6) = 12345678.654321
declare @myFavourite sql_variant

select @myFavourite = @number1 * @number2

	  SQL_VARIANT_PROPERTY(@myFavourite, 'BaseType')   as BaseType
	, SQL_VARIANT_PROPERTY(@myFavourite, 'Collation')  as Collation
	, SQL_VARIANT_PROPERTY(@myFavourite, 'MaxLength')  as MaxLength
	, SQL_VARIANT_PROPERTY(@myFavourite, 'Precision')  as Precision
	, SQL_VARIANT_PROPERTY(@myFavourite, 'Scale') 	   as Scale
	, SQL_VARIANT_PROPERTY(@myFavourite, 'TotalBytes') as TotalBytes

I’ll leave this as an exercise for the reader.

I’ve seen it used only once in production in an EAV table (Entity, Attribute, Value) where sql_variant column was holding the values and different column described the required data type. But maybe because it’s so rare for me is the reason I like it.

For the least favourite type – datetime, no questions. It’s everywhere (even in non-legacy databases) even though it’s replaced by superior datetime2.

There is some millisecond rounding and it can eat up more bytes than more specific date type – but none of this is serious enough that it warrants rewrite. So it’s this minor inefficiency that will always haunt the database and that’s why I hate it.

My experience doing the online proctored exam

With the MCSA exams being retired at the end of January and COVID restrictions ramping up in my country causing the test centers to be closed, I had no other option but to go online.

I was worried about this as I heard stories from my colleagues that the slightest unexpected sound might disqualify you. So I looked up a guide on how it all goes down – this one was most informative:

I have a family with small kids, so they had to go for the duration of the exam. Not nice to kick them out in the winter, but they took one for the team. I was also hoping no one will ring a bell, start to drill the shared walls, or randomly scream below my window (which are all frequent events).

With that in mind, I got to it. First of all, you download and run the system test program. This is the same program, that will be used on the day of the real exam. You will get an Access code for the test (and later a different one for the exam).

It will check your microphone, internet, and the webcam feed.
Next, you will need to close all applications and processes you possibly can.
Anecdotally I once forgot to close an app and when I got to the “Launch simulation” it told me that one app hasn’t been closed and this incident has been logged.


  • Computer (duh) preferably desktop.
    • They namely warn about VPN’s, proxies, and company computers with firewall rules, etc.
  • One valid government-issued Id (in my case driver’s license)
  • Mobile phone for verification (selfie, photo of Id, photos of the room, point of contact in case of e.g. internet outage).
    • The mobile phone was my biggest point of confusion – you cannot turn it off, because the proctor might contact you, but you also cannot have any random sounds? How am I supposed to control who calls me? I had the phone outside of my reach and screen down as per guidelines, but it was buzzing constantly with messages from company chat.
  • Webcam and microphone
  • A closed room where no one will disturb you
  • Clean work area – no papers, pens, jewelry, other screens, etc.
    • Nothing that could be used to cheat or copy the questions. I have two monitors on my stand – I had to turn the secondary away from me and unplug it. Also, there was a mess in the rest of the room as my ‘office’ is sort of a storage area.

On the day of the exam

You can start the process between 30 minutes before and 15 minutes after the registered time. Go to the bathroom in advance, you cannot during the exam.

You will go to your certification dashboard and it will show in the Appointments section. You will get the real access code this time and start the verification process. You will be prompted to use a mobile phone and go to the website and take a selfie, a photo of your Id against a dark background with no glare (both sides), and then 4 photos of your work area. Facing the desk, behind the desk, left and right.

I recommend starting as early as possible because I had some technical difficulties. First of all, my primary mobile browser is Firefox and I couldn’t get past the selfie stage – it just failed silently. Only after switching to chrome, the selfie camera showed an overlay with an oval area, where my face should be.

The second problem was with taking photos of my room – I took the first one and on the second it said – internal memory error. I’ve tried 3 times with no luck, so I restarted the phone and also changed the camera app from custom to default – that did the trick.

After completing verification, accepting the terms and conditions, and all that jazz, it went straight to the exam. I could see my camera feed at the top of the screen – your face must not move outside the camera scope, otherwise, you will be disqualified. Also, there is a Whiteboard option (similar to the piece of paper you would get at the test center) and a chat with the proctor.

I was expecting the greeter to give me some basic info and also slowly move my web camera from left to right to show my work area (like the official video said), but nothing happened.

Final thoughts

The test itself was not that much different than in the test center. But the feeling of being watched the whole time made me super itchy and I had the urge to scratch all the time. I was also mindful of not putting my hands over my mouth when thinking or mumbling to myself as these might be mistaken for a cheating attempt.

Overall I was more worried about failing for things outside my control (like the mobile phone or noise) than for my lack of knowledge. Given a choice, I would pick the test center the next time – even though that means a 2-hour commute one-way for me.

Silver bullets in SQL: Part 1, TYPE directive in FOR XML


“It depends” is the DBA’s most frequent answer and for a good reason. Most of the time giving good advice really depends on many variables. A good DBA must follow up by explaining “why” it depends.

Another often repeated mantra is “There are no silver bullets” everything is a trade-off, for example parallelism might decrease query duration but increases CPU usage.

Well, this blog post (and hopefully series) will try to cover a few of those very rare silver bullets. Now I’m cheating a bit in my definition, these tips will be along the lines “did you know this existed?” and the alternative is writing the query in a sub-optimal way. Let’s get to it then!

TYPE directive in FOR XML

XML is a very polarizing topic in the SQL community, some developers might be lucky and never run into it. But love it or hate it, it is there and will be for a while so we might as well use it in the most efficient manner.

Let’s say our goal is to generate an XML document from a query and pass it to an application or Service Broker, etc. We’re gonna use the FOR XML query. I’m testing this in a fresh SQL Server 2019 instance in the master database.

I’m gonna generate a small XML into an xml variable both with and without the TYPE directive. I’ll repeat the experiment but save it into the varchar(max) and nvarchar(max) variables.

/* xml variable, no TYPE directive */
DECLARE @xmlNoType xml
SET @xmlNoType = 
	FROM sys.all_objects
	FOR XML PATH(''), ROOT ('Document')
SELECT (DATALENGTH(@xmlNoType) / 1024.0) / 1024. AS SizeMB
/* xml variable, TYPE directive */
DECLARE @xmlType xml
SET @xmlType = 
	FROM sys.all_objects
	FOR XML PATH(''), ROOT ('Document'), TYPE
--SELECT (DATALENGTH(@xmlType) / 1024.0) / 1024. AS SizeMB
/* varchar(MAX) variable, no TYPE directive */
DECLARE @varchar varchar(MAX)
SET @varchar = 
	FROM sys.all_objects
	FOR XML PATH(''), ROOT ('Document') 
		Cannot use TYPE: 
		Implicit conversion from data type xml to varchar(max) is not allowed. Use the CONVERT function to run this query.
SELECT (DATALENGTH(@varchar) / 1024.0) / 1024. AS SizeMB
/* nvarchar(MAX) variable, no TYPE directive */
DECLARE @nvarchar nvarchar(MAX)
SET @nvarchar = 
	FROM sys.all_objects
	FOR XML PATH(''), ROOT ('Document')
		Cannot use TYPE: 
		Implicit conversion from data type xml to nvarchar(max) is not allowed. Use the CONVERT function to run this query.
SELECT (DATALENGTH(@nvarchar) / 1024.0) / 1024. AS SizeMB

	XML size			0.59 MB
	varchar(max) size	0.90 MB
	nvarchar(max) size	1.80 MB

Results on my machine are the following – first the size. Unsurprisingly both with and without TYPE directive, the XML size is the same – roughly 0.59 MB
varchar(max) variable is 0.9 MB and nvarchar(max) is the double 1.8 MB.

Let’s look at the CPU and elapsed time.

We can see that using the TYPE in our query saved some 6k reads, also the CPU and duration is half of the query without the TYPE. The non-xml variables are not doing bad performance wise, but also more reads and larger data type size.

Let’s compare the actual execution plan:

In the query without the TYPE there is an implicit conversion. The TYPE directive is saying that I want to generate an XML document. Without that I’m generating an nvarchar(max) and then implicitly casting it to XML.


SET @whatIsThis = 
	FROM sys.all_objects
	FOR XML PATH(''), ROOT ('Document')

That returns error message:

Msg 206, Level 16, State 2, Line 5
Operand type clash: nvarchar(max) is incompatible with sql_variant

I’ve repeated the tests but doing a cartesian product with several values to enlarge the XML. These are the results for 6 MB and 21 MB XML respectively. The varchar(max) size was 20 MB and 70 MB (nvarchar is doubled).

We can see that with an increasing document size, the benefits are even greater for such a little change. Thus earning its place in the Silver bullets series. I’ve run into processes that generated XMLs that were over a hundred MBs large and the savings were vast.

Next time, I’ll talk again again about XML trick, but instead of generating, we will be parsing.

It’s about… time

I haven’t written to this blog for almost 2 years – that’s a long time. Especially to pay for hosting that I’m effectively not using. So why did I start the blog?

One day, I fell in love with that idea – I can share the stuff I learned, maybe gather some feedback. Folks don’t have to rediscover America. They can learn from my mistakes. They can save time.

But once I got into it, the issues just piled up – I have to learn how to write a blog, WordPress, plugins, etc. Then I have to write posts with some frequency – it started to seem like a lot of work. Work I didn’t mind but didn’t have the time for. It didn’t seem like it can boost my career in any way.

My career; money-making. Time is money, right? If I don’t invest time in my skills, I can hardly make more money. Standing on shoulders of giants, I’ve read blogs, watched videos and presentations, gathering knowledge anywhere I can. Sometimes you invest some of the money to buy a better resource – a book or Pluralsight subscription, just to save time, but I think I’m wasting it.

What I’m lacking is a focus – for example, I’m learning about the problem at the hand, and immediately I start to imagine possible solutions, or even worse going off a tangent and start learning about a related problem. That’s why I have 4 books in progress now.

Also, where does it stop? I’ve started with SQL development, then data modelling dabbled in administration, trying to improve in automation, git, PowerShell, trying to at least know about Azure, SSIS, PowerBi.

But Jack of all trades is master of none. You have to pick useful skills but also skills that might be useful in the future. Something like a stock portfolio. But which ones are worth spending time on?

I’ve learned – just by an accident, really – that Paul Randal is offering to mentor a selected group of individuals. Now I’ve never had a mentor in my short career so far. Since I’ve started I have been a big fish in a small pond – seeking the challenge I’ve tried to move up. Since then I’ve settled in just about the right size of a pond, but have I moved too fast? Sometimes the imposter syndrome in me is real. While I wouldn’t say I’m self-learned, I wasn’t mentored either.

Now I’m on the fence about being mentored. I’m not in a bad place, but I don’t have a plan for what to do next. I always thought I would like to teach either as SQL lector or consultant, but is my knowledge good enough? Maybe now, getting advice from someone with more experience would help.

It’s about time…

Exploring SSMS

I’ll cover top menu, briefly introduce Registered Servers window and mention the most frequent sections of Object Explorer.

Top menu

Now the most important (and underrated) menu is the Quick Launch in the top right corner. From there you can just start typing what you are after.

Going left to right, we have:


Here you’ll find the typical New, Open, Save and Exit options. Nothing too exciting here.

File menu


Typical text editing and navigation options found in most editors. I recommend to use the hotkeys as much as possible and then this menu will become obsolete.

There is one interesting option:

  • Cycle Clipboard Ring
    • By pressing Ctrl + Shift + V you can access text previously saved to clipboard.
Edit menu


From this menu you can open the toolbar type of windows and edit toolbars as well. These are the main tools you will use:

  • Object Explorer
  • Registered Servers
  • Properties Window

But since you will open them once and keep them persisted (or learn the hotkeys). This is another menu you won’t frequent.

View menu


This option only becomes available when you have a New Query window opened.

As the name says – here you’ll find all query related commands.

But since the same commands are on the default toolbar just below the menu. We will ignore this menu most of the time as well.

Query menu
Query toolbar

Project and Debug

I’ll cover these two menus at once because I never use them. Since Project menu has no items (at least with no project opened). We’ll go straight to Debug menu then.

Debug has options start Debug, breakpoint and stepping through the code. But you never want to use this because when you start debugging, everyone else is blocked and waits for you and that is a huge problem. So never do it, there are better options how to debug.


A useful menu after a while, I’ll cover few options:

SQL Server Profiler – this is one of the debugging tools I have been talking about, but it is almost legacy now – instead we will cover Extended Events in a future article.

Options menu

Database Engine Tuning Advisor – to be fair I’ve never used it. I only had to clean up after it.

Code Snippets Manager – this tool is somehow lacking and that’s why there are plenty of commercial tools that make work with Snippets and ItelliSense much easier.

Import and Export Settings… – in the next article I’ll describe my recommended settings and this option will make it a one time operation even when you switch workstations.

Options – Everything else is in there. Even though I rather use the Quick Launch menu.


You can find some tab navigation here. But the most important option for me is the New Vertical Tab Group.

When I want to compare some code side by side or run code from separate sessions (for example blocking and deadlocking tests) this is my go to option.


Not much to say here. Typical help section with About section, where you can find your current version of SSMS.

Registered Servers

If you are working in multi-server environment, this is where you’ll save shortcuts to your saved server.

In a future post, I’ll cover how to set a default database after connect and recommend a colour scheme so you won’t run DEV code on production by accident.

Object Explorer

Here you will find all server related info in a structured format. Sometimes to get to the settings you must right-click and select properties.

Server object has the following structure, I’ve omitted some settings that aren’t that common, so you’ll have to do some exploring as well. Also, some folders can be expanded only to be empty, so don’t get fooled by that.

  • Databases
    • Tables
      • Columns
      • Keys
      • Constraints
      • Triggers
      • Indexes
      • Statistics
    • Views
      • Columns
    • Synonyms
    • Programmability – this is where you will spend most of the time in the begining
      • Stored Procedures
      • Functions
        • Table-valued Functions
        • Scalar-valued Functions
      • Database Triggers
      • Assemblies
      • Types
      • Rules
      • Defaults
      • Plan Guides
      • Sequences
    • Query Store – must be enabled in a database settings
    • Service Broker
    • Storage
    • Security
      • Users
      • Roles

Lots of these options won’t be used so much after we learn some DMVs.

Looking forward

This post only mentioned the options and not much else. I will describe individual option when I get to cover them properly.

Next time we’ll start writing some very basic SQL code and after that I’ll throw in some pro tips for productivity, writting style and SSMS settings.

Database playground


To get our own SQL up and running we are gonna need few things:

  1. SQL Server
  2. SQL Management studio (SSMS)
  3. Database filled with data

If you’re not preparing for a job in SQL and just want to try some SQL code, then perhaps this might be enough.


It’s a web portal where you can write queries directly agains the sample Stack Overflow database.


Everyone else, read on.



1. SQL Server

This is the server we will connect to. Normally companies have it on separate machine, but we will install it locally.


We have a choice between Express and Developer edition. Both are free and since Developer edition has a bit more functionality, we’re gonna go with that.


Download link


For speed we’re gonna go with the Basics installation. You can always change the details later.

1/3 Select the basic settings
1/3 Select the basic settings
2/3 Choose your instal location
2/3 Choose your instal location
3/3 Installation complete!
3/3 Installation complete!

2. SQL Management Studio (SSMS)

With this program we will connect to our newly installed SQL server. This is the tool you will use the most when writing SQL code. Always download and use the latest stable version, it doesn’t matter which version of SQL Server you’re using.


Download link


This installation is straightforward – no screenshots needed.

3. Database

We need some data to run our queries on.

Lucky for us, Microsoft provides us with that as well. We are gonna download Adventure Works database.


I’d recommend AdventureWorks2016_EXT.bak. You can choose any version, that is same or lower year than that of your SQL Server (ours is 2017).

Also be careful not to download the files that containt DW in their name – that means DataWarehouse and it has data stored differently than we need to.


This will require us to fire up SQL Management Studio (SSMS) and connect to our SQL Server.

I will write out the text, but there is a screenshot guide below.

  1. Open SSMS studio
  2. Connect to our SQL Server
    1. If you followed basic install it will be the name of your machine
  3. Open Object Explorer
    1. It might be opened as default
    2. If not use hotkey F8
  4. Expand the server, right click on the Database folder
  5. Choose option “Restore Database“
  6. In the popup on the “General” page select radio button “Device”
    1. Click on the ellipses …
    2. Click on “Add“
    3. Navigate to folder where you downloaded AdventureWorks2016_EXT.bak
  7. Back in the popup you should see the table populated.
  8. Click OK and it’s done, we have our database.
1/5 Connect to your SQL Server
1/5 Connect to your SQL Server
2/5 In Object Explorer right click on the Database, select Restore Database
2/5 In Object Explorer right click on the Database, select Restore Database
3/5 Select Device and click the ellipses
3/5 Select Device and click the ellipses
4/5 Click Add and navigate to folder with AdventureWorks database
4/5 Click Add and navigate to folder with AdventureWorks database
5/5 You should see the table populated
5/5 You should see the table populated

Looking forward

Next time I’ll write about basic navigation around the SSMS with some useful tips and tricks.