PowerShell for the SQL Server DBA - Variable Fundamentals

By: Alejandro Cobar   |   Updated: 2022-02-10   |   Comments (2)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > PowerShell

Problem

Moving forward with the tutorial series, PowerShell for the DBA, I would like to begin with one of the basic elements that will allow you to build scripts from the ground up, and that's variables. I know that it sounds very basic, but I think it's important to present the fundamentals on how they are declared, used, the datatypes available, and their scope within a script, so that you can write effective scripts for your DBA related tasks.

Solution

In this tutorial, we will learn how to declare, assign values and use variables in PowerShell, along with several examples to have a clearer understanding.

Initial considerations

• A very basic knowledge of coding in any other programming language will be assumed.
• No interaction with SQL Server will be covered in this tutorial, that will be covered in a future tutorial.

Important note: In the previous tutorials (see links at the end) we learned how to setup our environment of preference (Linux, MacOS, Windows) to be able to execute scripts. From this point forward I will be presenting all the examples using Windows, but that shouldn't be a showstopper for you to follow along.

Variables in General

Formally speaking, variables reside in the heap, which is a portion of memory allocated to a running program, and allocated by the Operating System. However, through them we can assign values which can either stay the same or change through the life-cycle of our program/script.

Now, an easier way to digest variables would be to think of them as individual boxes that can store something and the contents of the box can change (or not) over time.

All programming languages have their own set of rules in regards to variables, such as:

• Variable names cannot start with a number.
• If you define that a certain variable will be used to store only numbers, then you can't store strings there.
• The name assigned to your variable must be unique, within the same scope where it will be used.

These are just some examples, which can differ from language to language, so we will take a look at the rules defined for PowerShell.

Variables in Windows PowerShell

To work with variables in PowerShell, we have to do it the right way, or else we will fail to accomplish whatever it is that we're after, so let's dive in to learn how to do it appropriately.

Declaration of Variable in PowerShell

To declare a variable, you must start with a dollar sign ($) and then put the name you want for it.

Specifying Variable Data Type in PowerShell

To specify the data type of your variable, you have a couple of options: specify the data type yourself or let PowerShell figure it out for you.

Here's a list of data types that you can use:

Let's take a look at a few examples to see this in action.

Specify the data type within brackets syntax

[Int]$myFirstVariable = 5
Write-Host $myFirstVariable

Output

5

Don't specify the data type syntax

$myFirstVariable = 5
Write-Host $myFirstVariable

Output

5

Now, you might think: "If I don't specify the data type, how can I be sure that PowerShell will use the correct one?". Well in this case, based on the value assigned to the variable, PowerShell will automatically figure out what data type to use, but you can also check for yourself just to be 100% sure.

Here's how to check it:

$myFirstVariable = 5
$myFirstVariable.GetType().Name

Output

Int32

Note: Don't worry about that "weird looking" command .GetType().Name. Eventually, we will cover functions, properties and all the good stuff that PowerShell allows us to use.

Now, what would happen if you choose not to specify the data type of your variable and decide to store different data type values within your script? Let's find out:

$myFirstVariable = 5
$myFirstVariable.GetType().Name
 
$myFirstVariable = "Hello World"
$myFirstVariable.GetType().Name

Output

Int32
Cannot convert value "Hello World" to type "System.Int32". Error: "Input string was not in a correct format."

So, in this particular case, PowerShell sets the data type of the variable the very first time it encounters a value in the PowerShell session and sticks with it.

Now, let's evaluate a few additional scenarios just to see how PowerShell reacts.

Let's see what happens in the following example if I try to use the same variable to store values from different data types within the same script, but explicitly specifying the data types.

[Int]$myFirstVariable = 5
$myFirstVariable.GetType().Name
 
[String]$myFirstVariable = "Hello World"
$myFirstVariable.GetType().Name

Output

Int32
String

This does not return an error, just like the one from our previous example. In this case, PowerShell will stick to the String data type (in double quotes) from this point forward, during the whole lifecycle of the script, unless another explicit data type assignation takes place at some point within the script (it will basically forget that the variable was initially an integer).

Let's see what happens if I try to omit the first data type assignation, but specify a data type for the new variable in the second one.

$myFirstVariable = 5
$myFirstVariable.GetType().Name
 
[String]$myFirstVariable = "Hello World"
$myFirstVariable.GetType().Name

Output

String
String

Hold on, this doesn't make sense because the first declaration should return Int32, based on the fact that we know that PowerShell deduces the data type based on the value assigned right? Well, this is a tricky one that requires a bit of explaining from my side. See, I'm using PowerShell ISE to code this sample script and run it to see its output, but doing so in that way leaves the contents of the memory intact, in regards to my variables within my script, so the data type stored in memory will be String unless I either specify a data type for each variable declaration, or I close PowerShell ISE, fire it up again and run my script.

Therefore, let me close PowerShell ISE, open it up and execute my script a few times to see what it does:

See, the very first time it behaved "correctly", according to what we have learned so far, but the subsequent executions tell a different story (but now you know why it is doing this).

It is very important to note that this behavior will not be the same if you execute your script using the other options that we covered in the previous article, so let's give it a shot a couple of times to confirm this:

As you can see, either from the PowerShell console or the Command Prompt, the values are always the same because the contents of the memory, related to my script, are wiped as soon as the execution ends.

I know that I might have extended a bit on something relatively trivial, but it is very important that you are aware of these tiny details that might make your script do unexpected things. Now, to close this part, I'd just like to show you how to address this specific issue using the PowerShell ISE.

So, by explicitly defining the data type for each value assignation, it won't show the "weird" behavior that we saw before.

With all of this out of the way, let's continue exploring relevant information to properly work with variables in PowerShell.

PowerShell variables are not case-sensitive

Variables are not case-sensitive, so PowerShell will not differentiate between $myFirstVariable and $myfirstvariable.

Regardless of the lack of case-sensitivity while naming variables, you should still be careful while using PowerShell ISE because the same "weird" behavior might be experienced.

Assign Value to PowerShell Variable

You can assign a value to a variable using either a direct value or an assignment operator.

Assignment operators can be one of the following (taken from Microsoft's official documentation). The documentation includes a wide variety of examples that you can check out on your own, to expand what's being detailed within this article.

Here are some examples of different ways a value can be assigned to a variable.

For naming variables, you can make this complicated a bit if you like to use special characters, or names with spaces, something which is odd in practically the vast majority of languages out there.

As you can see, simply wrap the name of your variable within curly braces and you're good to go.

You can work with arrays pretty easily, as you will see in the example below:

Same as with arrays, you can work with hash tables pretty easily:

Scope of PowerShell Variables

What does scope of a variable mean? Well, a simple way to describe it would be in terms of the "visibility" of my variable within my script. However, I think this can be easily digested through some examples, so let's take a look at some.

Calling the exact same variable in two different functions within the same script.

In my script, I have defined 2 functions (we will cover functions in more depth in another article):

• Function f1 will display text and the value of the variable if the value of $myVariable is set, or it will display "I have nothing…" if the variable is empty.
• Function f2 simply assigns the string value "dummyValue" to $myVariable.

After defining both functions, I call them in the order depicted in the screenshot, and the output is what's interesting. The first time I call f1 it will display "I have nothing…" because $myVariable is empty, which is correct; however, after calling f2, which assigns a value to the variable, and then calling f1 again, then the result is exactly the same. This is because $myVariable is only visible within each individual function, at least in this particular example.

Let's try to address this using a special keyword available in PowerShell.

By introducing the keyword $global for my function f2, I'm telling PowerShell that the scope of $myVariable is global, which means that it will be visible in any part of my script. Now, as you can see in my example, I'm calling the functions in the exact same order as before, but the output is totally different. The second call of function f1 understands now that $myVariable has a value that was set in f2 and it displays it correctly.

Now that we've covered how to specify a global scope for a variable, we can also specify a local scope for it, either by using the keyword $local or by omitting it (just like the first example I showed). There are also a few additional keywords that can be used to specify the scope of a variable, but I won't be covering them in this article, since the usage of global and local scopes should be more than enough, at least initially, to use PowerShell for DBA related tasks.

Next Steps

• You can go through Microsoft's official documentation if you'd like to take a deeper dive on variable scope in PowerShell.
• Variables can seem to be a trivial thing in any programming language, but you must be familiar with all the details around them so that you can craft effective scripts.
• Even if PowerShell is relatively flexible with the fact that it lets you create variables with special characters and/or white spaces, you should try not to complicate things too much or your code might be a bit challenging to work with (especially for other people).
• Be as descriptive as you can with the naming of your variables, not just for PowerShell, but for any programming/scripting language that you decide to work with.
• Even if PowerShell lets you omit the data type for your variables, because it can figure it out automatically, I strongly recommend explicitly specifying it. It will make your code way more predictable and you will definitely avoid weird things like the example I showed you while working with PowerShell ISE.
• In the next tutorial of this series, we will be taking a look at if/else blocks and functions, so stay tuned!
• In case you missed it, check out the previous articles
  • PowerShell Commands for SQL Server – Getting Started
  • PowerShell for the SQL Server DBA – Environment Setup

About the author

Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips