Is there a UNION in PowerShell

Words: 462 487

Time to read: ~ 3 minutes

TL;DR: [System.Collections.Generic.HashSet<T>]

It has been a while ago since I’ve blogged so it seems fitting that this post will be about a question that was asked of me a while ago. The question was along the lines of “Can I join objects in PowerShell but remove duplicates?”.

So this is allowed:


But this isn’t allowed:


For me, it summed down to “Is there something like UNION or UNION ALL in PowerShell?

Luckily, this is something that I had asked before and been told the answer. So here I am, repeating the answer for you all since this is how I learn; repetition and practice.

The PowerShell type:

Don’t worry about the ‘<T>’. I didn’t figure it out either but I’ve been informed that it means a generic Type.

UPDATE: I have been reliably informed that this is a major understatement and I will update when I know more/learn more/am taught more about this.

An Example, not a Speech.

Let’s say we have similar but different objects.

$Boom = 1
$Blast = 2
$And = 3
$Ruin = 4

Now the question that we are asked is, if there is anyway to do a UNION on these objects?

Absolutely, we’ll create a hashset object and start putting these objects into the HashSet. I know these objects are integers so I’m going to put [Int] in for the <T> type.

#Create the HashSet object.
$HashSet = [System.Collections.Generic.HashSet[Int]]::new()

#Add the objects.
foreach ($Item in $Boom,$Blast,$And,$Ruin) {
Verbose by default

Now when you add something to a HashSet using the .Add method it returns either a True or a False.

true if the element is added to the HashSet<T> object; false if the element is already present.

If we then check the HashSet object, it returns both of our objects.

#Show us what you GOT!!!
and a 1 and a 2 and a…

If you don’t want to see the output of the .Add() method then you can push the output to one of the nulls e.g. $null = , [void] , > $null, | Out-Null, with the first two being placed at the start of the line and the last two at the end.

# Clear the HashSet.

# Add items and hide output.
$null = foreach ($Item in $Boom,$Blast,$And,$Ruin) {

# See if it still worked.
Hide and seek…

Check for Duplicates.

Now let’s see what happens if we try to add a duplicate object to the HashSet.

#Clear the HashSet

#Populate it again.
foreach ($Item in $Boom,$Blast,$And,$Boom){

#Check it again
No I don’t want no duplicates

HashSet sees the duplicate value, gracefully says no (False), and does not add it.

But wait, there’s more!

HashSet is something that I think DBAs will like as it is based on Sets. If you have the time, check out some of the other methods that it has.

#What else you got?
Get-Member -InputObject $HashSet
A set that contains everything except itself.

Take a look!

When does a Failing Pester Test return Green?

Words: 716
Time to read: ~ 3.5 minutes
TL;DR: Don't nest It blocks

I’ve been working more with different people in different departments more and more lately at work.

“Great” you might say and I would agree with you.

This has meant that any and all scripts that I write to give to these departments have to be tested so that they don’t fail when they are run.

“Great” you might say and, again, I would agree with you.


For PowerShell scripts, I’ve been using Pester. When I want to write a new function, I run a Pester command to create a .ps1 and a .Tests.ps1 file.

It’s a simple enough setup – I checkout a new branch for the command and switch to it.

git checkout -b NewFunction

I know that Pester has a command that I can use to create a framework of a new function and a framwork of a Pester test file for that function.

Get-Command -Verb New -Module Pester |
    Where-Object Definition -like '*create*scripts*tests*'

This returns the command New-Fixture and a quick scan of the help shows that this is exactly what we are looking for.

(Get-Help New-Fixture).Synopsis

This function generates two scripts, one that defines a function
and another one that contains its tests.

(Get-Help New-Fixture).Synopsis


An effect of working with more and more departments is that I have less and less time to spend on these functions. That’s meant that I’ve had to take a hard look at what would be a “nice to have” and a “must have”.

Because of this I’ve spent more time on planning and writing the tests first. Then I write my scripts to pass these tests and only these tests.

If it is a “must have” then it gets a test. If it has a test then the script gets that functionality, or property, or parameter, etc.

If it’s a “nice to have” then it doesn’t get a test. If it doesn’t get a test then it’s not in the script. When I have time later then I’ll go back and see if I’ll add it

I have yet to have time to go back and add stuff but then I’ve yet to have a need to add any of the “nice to have”s.

You may think that thinking about what’s needed and writing the tests takes time and you’d be correct.

But anytime spent on the planning part is saved by not spending time writing code that isn’t needed and getting it to work. Overall, it’s a major time saving technique.

Back on Track

Here is what is in the .Tests.ps1 file created from the New-Fixture command.

$here = Split-Path -Parent $MyInvocation.MyCommand.Path
$sut = (Split-Path -Leaf $MyInvocation.MyCommand.Path) -replace '.Tests.', '.'
. "$here\$sut"
Describe "<function name>" {
It "does something useful" {
$true | Should -Be $false

Contents of .Tests.ps1 file

It’s a nice template and all you need to do is modify that It block to have your test instead.

Here is where my Pester test failed and returned a green.

$here = Split-Path -Parent $MyInvocation.MyCommand.Path
$sut = (Split-Path -Leaf $MyInvocation.MyCommand.Path) -replace '\.Tests\.', '.'
. "$here\$sut"

Describe "Test-FakeFunction" {
    It "does something useful" {
        It 'should pass by deafult' {
            $true | Should -Be $false

It has a helpful message for you…

Do you see it?

The Reason

In modifying the template, I forgot to take out the original It block and just put my It block inside it.

This lead to my block “pass by deafult” failed
(as it should cause of that typo) but the original, parent block it was in “does something useful” passed!

It surprised me that I had a passing and failing tests since I thought I had only written 1 test but the maintainers of Pester included a helpful little error message for us.

You are already in a test case.

In the error message…3 times

Further Action

I’m an advocate of “if you see an issue, raise it on Github”. Even if you don’t fix the issue yourself, somebody else more than likely will come along and fix it for everyone.

Am I going to raise an issue for this on Github though?

  • An issue where it was due to my bad typing?
  • An issue where they already have it raised in the error message?
  • An issue where I accidentally put an It block inside an It block when I shouldn’t have?

For this instance, I think we’re okay…

Pester Testing Self Contained Scripts

Words: 1009

Time to read: ~ 5 minutes

    2019-02-22: Added a test for Jakub’s (don’t freak out, don’t freak out) comment.
    2019-06-13: Check out Jakub’s post on the topic – Such elegance

Continue reading “Pester Testing Self Contained Scripts”

Reflections: 2018

What a Difference a Year Makes

Words: 665

Time to read: ~ 3 minutes

2018 has been a busy year.


Let’s talk blog numbers for a moment… They say a picture paints a thousand words so I’m going to keep the word count down.

That’s 42,680 views across 34,920 visitors. Now seeing as last year it was 11,079 views across 9,061 visitors and that’s a 285% increase…yeah I’m pretty damn happy with that!

2018 was the year that I started to get more and more into the PowerShell community so I’m happy that 3 out of the top 4 posts were about topics that merges both the SQL Server and PowerShell communities.

I’m not going to read into the fact that my post with the highest views of 2018 was about me being a fan-boy of the work of somebody else. Mainly because it’s awesome and I’m a fan-boy of the work of a lot of people.

As for the Passive voice post, well it annoyed me and I was so happy to find the answer that I had to blog about it. Thank you to a certain German Fashion magazine forum for continuously sending traffic my way because of it!


SQLBits, SQL Saturday Oslo, SQL Saturday Reading, PSDay UK, etc.

It’s probably been my busiest year with regard to travel. Combine this with my moving back to Ireland from London in June this year and I’ve now reached the age stage where I can’t fully remember where I’ve been.

To combat this, I’ve enabled Google Timelines to help keep track of where I’ve been. Here is what it’s shown me since July 2018.

The amount of information that I’ve been taught, the people that I’ve finally met face to face, the beauty of the places I’ve been definitely makes me want to keep the travel going into 2019!

Shout out to Drew Furgiuele ( blog | twitter ) who I didn’t manage to meet when we were both in SQL Saturday Oslo. We’ll have our coffee/alcohol/workout/handshake eventually!


While it has been a busy year for blogging, travelling, and meeting people it has come at a cost. One that I’ve only felt lately when I sat down and tried to a brief forward plan of the upcoming year.

I’ve been receiving an enormous amount of PowerShell help from the PowerShell community. They are extremely welcoming, open, and willing to aid knowledge seekers (so much so that I’m having a fun little argument with them about which community is more welcoming: PowerShell or SQL 🙂 ).

Catching up with some SQL webinars and reading some of the SQL blogging community’s posts has shown me that I haven’t been keeping up to date with the SQL Server announcements and new technologies.

This is something that I am going to have to actively aim to rectify in 2019.


2018 was amazing for seeing a small subset of what someone can achieve with the technology out there in the world. To say that I’m bursting with ideas wouldn’t be far from the truth, thanks to a combination of procrastination and under-used time-management skills.

I’m going to be aiming to see if I can turn 2018, the year of ideas, into 2019, the year of actions.


Hard to believe that I’ve been blogging for 3 years but this will be the first reflection that I have done.

Overall I’m pleased with what I’ve accomplished, learned, and realised with 2018. Yes, even the realisation that I need to increase my effort on SQL Server learning.

What get’s measured gets done!

Looking forward, I’m not quite sure what’s next. I’ve a couple of SQL Server and PowerShell days/conferences that I’m looking forward to. I suppose it’s time to stop reflecting and start planning.

To everyone who I’ve encountered in 2018, thank you.

To everyone who has encountered me in 2018, I’m sorry. I need more caffeine at the time.

To everyone I’m going to meet in 2019, looking forward to it!

Learning C# methods in PowerShell don’t like -1

Trying to be smart getting me learned!

Words: 432

Time to read: ~ 3 minutes

It’s been a while…

…since I’ve written a blog post and there’s no better way to get back into something than to just start doing. Even if it’s just a throwaway, little post.

So here’s mine. Hope you enjoy it.

Don’t ask why…

… but recently I was trying in PowerShell  to split a string up into its individual characters. So, as an example, ‘SQL Server’ would become the ('S', 'Q', 'L', ' ', 'S', 'e', 'r', 'v', 'e', 'r') collection of characters.

I also wanted the character before it and after it as well…

This CharPrev CharNext Char
S e

The Easiest Way…

…that I found was to simply ask for the character a certain position (or index) in the string.

$String = 'SQL Server'
for ($i = 0; $i -lt $String.Length; $i++ ) {
'This Char' = $string[$i]
'Prev Char' = $string[$i -1]
'Next Char' = $string[($i +1) % $String.Length]
view raw UsingForSyntax.ps1 hosted with ❤ by GitHub

As you can see we had to add a little got’cha to our code: ($i +1) % $String.Length

This is because, without the modulo operator (what remains when we divide the numbers), PowerShell looks for the next index (10) and returns nothing since there is essentially nothing in index 10.

So we ask PowerShell what 10 modulo the length of the string is ( 10 % 10) and the remainder is 0. This way we can wrap back around to the start again!

However, coming from a database background…

…this may seem like a pretty simple exercise, especially since we know that PowerShell has a Substring method.

$String = 'SQL Server'
0..($String.Length 1) | ForEach-Object Process {
$String.Substring($_, 1)

However, what happens when we try and go backwards i.e. $String.Substring(-1, 1)?


Exception calling “Substring” with “2” argument(s): “StartIndex cannot be less than zero.


Try as I might…

…I couldn’t get any way that used “.whatever()” to work. Substring; nope, Chars; nada.

The moment that I passed in a -1 I just saw a sea of red.

Thankfully I’ve been frequenting the PowerShell slack channel lately and they were able to let me know why.

sifb [Nov 12th at 10:01 PM]
@Shane O’Neill I think the $array[-1] loop-around is a powershell convenience, and doesn’t exist in C# / the lower level .Net libraries

So there we go…

…even though we may be used to SubString, it doesn’t mean that it is the best way for us to go. This is technically a new language and there are going to be tips and tricks for doing things that we don’t yet know about!

Half the fun is rooting them out, finding them, and slowly, slowly watching your code improve and knowing why.

I would have written a shorter [post], but I did not have the time.