Table Column Differences Part 03 – Compare-SqlTableColumns

Words: 470

Time to read: ~ 2 minutes

Don’t talk to me about it!

Four years ago (I know, where did the time go?), I wrote about Table Column Differences with T-SQL and PowerShell.

A Michal commented on the post, asking how to get a specific output from his search.


Thanks for your sharing. What if I also want to compare case sensitively columns and the order of them (syncwindows). How can I presented it on powershell.

I mean that in the final table I want to show also something like: column_a, column_A –> case sensitive


column_a, column_a –> different order in the table

Thanks in advance


I confess that I never got around to answering Michal until a few weeks ago when I found myself with some rare free time.

Since then, I’ve written a script, slapped it into a function, and threw it up on Github.

Here’s hoping that it does what you want this time Michal, thanks for waiting.

Shall I Compare Thee to Another Table?

The first thing that we need to do is have a couple of SQL tables to compare.

So, I threw up a Docker container and created a couple of tables with nearly the same layout.

(Get-DbaDbTable -SqlInstance localhost -Table 'dbo.DifferenceTable01', 'dbo.DifferenceTable02').Columns |
        Select-Object -Property Parent, Name, ID, DataType |
        Format-Table -GroupBy Parent
I’m liking the new PowerShell formatting

You can see that there are around three differences here

  1. Column orders, e.g. col9 has id 6 in dbo.DifferenceTable01 but id 5 in dbo.DifferenceTable02.
  2. Column case sensitivity, e.g. col7 does not match COL7.
  3. Column presence, e.g. col3 doesn’t exist in dbo.DifferenceTable01 at all.

While Compare-Object has the -CaseSensitive switch, I don’t think that it would be helpful in all these cases. Or else I didn’t want to use that command this time around.

So, I wrote a function to get the output we wanted, and yes, I now include myself among that list of people wishing for that output.

I’m allowed to be biased towards the things that I write 🙂


Compare-SqlTableColumns -SqlInstance localhost -Table1 'dbo.DifferenceTable01' -Table2 'dbo.DifferenceTable02' |

I’ve tried to include everything you could want in the function output, i.e. column names, column ids, and statuses.

Something I’ve started to do lately is wrapping a [Diagnostics.StopWatch] in my verbose statement to see where potential slow parts of the function are.

I’d like to think that 0.2 seconds for this example aren’t too bad.

$x = Compare-SqlTableColumns -SqlInstance localhost -Table1 'dbo.DifferenceTable01' -Table2 'dbo.DifferenceTable02' -Verbose

$x | Format-Table

Thou hast less columns than thine brother…

Feel free to use and abuse this function to your hearts content. I know that there are a few things that I’d add to it. Comparing across different instances being an obvious one that I’d like to put in.

Hopefully though, someone out there will find it helpful.

Here’s looking at you, Michal.

T-SQL Tuesday #143 – Short code examples

Time to read: ~ 2 minutes

Words: 328

Welcome to T-SQL Tuesday, the monthly blog post invitational where we’re given a topic and asked to write about it.

This month we have John McCormack (Blog | Twitter) asking, “What are your go-to handy scripts“?

For this post, I’m going to break these down into different languages.


I once had the annoyingly complex T-SQL to change MS format time into a human-readable format memorised.

    time_MS_format = [TimeMSFormat],
    converted_time = '2021-10-12 ' + 
            RIGHT('000000' + X.TimeMSFormat, 6), 3, 0, ':'
        ), 6, 0, ':'
   , ('00500')
   , ('01000')
   , ('10000')
   , ('10500')
   , ('100000')
   , ('100500')
   , ('115500')
   , ('120000')
) X ([TimeMSFormat]);

Then I read a blog post from Kenneth Fisher (Blog | Twitter) about the in-built msdb database function dbo.agent_datetime.

    time_MS_format = [TimeMSFormat],
    new_function = msdb.dbo.agent_datetime(20211012, X.TimeMSFormat)
   , ('00500')
   , ('01000')
   , ('10000')
   , ('10500')
   , ('100000')
   , ('100500')
   , ('115500')
   , ('120000')
) X ([TimeMSFormat]);

If I run sp_helptext on that function, it reminds me of that Andy Mallon (Blog | Twitter) post.

It would be more performant if I stripped that function and used the code directly but the code is too handy to use for the infrequent times I need it.


I’ve talked before about using ConvertTo-SqlSelect in a blog post before and I still use that function alot!

Another short piece of code that I use is more for formatting than anything else. You can populate a variable with an array of properties names. Select-Object can use this variable to return information.

 $Properties = 'SqlInstance', @{Name = 'DatabaseName'; Expression = {$_.Name}}, 'Status', 'RecoveryModel', 'Owner'

Get-DbaDatabase -SqlInstance localhost -SqlCredential $Cred | Select $Properties

A useful snipper for reporting is to use a combination of Sort-Object and the Format-* commands with the -GroupBy parameter.

Get-DbaDatabase -SqlInstance localhost -SqlCredential $Cred |
    Select $Properties |
    Sort-Object RecoveryModel |
    Format-Table -GroupBy RecoveryModel

Sin é

When I sit down and write this post, I realise that I don’t have a lot of handy scripts. Either I re-write things constantly (that’s likely), or I don’t know enough yet (also likely). I should fix that.


Words: 651

Time to read: ~ 3 minutes

Update 2021-06-17: It now accepts pipeline input

It’s been a busy month for me so there’s not a lot of outside work research that has been going on.

That being said, there has been quite a gap since I wrote a blog post so I figured that I might as well write something

So what do I have to write about?

SELECT Statements

There are times when I want to mess about with data in SQL Server, data that I have obtained in PowerShell. This will require some way to get the information from PowerShell into SQL Server.

I know of a few ways to do this.


There is the dbatools module and the Write-DbaDbTableData function.

Get-Help -Name Write-DbaDbTableData -Full

If I wanted to write the properties of 50 modules from PSGallery into SQL Server, I can use the function handy enough.

Find-Module | Select-Object -First 50 | Write-DbaDbTableData -SqlInstance localhost -Database WAT -Table dbatools_Insert -WhatIf


There is also the ImportExcel module and the ConvertFrom-ExcelToSQLInsert function.

Get-Help -Name ConvertFrom-ExcelToSQLInsert -Full
Find-Module | Select-Object -First 50 | Export-Excel -Path .\Documents\Excel\temp_20210614.xlsx;
ConvertFrom-ExcelToSQLInsert -TableName ImportExcel_Insert -Path .\Documents\Excel\temp_20210614.xlsx -UseMsSqlSyntax

Being Picky

Both of these were a bit too much for me though. I only wanted a quick and easy way to have the data available in a SELECT statement.

I can use ImportExcel and ConvertFrom-ExcelToSQLInsert but that is dependent on the table already existing, never mind having to save the data in an Excel file first.

Don’t get me wrong – I’m aware that you don’t need Excel installed on the computer where you’re running these commands from. You still need to save the files somewhere though. The function doesn’t take data from variables.

I can use dbatools and Write-DbaDbTableData. This function is not dependent on the table having to already exist. It will create the table for you if you tell it to. Thank you -AutoCreateTable; even though I recommend pre-sizing your columns if you want to go with this method.

However, I don’t want to have to create the table beforehand.


So I wrote a primitive function to have the data available in a SELECT statement that I can run in an SSMS or Azure Data Studio window.

You can find the code for it here on Github:

I can pass a bunch of objects into it and it will create the SELECT for me using the good ol’ VALUES clause.

Although I’m pretty sure this is basically what ORMs do under the cover before people who knew what they were doing looked at them…

ConvertTo-SQLSelect -Data (Find-Module | Select-Object -First 50)
… there’s more data here….


There are a couple of caveats to be aware of…

  • It doesn’t allow pipeline input.

It probably could but that would require a sit-down and think about how to do it. Like I said; this was a quick and dirty put-together function.

It now accepts pipeline input – although I’m sure it isn’t the best way I could have implemented that…

-999..1000 | ForEach-Object -Process { (Get-Date).AddDays($_) } | ConvertTo-SQLSelect
  • There are no data types.

There are strings and they get inserted as strings but that’s okay for me for a quick playthrough. Any data conversions, I can do once I have the data in an SSMS window.

  • It doesn’t like single quotes

Yeah, I have no real excuse for this one. I should really fix that before I use this function again…

It can handle single quotes now

  • There is also no help comments for this.

There should be, even though there is only one parameter. There should also be tests! I am filled with good intentions that are yet to see fruition though…

That being said, I’ve had to use it a few times already that has meant that writing it has already paid off.

So feel free to use, abuse, and/or improve it as you see fit.

I hope you find it useful.

Uncommon SQL

Words: 612

Time to read: ~ 3 minutes


Recently the DBA Team Lead and I were reviewing some SQL code, and we came across some SQL that neither of us had frequently encountered before. This led to a brief watercooler moment where we shared some uncommon SQL that we had seen. Perfect blog post material, I think.

ODBC Date Functions

From a previous post, I talked about ODBC date functions.

I’m using AdventureWorks2014 here.

/* The 10 employees who have been the longest at the company */

        {d '2006-06-30'} AS start_of_company,
        DATEDIFF(DAY, {d '2006-06-30'}, HE.HireDate) AS days_since_company_start
FROM    HumanResources.Employee AS HE
ORDER BY    days_since_company_start;


An unexpected item that we found recently was that INSERT INTO statements care about correct column names. That’s all though, nothing else seems to faze them.
This means that you can add the most ridiculous aliases or part names to the column and SQL Server won’t care. As far as I can tell, it will just ignore them.

/* Prefixes get prefixed */

    DROP TABLE dbo.Hires;

CREATE TABLE dbo.Hires (
    hire_id int IDENTITY(1, 1) NOT NULL
        CONSTRAINT [PK dbo.Hires hire_id] PRIMARY KEY CLUSTERED,
    job_title varchar(50) NOT NULL,
    hire_date datetime2(7) NOT NULL,
    is_on_salary bit NULL
        CONSTRAINT [DF dbo.Hires is_on_salary] DEFAULT (0)


WITH OldestHires AS (
        HE.JobTitle AS job_title,
        HE.HireDate AS hire_date,
        ROW_NUMBER() OVER (ORDER BY HE.HireDate) AS rn
FROM    HumanResources.Employee AS HE
ORDER BY    HE.HireDate
INSERT INTO dbo.Hires (
    [1/0].[%].[OUT_OF_BOUNDS].[   ].is_on_salary
SELECT  OH.job_title,
            WHEN OH.rn % 3 = 0 THEN NULL
            ELSE 1
        END AS is_on_salary
FROM    OldestHires AS OH;

FROM    dbo.Hires;

Default Option

Let’s contrive an example. Let us say that we have a table called dbo.Hires and we’ve added a column called is_on_salary.
Since most of the hires are salaried, we have added a new default constraint setting the value to 0.
Unfortunately, it looks like the default constraint hasn’t been applied yet…

/* Our dbo.Hires table */

FROM    dbo.Hires;


Recently, my DBA Team Lead pointed me to a piece of code where the syntax was: UPDATE T SET COLUMN = DEFAULT

Now, I had never seen this before, and I wasn’t quite sure that this method would work. I wasn’t wholly surprised, though when a quick test proved that it does.


UPDATE  dbo.Hires
SET     is_on_salary = DEFAULT
WHERE   is_on_salary IS NULL;

FROM    dbo.Hires;

What about with no default?

Okay, that seems to add the default constraint to a column. What about when there is no defined constraint on the column. Will it error out then?

/* Removing our default constraint */
    DROP CONSTRAINT [DF dbo.Hires is_on_salary]

SELECT  'Pre update' AS [status],
FROM    dbo.Hires;

UPDATE  dbo.Hires
SET     is_on_salary = DEFAULT
WHERE   is_on_salary = 0;

SELECT  'Post update' AS [status],
FROM    dbo.Hires;

Nope! As mentioned in the docs – if there is no default, and the column can become NULL, then NULL will be inserted.


Finally, we have CURRENT.
While the vast majority of scripts manually define the database context for commands, such as ALTER DATABASE AdventureWorks, etc., you can tell SQL Server:
Hey! Use the current database context!

/* CURRENT Database Context */


SELECT 'Pre change' AS [status], [name], page_verify_option_desc FROM [sys].[databases] WHERE [name] = N'AdventureWorks2014';


SELECT 'Post change' AS [status], [name], page_verify_option_desc FROM [sys].[databases] WHERE [name] = N'AdventureWorks2014';

And so forth

Thre’s probably a lot more but these are the ones that we talked about. If you have any uncommon SQL, let me know!

PIVOT in PowerShell

Words: 1151

Time to read: ~ 6 minutes


I’m going to start this post off with an apology.

As Kevin Feasel ( Blog | Twitter ) mentioned about my last post Attempting SUM() OVER () in PowerShell:

It’d be a lot easier, though, with a properly normalized data model which includes date, attempt number, and push-ups in that attempt. Pivot those results at the end if you want this sort of report, but SQL is designed to work best with tables in first normal form or higher.

Kevin Feasel

I can’t very well give out to people for not doing the right thing first time, even if it’s more difficult, if I don’t do the right thing myself!

As Kevin mentioned, once the data was in a proper format, a format designed for SQL, the calculations were trivial.

However, outputting the results in the same way in PowerShell required a way to pivot results in PowerShell. Thanks to some heavy lifting from Joel Sallow ( Blog | Twitter ), I now know how to pivot in PowerShell!

Here’s hoping that this post will help explain it for you also.

Exploring our Data


First off, let’s check the current state of our table in SQL.

SELECT	POP.pushup_date,
		SUM(POP.pushup_count) OVER (PARTITION BY POP.pushup_date ORDER BY POP.pushup_date) AS total_per_date,
		SUM(POP.pushup_count) OVER () AS grand_total
FROM	dbo.PushupsOctoberProper AS POP;
SQL style!


I want to get all possible 8 attempts horizontal like the last post. I find this fairly easy when I have the documentation for PIVOTs open in another tab.

/* Can we pivot these? */
SELECT	PVT_01.pushup_date,
		[1] AS attempt_1,
		[2] AS attempt_2,
		[3] AS attempt_3,
		[4] AS attempt_4,
		[5] AS attempt_5,
		[6] AS attempt_6,
		[7] AS attempt_7,
		[8] AS attempt_8,,
	SELECT	POP.pushup_date,
			SUM(POP.pushup_count) OVER (PARTITION BY POP.pushup_date ORDER BY POP.pushup_date) AS total,
			SUM(POP.pushup_count) OVER () AS total_so_far
	FROM	dbo.PushupsOctoberProper AS POP
	MAX(pushup_count) FOR attempt_number IN ([1], [2], [3], [4], [5], [6], [7], [8])
) AS PVT_01
ORDER BY	PVT_01.pushup_date;

Simple, right? Once we have the data in the expected format then the above steps are the only steps necessary to calculate and show the data in the way that we want.

However, it becomes a bit more complicated in PowerShell.


Let’s grab the data from our SQL instance and take a look at it.

<# Populate our variable from the database #>
$invQueryParams = @{
    SqlInstance = $sqlInstance
    Database = 'LocalTesting'
    Query = 'SELECT * FROM dbo.PushupsOctoberProper;'
$data = Invoke-DbaQuery @invQueryParams

<# Show our data #>
$data | Format-Table -Autosize
So far, so good…

Grouping our Data

We have our data fetched, now we need to group it by the different dates. If only PowerShell had a way to group objects…what? Group-Object? oh!

<# Grouping our data #>
$dataGroups = $data | Group-Object -Property pushup_date
Data.DataRow? * sigh* one of these days I’ll remember to use -AS PSObject with my Invoke-DbaQuery

Now that we have our data grouped by the different dates, we can loop through each date and pivot the data out horizontally.

Manual Pivot

The first way that came to mind was to manually list out all columns. I know that the maximum attempt_count that I have is 8 so let’s manually create 8 attempt columns.

<# Let's pivot this manually because it's the first way that came to mind #>
$ManualpivotedData = foreach ($dg in $dataGroups) {

        pushup_date = ($dg.Group | Select-Object -ExpandProperty pushup_date -Unique).ToShortDateString()
        attempt01 = ($dg.Group | Where-Object attempt_number -eq 1).pushup_count
        attempt02 = ($dg.Group | Where-Object attempt_number -eq 2).pushup_count
        attempt03   = ($dg.Group | Where-Object attempt_number -eq 3).pushup_count
        attempt04   = ($dg.Group | Where-Object attempt_number -eq 4).pushup_count
        attempt05   = ($dg.Group | Where-Object attempt_number -eq 5).pushup_count
        attempt06   = ($dg.Group | Where-Object attempt_number -eq 6).pushup_count
        attempt07   = ($dg.Group | Where-Object attempt_number -eq 7).pushup_count
        attempt08   = ($dg.Group | Where-Object attempt_number -eq 8).pushup_count
        total = ($dg.Group | Measure-Object -Property pushup_count -Sum).Sum
        total_so_far = ($data | Measure-Object -Property pushup_count -Sum).Sum

<# Let's make this pretty #>
$ManualpivotedData | Format-Table -Property pushup_date, @{ Expression = 'attempt*'; Width = 10 }, total, total_so_far
Seems to work

In case you’re wondering what @{ Expression = 'attempt*' ; Width = 10 } does, I use it to narrow the width of the columns named like attempt since they’re integers. Since they don’t need as much space, I can narrow them down and then Format-Table won’t cut-off my later columns!

Dynamic Pivot

I’m not against the manual way. I just find it too bulky and repetitve. It works! Please don’t get me wrong on that accout but as I recently heard someone say: “It works, now clean it up

Our main problem is the attempt columns and our manually typing them out. They seem like a perfect candidate for a ForEach loop. But, when we try to slot that in….

foreach ($dg in $dataGroups) {

    $props = @(
        @{ Name = 'pushup_date' ; Expression = { ($dg.Group | Select-Object -ExpandProperty pushup_date -Unique).ToShortDateString() }}
        foreach ($num in 1..8) {
                Name = "attempt_$num" 
                Expression = { $dg.Group |
                    Where-Object attempt_number -eq $num |
                    Select-Object -ExpandProperty pushup_count } 
        @{ Name = 'total' ; Expression = { ($dg.Group | Measure-Object -Property pushup_count -Sum).Sum } }
        @{ Name = 'total_so_far' ; Expression = { ($data | Measure-Object -Property pushup_count -Sum).Sum }}

    $dg | Select-Object $props
Well that shouldn’t be empty!

Yeah, that’s a “no” from PowerShell. Why is this?

Why this is

Let’s investigate that $props variable. We’re creating a hashtable where the Key is our name and the Value is the expression we want. So let’s get the values.

$props | Format-List
Expression = $num

Do you see the way that each of the Expression keys have a value with the $num variable?

If you check $num now, you’ll see that it’s set to 8. It looks like we have found our problem, the $props variable isn’t keeping the value of $num when we define it!

Since only one date has a value for attempt 8, we should see some values there.

$pivotedData = foreach ($dg in $dataGroups) {

    $props = @(
        @{ Name = 'pushup_date' ; Expression = { ($dg.Group | Select-Object -ExpandProperty pushup_date -Unique).ToShortDateString() }}
        foreach ($num in 1..8) {
                Name = "attempt_$num" 
                Expression = { $dg.Group |
                    Where-Object attempt_number -eq $num |
                    Select-Object -ExpandProperty pushup_count } 
        @{ Name = 'total' ; Expression = { ($dg.Group | Measure-Object -Property pushup_count -Sum).Sum } }
        @{ Name = 'total_so_far' ; Expression = { ($data | Measure-Object -Property pushup_count -Sum).Sum }}

    $dg | Select-Object $props

<# Let's check the 14th #>
$pivotedData | Where-Object pushup_date -eq '14/10/2020'
All filled but all with value for the 8th attempt!

Yeah…that’s not correct. I did 30 on the first attempt. Believe me, I remember the pain. Looks like it’s putting the value for attempt 8 into each of the attempts.

Not cool…


If only there was a way to keep the value of $num when we defined the $props variable. Well, thanks to Joel and his post ScriptBlocks and GetNewClosure(), I now know that there is!

$pivotedData = foreach ($dg in $dataGroups) {

    $props = @(
        @{ Name = 'pushup_date' ; Expression = { ($dg.Group | Select-Object -ExpandProperty pushup_date -Unique).ToShortDateString() }}
        foreach ($num in 1..8) {
                Name = "attempt_$num" 
                Expression = { $dg.Group |
                    Where-Object attempt_number -eq $num |
                    Select-Object -ExpandProperty pushup_count }.GetNewClosure()
        @{ Name = 'total' ; Expression = { ($dg.Group | Measure-Object -Property pushup_count -Sum).Sum } }
        @{ Name = 'total_so_far' ; Expression = { ($data | Measure-Object -Property pushup_count -Sum).Sum }}

    $dg | Select-Object $props

<# Let's make this pretty #>
$pivotedData | Format-Table -Property pushup_date, @{ Expression = 'attempt*'; Width = 10 }, total, total_so_far
It’s alive!!!!!


There’s nothing wrong with making mistakes; as long as you learn from them.

Thanks to Kevin for reminding me how things should be stored, and thanks to Joel for this (vast) knowledge sharing, I’ve been able to learn how to dynamically pivot in PowerShell from my mistakes.

Review your mistakes, you never know what you may learn.

T-SQL Tuesday #131: Data Analogies, or: Explain Databases Like I’m Five!

Words: 906

Time to read: ~ 5 minutes

Welcome to T-SQL Tuesday! This month’s host is Rob Volk (blog|twitter) and the topic is Data Analogies.


Come in! Come in, my boy!
Now, your father sent you to me to explain what I used to do.
Well, I was a DBA, my boy!
What do you mean “what does that do”? I took care of the databases! I made sure that they were looked after, that they had vim and vigour, and that they didn’t go down.

What? No, I don’t mean “down-down”, it’s not a direction! I mean that they didn’t go offline. No, not “off-off”, well not quite… It was my duty to ensure that they were available. Got it? No?

Database Administration

Well, let’s take that whiskey cabinet over there Jas…Jaeysin. Let us say that the whiskey cabinet is our database, and it is our job to see that it is never empty. We do that by lots of ways; by checking on it and making sure that nothing is alarming, like empty bottles. We check that there is space if someone wants to add more whiskey and that anyone who wants something from it, can get it. Like me, hehe.

What? You don’t understand how that’s like being a DBA? Well think about it my boy, I would check on the databases, make sure nothing was alarming, and that the data was always available for whoever wanted it.


What’s that? You want some? Ho ho, my boy, you are one for jests. I tell you what, try opening the cupboard door on the left. Yes, the one where you can see all the sticks and cherries through the glass. Not a problem for you, was it? Put back the cherry, please. And wipe your hands…NOT ON THE GLASS!
Nevermind, my boy, nevermind, I shouldn’t have put the soda water in a dangerous place like that…inside a cupboard…away from the ledge. Try and open the right cupboard door now. Yes, the one with the fancy bottles and the shiny lights. Yes, I’m aware it’s locked. Now see, you cannot open that door but I can because I have permission to open that door.
That was another part of my job, making sure that people had the right permission to get what they wanted, and that people without permission could not.

What’s that? Who decides on the permissions? Well, back then, it was a business decision. Where those higher up in standing would pick, and I would have to follow their instructions. Now, I’m in charge.

What do you mean that’s not what your father says? Right, well, I’ll be having a few words with him, and we’ll see more about this “under his partner’s thumb” business. No, I can’t open it either. Because I don’t have the key. Yeah well, I may be db_owner, but not sysadmin… Nevermind.


What else did I do? Well, I made sure those who wanted data from the database knew where they could get the data and could get the data promptly.
Well, do you see the whiskey cabinet? Yes, the one on the right. Yes, the one you’re not allowed open. Yes, you’re allowed to look…oi! Quit your cheek, or I’ll add that to the list of things I’ll have to talk to your father about.
Now, if someone were to go to that cabinet wanting a nice Scotch, they only have to reach into the middle shelf, and they would have a whole choice of Scotch to choose from. I know that because I know that my middle shelf is only for Scotch.
Same with the databases; I helped people to create tables to hold their similar data the same way I choose that shelf to have my Scotch.

And see the way that the bottles get fancier as you scan your eyes from left to right. I ordered that shelf, so the most expensive bottles were all the way over there on the right. And, the least expensive bottles are all over to the left.
Same with the databases; I would create indexes so that people could go to what they wanted, whether that be the “expensive bottles” or not.
No more looking through the entire table for what they want, they knew exactly where to go and knew when they could stop looking as well.

What? Indexes, my boy, an index. No, it hasn’t nothing to do with your finger! Wait! You may be on to something there. Tell me, have you ever run your index finger down a phone book? A phone book. You’ve never heard of it? Hold on…this thing? Ever seen this? Big yellow book, lots of information? Yes, I know we have Google, nevermind.


Was that it? No lad, that was not it. The world of data is vast and open. So much more than what an analogy using a whiskey cabinet can provide. But I will leave you with one more analogy; Redundancy. It’s always essential to have a redundant copy of your data, a recovery plan if a disaster were ever to strike.
Open that desk drawer for me, my boy. Yes, it’s another bottle of whiskey. A redundant backup, if you would. Now I believe I see your father pull into the drive, so it is time for you to leave. I shall go see if my backup can still be restored.

Goodbye, you little disaster.

ODBC date/time Extension Option in SQL Server

Words: 329

Time to read: ~ 2 minutes

At this stage of my career with Transact-SQL, I would like to think that I have the language mostly mapped. If there is something that seems new or unusual, then it often comes down to the developers marking the code to make replacing it more effortless.

In case you don’t grasp what I’m on about, the below piece of code shows it pretty well.

SELECT N'##datetime##' AS search_and_replace;

<clickbait>I had never seen this before</clickbait>

Can you imagine my shock when I came across a piece of code that not only was not for finding and replacing but even though I did not think it would compile, it did!

If you can imagine my shock, then you’re going to need to increase it more when I tell you that there are a whole family of the same functions!
Here is the code that threw me for a loop the first time I saw it.

SELECT {d '1970-01-01'};


There are more than just {d '<datetime>'} as well!

SELECT {d '2020-01-01'} AS sample_date,
       {t '12:31:56'} AS sample_time,
       {ts '2020-01-01 12:31:56'} AS sample_datetime;

There’s a whole page in Microsoft documentation about them.

It’s not just date and time functions but string function as well.

SELECT LEN(N'This string is 33 characters long') AS string_length,
       {fn BIT_LENGTH(N'This string is 33 characters long')} AS string_bit_length,
       {fn OCTET_LENGTH(N'This string is 33 characters long')} AS string_nvarchar_octet_length,
       {fn OCTET_LENGTH('This string is 33 characters long')} AS string_varchar_octet_length,
       DATALENGTH(N'This string is 33 characters long') AS string_nvarchar_datalength,
       DATALENGTH('This string is 33 characters long') AS string_varchar_datalength;

From whence camest thou?

My Google-Bing-Fu located these functions in the following link.
As well as a few answers on StackOverflow regarding querying SQL Server and A.N.Other datastore.

So now that I think I’ve grasped where they came from, will I be using these going forward?

No, thanks.

Oh, and in case you were wondering how I got the “ODBC date/time extension option” information, good error messages are irreplaceable.

SELECT {date '2020-01-01'} AS sample_date;

‘date’ is not a recognized ODBC date/time extension option.

Checking Job Step Output Mid-Job using PowerShell

Words: 627

Time to read: ~ 3 minutes

The XY Problem

Them: We have this job…

Me: Right…

Them: … and one of the steps in the job creates files…

Me: Okay…

Them: … and we need you to check if it creates the files, otherwise we don’t need to do any of the rest of the steps.

Me: Why don’t use just error out if that step fails?

Them: Cause there are other errors in that step but they don’t mean it failed

Me: … say what?

Pretty close representation of the conversation


I’m going to ignore the whole “there are other errors” for the moment and actually attempt this task. First, let’s try to see if there is a way to get the last time a job step ran.

I already have a test SQL Agent job called “TestEmail” so let’s use that for our test.

(Get-DbaAgentJob -SqlInstance localhost -Job 'TestEmail').JobSteps

Glancing at the output, it appears that we’re looking for the LastRunDate property. In this screenshot, it shows 1/1/0001 12:00:00 AM which means it’s never run before.

Creating Files

We need a way to create files, and not create files, on demand.

Vaguely in the back of my head (and I apologise for not remembering whom), I remembered someone using the presence of a temp table to fire or not fire a trigger. We’re going to use that premise here.

In a SSMS window, we have this code:


/* Create the files */
DROP TABLE IF EXISTS dbo.DoNotCreateFiles;

/* Do not create the files */
CREATE TABLE dbo.DoNotCreateFiles (DoNotCreateFilesID int NOT NULL);

If we want to create files from the PowerShell script, we need to drop the table.
If we don’t want to create files from the PowerShell script, we need to ensure the table exists.

Next, we create this PowerShell file which I’ve called “CreatePowerShellFiles.ps1“.

$Query = @'
IF EXISTS (SELECT 1/0 FROM [sys].[tables] WHERE [name] = N'DoNotCreateFiles')
    SELECT CreateFiles = 0;
    SELECT CreateFiles = 1;

[bool]$CreateFiles = (Invoke-DbaQuery -SqlInstance localhost -Database __DBA -Query $Query).CreateFiles

if ($CreateFiles) {
        Name = 'CreatedFile'
        DateCreated = Get-Date
        Nonce = New-Guid
    } | Export-Csv -NoTypeInformation -Path "C:\Users\shane.oneill\Desktop\TestPowerShellCreatedCode_$(Get-Date -Format FileDateTime).csv"

Adding this file as a step in our job, it checks for the existence of our table – if the table exists it does nothing otherwise it creates a sample csv file.

Now for the main course

We’re going to add another step now. This one will check for files created after the previous step has run.

First, we’ll create a PowerShell file (“CheckPowerShellFiles.ps1“).

param (


    [ValidateScript({ Test-Path -Path $PSItem })]

$Jobs = Get-DbaAgentJob -SqlInstance localhost -Job $JobName

$LastStep = $Jobs.JobSteps |
    Where-Object Name -eq $StepName

$FilesExist = Get-ChildItem -Path $FileDirectory |
    Where-Object LastWriteTime -ge $LastStep.LastRunDate

if (-not $FilesExist) {
    $ErrorMessage = 'Files were not created after {0}' -f $LastStep.LastRunDate
    throw $ErrorMessage

And add it to the job, passing in the parameters that we want:

Test Run

We’ve got two states that we want to test

  1. The files get created.
    1. Job should succeed.
  2. The files don’t get created.
    1. Job should fail.

Let’s run the first test:

  • Make sure the table is dropped so we create the files:

/* Create the files */
DROP TABLE IF EXISTS dbo.DoNotCreateFiles;
  • Now run the job:


Now to check that the job will fail if no files get created:

  • Make sure the table exists so no files get created:
/* Do not create the files */
CREATE TABLE dbo.DoNotCreateFiles (DoNotCreateFilesID int NOT NULL);
  • Now run the job:
Congrats, you have successfully failed

Taking a look at the job history, we can see our error message:


Well, we’ve proved that this method works!

I can pass on “CheckPowerShellFiles.ps1” to the people who requested the check telling them that they only need to add in the right values for the parameters…

Along with a polite note along the lines of “you should really fix your errors”.

T-SQL Tuesday #121: Gifts received for this year

Words: 401

Time to read: ~ 2 minutes.

T-SQL Tuesday, the monthly blogging party created by Adam Machanic, is still going strong. This months T-SQL Tuesday is hosted by Malathi Mahadevan ( blog | twitter ) who asks us…

“[…] what are a few things would you consider as gifts, and why?”

Invitation Post

There are many items that I consider gifts. More than I can reasonably write down in a blog post. So, in the interest of brevity, I’m going to break it down into gifts of SQL past, the gifts of SQL present, and the gifts of SQL future.

Gifts of SQL Past

I have been gifted with being mentored by DBAs that were knowledgeable and passionate about the Data Community.

I have been gifted with mentors who encouraged me to go to User Groups, volunteer at conferences and soak up as much data learnings as I could.

I have been gifted with mistakes that I have made in the past that have allowed me to learn from them. (I have definitely been gifted with the knowledge of how important test labs are!)

Gifts of SQL Present

I am gifted with a job that seems to actively repel me away from any sort of comfort zone. 

I am gifted with an active Data Community on Twitter, Slack, and in person for any issues that I run into.

I am gifted with a PowerShell community on Discord and Slack who seem to be constantly battling for the right to call themselves the “most welcoming community”.

I am gifted with a shrinking of borders so that anyone anywhere can be considered a helpful resource to a data question.

Gifts of SQL Future

I’m being gifted with new technologies to help out with old and new problems.

I’m being gifted with a field that is ever-changing. Fluid, dynamic, and will never become stagnant nor boring.

I’m being gifted with putting names to welcome faces and meeting up with new Data Community friends.

Mostly though, I’m being gifted with the opportunity to help others out in the future. Whether that be with blog posts, tweets, talks, examples, or just encouragement.

As good as all the previous gifts are, I believe the best gift is the gifts that you can give back. So, here’s hoping that the next year sees us all help more than hinder, learn more than laze, and teach more than troll.

Those are what I consider gifts.

T-SQL Tuesday #120 – What were you thinking?

Words: 712
Time to read: ~ 3.5 minutes

10 Years

T-SQL Tuesday is upon us once more. T-SQL Tuesday number 120 means something else as well. 120 monthly posts equals 10 years that Adam Machanic’s ( twitter | blog ) blog post party has been going on.

Wayne Sheffield ( twitter | blog ) is hosting this month’s event. Wayne asks us something that I’m sure we’ve all thought at some stage.

What were you thinking?

In the beginning…

The first exploration of a system leaves a lasting impact. When you first get a chance to delve into the database, you capture a shot of what the coding standards are like. You gleam the past experiences of the developers.

I’m looking for instances of NOLOCK if I’m being honest.

…there are impressions.

This impression was a What were you thinking? experience.

  1. DEADLOCK_PRIORITY LOW on most procedures.
  2. A lot of hierarchial data types.
  3. VARCHAR(MAX) on most columns
  4. Variables at the start of procedures used in equality WHERE clauses. e.g. DECLARE @Success int; Set @Success = 4; ... WHERE StatusId = @Success.
  5. Functions that return a single, deterministic value.
  6. Multi-statement Table-Valued Functions with WHILE statements.
  7. A plethora of indexes on the tables, all single-column indexes.

I’ve said enough.

If you had seen my face at that moment, you would have laughed. Imagine me staring, horrified, eyes darting around the screen mouthing What the…

A little thinking saves a lot of shouting

Granted it took getting a coffee and staring in disbelief at the code before I recovered. It took getting another coffee after the first before I could rationalise what I was seeing.

I took what I knew, which was these developers were smart. I tried to match that with what I was seeing. And there was an answer.

Theoretical, not Physical

The codebase read like developers who were not used to interacting with a database. Developers who thought of the database as a “place to shove data” and that’s all.

It was clear they had tried to follow the DRY (Don’t Repeat Yourself) approach (#4, #5).

They had read the documentation on hierarchical data types and Microsoft’s saying:…

The built-in hierarchyid data type makes it easier to store and query hierarchical data

…instead of a parent/child relationship tables. (#2)

They had tried to translate the .Net data type [string] into the database. Deciding that varchar(max) was its closest match. (#3)

They had tried to query the data in a row-by-row approach, instead of a set-based method (#6).

And, they had tried to deal with the consequences of these and other decisions. (#1, #7)

Understanding, not blame

It’s hard to stay annoyed at people when you can understand their motives. Their mindset is the most effective deterrent to anger I can think of. There’s no blame but understanding. You want to help them improve. And that’s where this on-going process is now.

To move away from multi-statement Table-Valued functions with WHILE statements. Here’s inline Table-Valued functions with a recursive CTE (Common Table Expression) instead.

To use variables when you have to but be aware of the change in statistics that it brings.

The difference it can make to a query and a database when the data types are apt. How memory grants, logical page reads, and more are affecting by blobs.

How DEADLOCK_PRIORITY LOW is not an option if every procedure has it! How indexes can be of more than a single column. That there is such a thing as an INCLUDES!

Seeing now that the driving force they have is to create features. But the pain force they feel is database performance. I can grok their choices and actions at the time.

Still, it didn’t stop me going What were you thinking? at the outset.

I’m no better

I’m trying to learn different languages and frameworks at the moment. If someone more knowledgeable was to come along and see my interactions with Linux. If they were to critique my Python files. Or attempt to suppress a groan at my PromQL. I’d appreciate an air of understanding, not blame at that time.

So well done to the people who dived in and attempted the work even if they didn’t know how at the time. To paraphrase; those whos face is marred by dust and sweat and blood deserve the credit.

But don’t think I didn’t see those TRANSACTION LEVEL READ UNCOMMITTED that you’re using as NOLOCKS!