Voice of the DBA

I saw an article on this and realized I had no idea how to do this, so I decided to practice a bit. I don’t work with PoSh arrays a lot, but with more and more DevOps work needing complex scripting, PoSh is a better environment for me than bash. At least for now.

This post looks at the basics of creating an array and then removing elements.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Creating a PoSh Array

Creating an array in PowerShell is relatively easy. I use a variable and the @ sign, enclosing the elements inside parenthesis with comma separation. In code, that looks like this:

$servers = @("server1", "server2", "server3")

or this:

$numbers = @(1,2,3,4,5,6,7,8,9,10)

If I were to reference an array, I’d get results like this:

PS E:\Documents\git\sqlsatwebsite> $servers
server1
server2
server3

PS E:\Documents\git\sqlsatwebsite> $numbers[2]
3

As you can see, I can get all elements or a single element with an index inside brackets. Note that the array is zero indexed as the index of 2 returns the third element.

In code, I might use other variables to represent the index or maybe to store the results of an array. If I were trying to brute force the removal of an element, I might decide to copy items one at a time (iterating through the index and skipping the copy if I reached an item to skip.

Removing an Element – Kind of

The way to remove this is deceptively simple. The article referenced above shows the easy way, which is to set an element to $null. I’d have never thought of that, as I’d assume this would leave the element, but change the key.

If I want to remove 5, I’d set $numbers[4]=$Null. Crazy, but let’s see this work. First, I’ll iterate the array, printing each index and the value with this code:

for ($i = 0; $i -lt $numbers.Length; $i++) {

Write-Host $i : $numbers[$i]

}

When I run this, I see these results:

0 : 1
1 : 2
2 : 3
3 : 4
4 : 5
5 : 6
6 : 7
7 : 8
8 : 9
9 : 10

Now, I’ll run this:

$numbers[4]=$null

Now we re-run the iteration and see these results:

0 : 1
1 : 2
2 : 3
3 : 4
4 :
5 : 6
6 : 7
7 : 8
8 : 9
9 : 10

Hmmm, the element is gone and isn’t. Nothing has moved, and in fact, the value of 6 remains in index 5. If I try to get element at index 4, I get this:

It’s not there, but the spot is held. That’s somewhat what I’d expect.

Removing an Item – The Better Way

PowerShell has a lot of flexibility in how variables work and how queries work. I found another blog that helped me understand a few things and experiment. Look at this code:

$numbers = $numbers | Where-Object { $_ -ne 5}

If I run my iterator on $numbers now I see:

0 : 1
1 : 2
2 : 3
3 : 4
4 : 6
5 : 7
6 : 8
7 : 9
8 : 10

That works much better, since I create a new array.

Arrays in PowerShell are funny things, and the docs show some of the methods available don’t change the size of an array, they just operate on it. There isn’t a remove method, which is interesting. There is a Remove-Item cmdlet, but even the docs say assigning $null is faster. However, it doesn’t get the indexing right.

If you have a better method, let me know.

SQL New Blogger

I spent about 10-15 minutes experimenting in PowerShell on various code elements trying to understand how different methods change the array. I read some docs, and ended up taking another 15 minutes to write this post and copy over some code.

This was fun, but it also showcased some learning and methodology. You could easily do the same thing, with PowerShell, T-SQL, or any other coding or scripting you do at work. Show how you experiment and learn.