Attempting SUM() OVER () in PowerShell

Words: 891

Time to read: ~ 5 minutes

Pro-Cras-Tin-Ation!

Like most things in life, this piece of work came about while attempting to complete something else. It’s not a bad thing, I expect it at this stage.

Easy Like Sunday Morning

I find it easy to get the total of a row in SQL. Hell, when it is not particularly important, I’ll even go the easy route and use a calculated column in the table.

CREATE TABLE dbo.PushupsOctober
(
	pushup_date date NOT NULL
		CONSTRAINT PK_PushupsOctober PRIMARY KEY CLUSTERED,
	attempt_01 tinyint NULL,
	attempt_02 tinyint NULL,
	attempt_03 tinyint NULL,
	attempt_04 tinyint NULL,
	attempt_05 tinyint NULL,
	attempt_06 tinyint NULL,
	attempt_07 tinyint NULL,
	attempt_08 tinyint NULL,
	total_pushups_per_day AS (ISNULL(attempt_01, 0) + ISNULL(attempt_02, 0) + ISNULL(attempt_03, 0) + ISNULL(attempt_04, 0) + ISNULL(attempt_05, 0) + ISNULL(attempt_06, 0) + ISNULL(attempt_07, 0) + ISNULL(attempt_08, 0))
);
GO

Then, all I have to do is insert the data and SQL will automatically take care of calculating the total per row for me.

INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/07/2020 00:00:00', 20, 20, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/08/2020 00:00:00', 20, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/09/2020 00:00:00', 20, 20, 25, NULL, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/10/2020 00:00:00', 25, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/11/2020 00:00:00', 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/12/2020 00:00:00', 25, 25, 25, 25, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/13/2020 00:00:00', 20, 15, 15, 25, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/14/2020 00:00:00', 30, 30, 20, 20, 25, 20, 20, 20);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/15/2020 00:00:00', 25, 25, 25, 25, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/16/2020 00:00:00', 25, 25, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/17/2020 00:00:00', 25, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
GO

SELECT	*
FROM	dbo.PushupsOctober;
GO
Why count when not need to?

Once you have the total per row, you throw in a SUM(that total) OVER () and you have a grand total. Thank you to Kevin Wilkie ( blog | twitter ) for re-igniting my curiosity about Window Functions again.

SELECT	*,
		SUM(p.total_pushups_per_day) OVER () AS total_so_far 
FROM	dbo.PushupsOctober AS p;
GO
Total total

Easy Like Monday Morning

PowerShell is a different beast. Please don’t get me wrong; I still love the language. I don’t find it easier to get a row total and then a grand total though.

It’s possible! I’m just hoping that there is a better way. Saying all that here is my attempt at a row total and grand total using PowerShell.

If you have a better way (you choose the conditions that satisfy “better”) please let me know.

Grabbing the Data

First, let’s grab the data from the table in our database.

$data_2 = Invoke-DbaQuery -SqlInstance localhost -Database LocalTesting -Query @'
SELECT * FROM dbo.PushupsOctober;
'@

Removing Unwanted Properties

Here’s where I remembered that I had a calculated column, realised that it would be cheating to use it and decided it needed to go. Thankfully, this also enabled me to get rid of those pesky columns that get returned from Invoke-DbaQuery when you forget the parameter -As PSObject!

$data_2 = $data_2 | Select * -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors, total_pushups_per_day

Grabbing Property Names

There’s a couple of things that we need here. We need a way to add up all the “attempt” columns so we need a way to select them all.

$props = $data_2[0].PSObject.Properties | Where-Object Name -like 'attempt*' | Select-Object -ExpandProperty Name

There, that should do nicely!

Grabbing the Values for those Properties

Now, we can iterate over them and get all the values.

foreach ($p in $props) { $data_2[0] | Select -ExpandProperty $p }

Potential Problem

See all those empty lines? Yep, that’s a potential problem for Measure-Object.

$hasToBeAnEasierWay = foreach ($p in $props) { $data_2[0] | Select -ExpandProperty $p }
$hasToBeAnEasierWay | Measure-Object -Sum

Removing NULL or WhiteSpace

Thankfully, there’s a way to get rid of those empty lines.

$hasToBeAnEasierWay | Where-Object { -Not [String]::IsNullOrWhiteSpace($_) } | Measure-Object -Sum

Putting it ALL Together

Now that we have the skeleton of a script ready, let’s put it all together.

Row total

$data_2 | ForEach-Object -Begin {
    $props = $data_2[0].PSObject.Properties | Where-Object Name -like 'attempt*' | Select-Object -ExpandProperty Name
} -Process {
    $total = $null
    $hasToBeAnEasierWay = $null

    $hasToBeAnEasierWay = foreach ($prop in $props) {
        $_ | Select-Object -ExpandProperty $prop
    }
    $total = ($hasToBeAnEasierWay | Where-Object { -Not [String]::IsNullOrWhiteSpace($_) } | Measure-Object -Sum).Sum

    $_ | Select-Object -Property *, @{
        Name = 'total_per_day'
        Expression = { $total }
    }, @{
        Name = 'days_left'
        Expression = { ((Get-Date -Date '2020-10-31') - (Get-Date -Date $_.pushup_date)).Days }
    }
} -OutVariable data_3 | Format-Table -Autosize

We now have the row total in our total_per_day property. And, with our use of -outvariable data_3, we have the results saved into a variable called $data_3 .

Grand Total

Once we have a single column that we can sum up to give us our grand total, then PowerShell makes this operation trivial.

I do have to use Format-List here because Format-Table can’t fit all the properties in so our new property total_so_far won’t show up.

$data_3 | Select-Object -Property *, @{
    Name = 'total_so_far'
    Expression = { ($data_3 | Measure-Object -Property total_per_day -Sum).Sum }
} | Format-List

There We Go!

While, I’d argue that it’s not as easy as SQL, it’s completely possible to get row totals and grant totals in PowerShell.

Honestly though, I hope there’s an easier way. Otherwise, I’m going to do it in SQL and then grab it out into PowerShell afterwards.

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.

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.

Security

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.

Performance

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.

Redundancy

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'};

Researching

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.


Using PowerShell for Lazy Maths

Words: 317
Time to Read: ~ 2 minutes

Pre-Coffee Question

I was asked today if I could figure out how many ways I could arrange the following equation to get the number 18?

// Replacing the "?"s with either "+", "-", "*", or "/" how many ways can you get 18?

2 ? 2 ? 2 ? 2 ? 2

Now I’m sure with enough time and effort that I probably could. But if you ask me this before I’ve had any coffee, the best you’re going to get is a dirty look.

Again, that’s the best that you’re going to get…

So I got lazy.

I know PowerShell.

I know that I can create the formula in PowerShell.

I know that I can invoke the formula in PowerShell to get the result.

So with an icy glare at the offending message and a sip at my scalding coffee, I create the script.

The Script

I’m not saying that this script is any good. Like I said, I wrote the script before the caffeine had hidden the tiredness from my brain.

It works, and that was the main thing for me at the time.

$operators = '+', '-', '*', '/'

$Permutations = foreach ($op1 in $operators) {
  foreach ($op2 in $operators) {
    foreach ($op3 in $operators) {
      foreach ($op4 in $operators) {
        $MatsIzHard = [scriptblock]::Create("2 $op1 2 $op2 2 $op3 2 $op4 2")

        [PSCustomObject]@{
          Formula = $MatsIzHard.ToString()
          Result = $MatsIzHard.InvokeReturnAsIs()
        }
      }
    }
  }
}
Defining the script and then calling the `$Permutations variable to get the results.
Like I said, seems to work…

Now that I have the results in the $Permutations variable, I can look for any results where the Result property is 18.

$Permutations.Where({ $_.Result -eq 18 })
2 rows returned from the `$Permutations variable showing 18 as the result
Yes, I can answer your question.

Yes, I can get 18 from that expression 2 ways…

On to my Actual Job

Overall that took around 3 minutes to complete. Which was the perfect time required for the caffeine to kick in and for me to be ready to start my day.

Now, that PowerShell was not pretty, dynamic, or efficient.
It was probably only fast because the number of iterations was so low.

But I’m happy since even though I can’t do maths before coffee, I can at least write PowerShell.

Minimum Permissions for Get-DbaDbUser

Words: 806

Time to read: ~ 4 minutes

Update: 2020-07-15 – Thank you Garry Bargsley for being an unofficial editor 🙂

Update: 2020-07-17 – Thanks to Shawn Melton for spot-checking this and letting me know ALL permissions needed!

TL;DR:
All Users:
A user on the database with ALTER ANY USER permission.

Current User and System Users:
To work against all databases for the current user and system users requires CONNECT ANY DATABASE.


Update


2020-07-17

Thanks to Shawn Melton for pointing out that CONNECT ANY DATABASE allows the user to see only themselves and the system users.

To see all users from Get-DbaDBUser, the caller will need a user on the databases and the permissions ALTER ANY USER.

CONNECT ANY USER

Get-DbaDbUser -SqlInstance localhost -SqlCredential $Cred -Database __DBA -EnableException -Verbose |
    Group-Object -Property Database

4 records are returned – the user itself and the system users.

User & ALTER ANY USER

USE __DBA;
GO

CREATE USER LimitedPermissions FROM LOGIN LimitedPermissions;
GO

GRANT ALTER ANY USER TO LimitedPermissions;
GO

The LimitedPermissions login now has a user in the database and we’ve granted that user the ALTER ANY USER permission.

Get-DbaDbUser -SqlInstance localhost -SqlCredential $Cred -Database __DBA -EnableException -Verbose |
    Group-Object -Property Database

Now, we can see all the users; the user itself, the system users, and the other user I created on the database.


Original Article


The Backstory

Work is in the process of automating tasks. Part of this automation includes verifying the automation that we’ve done.

Where am I going with this?

Well, when we’ve automated the creation of database users we also want to verify that we’ve created the users that we say we’ve created.

My fellow co-workers have, thankfully, seen the dbatools light and we use the command Get-DbaDbUser to get the users in a database and compare the list against the users we were supposed to create.

If there are any users that should have been created but don’t show up, well then we have a problem.

The Principle of Least Privilege

Works fine for me […] but it looks like […] can’t run it with her “public” access to the db server.

I’m not going to sugarcoat things – the person that sent me the request has more access than they rightly need. The “public” access worker did not need any of that access so I wasn’t going to just give her the same level.

Plus, we’re supposed to be a workforce that has embraced the DevOps spirit and DevOps is nothing if it doesn’t include Security in it.

So, if I could find a way to give the user enough permission to run the command and not a lot more, then the happier I would be.

But, I was surprised how difficult it was to find out what permissions were needed to run Get-DbaDbUser. Even more surprised when I failed and realised I’d have to find out myself.

If anyone else can Google/Bing it and get the answer, please let me know 😐

The Test

Let’s create a new user with no permissions in SQL Server.

USE [master];
GO

CREATE LOGIN LimitedPermissions WITH PASSWORD = N'MorePermissionsMoreProblems!';
GO

Now let’s test it out. I have a database in my instance called __DBA. Can we access the users in that database?

<#
    $Cred
    -----
    Username = LimitedPermissions
    Password = 'MorePermissionsMoreProblems!'
#>
Get-DbaDbUser -SqlInstance localhost -SqlCredential $Cred -Database __DBA -EnableException

It doesn’t work. What’s even more surprising is that it silently doesn’t work. No warnings, no permissions errors, or nothing. And I included the -EnableException switch!

The Investigation

It’s good to know that you can check out the contents of the dbatools (and other) commands from PowerShell. No, I’m not talking about opening the .ps1 files. I’m talking about using the Function:\ psdrive.

Get-ChildItem -Path Function:\Get-DbaDbUser |
    Select-Object -ExpandProperty Definition

See those $server.databases and $db.users? For me, that means that it’s using SMO (Server Management Objects). If there was any hope of me google/binging permissions before this, well it’s gone now.

The Will is going

To cut a rather long story short, eventually I came to the idea of thinking that maybe it only needs to connect to the database. So let’s try that.

USE __DBA;
GO

CREATE USER LimitedPermissions FROM LOGIN LimitedPermissions;
GO

And now let’s try our Get-DbaDbUser command again.

Get-DbaDbUser -SqlInstance localhost -SqlCredential $Cred -Database __DBA -EnableException -Verbose |
    Select-Object Database, Name, LoginType, UserType

Double-Checking

Let’s try all of the databases on the instance now

Get-DbaDbUser -SqlInstance localhost -SqlCredential $Cred -EnableException -Verbose |
    Group-Object -Property Database
Oh it has the system databases as well now!

Apart, from the system databases (excluding model) it only works on __DBA.

Give it all

Now, let’s use the CONNECT ANY DATABASE server permission.

USE [master];
GO
GRANT CONNECT ANY DATABASE TO LimitedPermissions;
GO

And we’ll run against all databases again.

Get-DbaDbUser -SqlInstance localhost -SqlCredential $Cred -EnableException -Verbose |
    Group-Object -Property Database

Sin é

That’s it! Minimum permissions that I could find for Get-DbaDbUser is the permission to connect to the database.

Hope that helps!

T-SQL Tuesday #127 – Non SQL Tips and Tricks

Words: 412

Time to read: ~ 2 minutes.

T-SQL Tuesday time! This month we have Kenneth Fisher ( blog | twitter ) as the host and he’s asking us for our non-SQL related tips and tricks.

Short…

I will confess to only starting this post late. So my tips and tricks will not be well thought out or planned. They will involve PowerShell though, something that I think about daily.

What we know

I consider it to be common knowledge that you can open up PowerShell from the explorer.

By default, my PowerShell opens up to “C:\Users\Shane”.

But by typing “PowerShell” into the location bar of an explorer, you can open a PowerShell session.

The PowerShell session will open to the location the explorer was open.

Et Viola

Reverse it

Did you know that you can drag and drop onto a PowerShell console?

Let’s create an empty text file.

New-Item -Name TestEmptyFile.txt -ItemType File

And we can see that it shows up in the open explorer location.

If we were to drag and drop the file into our PowerShell console window, it will return the full path to that file

Learn from History

If you spend a lot of time in a PowerShell console, it’s not rash to presume that you’re going to be running some of the same commands over and over again.

That’s where PowerShell’s history comes into play.

By using the command Get-History or even its alias h , you can see the commands that you’ve run before:

#Hashtag

Claudio Silva ( blog | twitter ) mentions in his T-SQL Tuesday post about using PSReadline’s HistorySearchBackward and HistorySearchForward.

I’ve fallen into the habit of using #.

Get-History returns an Id that we can use with our #. On our PowerShell console, if we want to run the 2nd command in our history, we only need to type #2 and then press Tab.

If we don’t know the Id but know a word, phrase, or substring of the command we can use #<word | phrase | substring of the command> to look through our history for the command.

So to find the command Get-History that we ran, we can use #Hist and then press Tab.

If it’s still not the right command, we can keep pressing Tab until we find the previous command that we’re looking for.

..but Sweet

I’m pretty sure I haven’t blown your socks off in amazement with these tips and tricks. But they work, they’re semi-useful, and they should be helpful.

I hope you knock some use out of them.

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

Investigation

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:

USE __DBA;
GO

/* 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')
BEGIN
    SELECT CreateFiles = 0;
END; ELSE
BEGIN
    SELECT CreateFiles = 1;
END;
'@

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

if ($CreateFiles) {
    [PSCustomObject]@{
        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 (
    [Parameter(Mandatory)]
    $JobName,

    [Parameter(Mandatory)]
    $StepName,

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

$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:
USE __DBA;
GO

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

Success!

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:

Finally

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”.

Pluralsight’s VS Code Extension

Words: 276

Time to read: ~ 1 minute

I’m a fan

I’m open about this – I like Pluralsight.

I admire the array of learning materials that they have for different topics. I find it reassuring seeing the names of Pluralsight authors and recognising them independently as knowledge leaders in their field.

I even have a personal subscription since I was devastated when I moved jobs and was removed from the old jobs plan!

So, even though it’s been out for a while now, when I saw that they now have an extension for VS Code it was enough to make me install it on my machine.

The Pluralsight extension page in VS Code

I’m currently going through some C# Fundamentals by Scott Allen ( twitter | blog ) on Pluralsight with an aim to work my way into Entity Framework.

With the Pluralsight extension on VS Code, I can open up some sample code file and Pluralsight will recommend some clips based on the file

List of Pluralsight clips based on the open file in VS Code.

Clicking on one of those clips will open a new tab in VS Code like below:

A tab in VS Code with a title, course description, and a button saying "Watch Clip"

Clicking the “Watch Clip” button will bring up a dialog from VS Code asking if you meant to go to an external website.

A dialog box from VS Code asking permission if you want to open an external website.

Opening or copying & going to the link will open up the Pluralsight video segment from the recommendations.

Insert Dad joke response here

Give it a go and see how it works for you. I use Azure Data Studio for my SQL code but I’m tempted to open up the .sql files in VS Code and see what Pluralsight recommends for them.

What do I think it will say? Who wrote this? Urgh! Kill it, kill it with fire!

… I never said I wrote good SQL…

T-SQL Tuesday #125 – Unit Testing Databases.

Words: 1344

Time to read: ~ 7 minutes

Welcome to the April edition of T-SQL Tuesday. This month we have Hamish Watson ( Blog | Twitter ) asking us about how we unit test our databases.

Performance Tuning

At one stage or another, I’m sure that we’ve all been asked to performance tune database code. Sometimes this tuning can involve changing the code. How do we know that the changes that we are introducing isn’t going to introduce bugs? You got it – Unit tests!

Set up

Let’s set up a contrived example – Contrived of course coming from the words “con” as in fake and “Tried V” as in I tried 5 times before I just gave up and used what I had.

USE [tempdb];
GO

/*
    Let's build this thang...
*/
CREATE TABLE [dbo].[Objects]
(
    [id] int NOT NULL CONSTRAINT [PK dbo.Objects(id)] PRIMARY KEY,
    [level] int NOT NULL,
    [object_type] int NOT NULL,
    [tag] nvarchar(256) NOT NULL,
    [description] nvarchar(max) NOT NULL,
    [parent_object_id] int NULL
);
GO

INSERT INTO [dbo].[Objects]
(
    [id],
    [level],
    [object_type],
    [tag],
    [description],
    [parent_object_id]
)
SELECT
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS [id],
    NTILE(10000) OVER (ORDER BY [S01].[alias]) AS [level],
    [M01].[severity] AS [object_type],
    [S01].[alias] AS [tag],
    [M01].[text] AS [description],
    NULLIF(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1, 0) AS [parent_object_id]
FROM [sys].[messages] AS [M01]
INNER JOIN [sys].[syslanguages] AS [S01] ON [M01].[language_id] = [S01].[lcid]
WHERE [S01].[alias] = N'English';
GO

SELECT *
FROM [dbo].[Objects];
GO

This will insert 11,466 records into the dbo.Objects table. The table has a column called parent_object_id that points to its … parent object id, wow you have been paying attention! Luckily, or unluckily, tuning the schema of the table is out of scope for this post

Next we have the piece of code that we’ve been asked to performance tune. It’s …uhm … well it works.

/*
    RBAR SQL
*/
CREATE FUNCTION [dbo].[GetChildObjects]
(
    @ObjectId int
)
RETURNS @ChildObjects TABLE
(
    [object_id] int NOT NULL,
    [level] int NOT NULL,
    [object_type] int NOT NULL,
    [tag] nvarchar(256) NOT NULL,
    [description] nvarchar(max) NOT NULL
)
AS
BEGIN
    --
    -- Insert first object.
    --
    INSERT INTO @ChildObjects
    SELECT
        id,
        level,
        object_type,
        tag,
        description
    FROM dbo.Objects
    WHERE id = @ObjectId;

    --
    -- Stack
    --
    DECLARE @Stack TABLE
    (
        ObjectId int NOT NULL,
        lvl int NOT NULL
    );

    --
    -- Initialise level for stack
    --
    DECLARE @lvl int = 1,
            @CurrentObjectId INT = NULL;

    --
    -- Insert current object
    --
    INSERT INTO @Stack
    (
        ObjectId,
        lvl
    )
    VALUES
    (@ObjectId, @lvl);

    --
    -- Loop through stack and get child objects
    --
    WHILE (@lvl > 0)
    BEGIN
        IF EXISTS (SELECT * FROM @Stack WHERE lvl = @lvl)
        BEGIN

            --
            -- Get object
            --
            SELECT @CurrentObjectId = ObjectId
            FROM @Stack
            WHERE lvl = @lvl;

            --
            -- Insert child objects
            --
            INSERT INTO @ChildObjects
            SELECT 
                id,
                level,
                object_type,
                tag,
                description
            FROM dbo.Objects
            WHERE parent_object_id = @CurrentObjectId;

            --
            -- Delete object from stack
            --
            DELETE FROM @Stack
            WHERE lvl = @lvl
            AND ObjectId = @CurrentObjectId;

            --
            -- Insert the childnodes of the current node into the stack
            --
            INSERT INTO @Stack
            (
                ObjectId,
                lvl
            )
            SELECT
                id,
                @lvl + 1
            FROM dbo.Objects
            WHERE parent_object_id = @CurrentObjectId;

            IF (@@ROWCOUNT > 0) -- If the previous statement added one or more nodes, go down for its first child.
                SET @lvl = @lvl + 1; -- If no nodes are added, check its sibling nodes
        END;
        ELSE
            SET @lvl = @lvl - 1; -- Back to the level immediately above
        END;
    RETURN;
END;
GO

Timing is important

Let’s get some times for this function. There’s nothing fancy here, we’re just going to call it for different values, returning more and more results and see how long it takes to finish.

/*
    Simple tezt...
*/
SELECT * FROM [dbo].[GetChildObjects](11466) OPTION (RECOMPILE); -- 0 seconds.
SELECT * FROM [dbo].[GetChildObjects](11000) OPTION (RECOMPILE); -- 3 seconds.
SELECT * FROM [dbo].[GetChildObjects](10000) OPTION (RECOMPILE); -- 10 seconds.
SELECT * FROM [dbo].[GetChildObjects](9000) OPTION (RECOMPILE); -- 17 seconds.
SELECT * FROM [dbo].[GetChildObjects](8000) OPTION (RECOMPILE); -- 23 seconds.
SELECT * FROM [dbo].[GetChildObjects](7000) OPTION (RECOMPILE); -- 29 seconds.
SELECT * FROM [dbo].[GetChildObjects](6000) OPTION (RECOMPILE); -- 36 seconds.
SELECT * FROM [dbo].[GetChildObjects](5000) OPTION (RECOMPILE); -- 41 seconds.
SELECT * FROM [dbo].[GetChildObjects](4000) OPTION (RECOMPILE); -- 48 seconds.
SELECT * FROM [dbo].[GetChildObjects](3000) OPTION (RECOMPILE); -- 54 seconds.
SELECT * FROM [dbo].[GetChildObjects](2000) OPTION (RECOMPILE); -- 61 seconds.
SELECT * FROM [dbo].[GetChildObjects](1000) OPTION (RECOMPILE); -- 68 seconds.
SELECT * FROM [dbo].[GetChildObjects](10) OPTION (RECOMPILE); -- 76 seconds.

Taking from 0 seconds for 1 row to 76 seconds for 11,456 rows, we now our timings.

As you can see there has been some performance tuning efforts by the code creators already. So impressed was I by the use of OPTION (RECOMPILE) that I asked why they asked me to take a look.

The execution plan seems fine; doesn’t look like there’s anything we can do with it…

Well, let’s take a look at the execution plan, shall we?

Ah!

No surprises

Listen, we both know that this needs to be rewritten. Here is the crux of the post – how do we know that our re-write isn’t going to introduce unexpected side-effects? Little happy “unspecified-features“? You got it – Unit tests!

Creating Unit Tests

To keep this brief, I’ve installed tSQLt on my tempdb database. The documentation for installing it is very easy to follow on the tSQLt website so head there for instructions. We’re going to create some tests for this function.


/*
    tSQLt time!
*/
EXECUTE [tSQLt].[NewTestClass] @ClassName = N'GetChildObjects';
GO

CREATE PROCEDURE [GetChildObjects].[test returns nothing for nothing]
AS
BEGIN
    -- Arrange (new "fake" table).
    EXECUTE [tSQLt].[FakeTable] @TableName = N'[dbo].[Objects]';

    -- Act
    SELECT
        *
    INTO #Actual
    FROM [dbo].[GetChildObjects](1);

    -- Assert (empty is empty).
    EXECUTE [tSQLt].[AssertEmptyTable] @TableName = N'#Actual'
END;
GO

CREATE PROCEDURE [GetChildObjects].[test returns 1 row if no child objects]
AS
BEGIN
    -- Arrange (new "fake" table).
    EXECUTE [tSQLt].[FakeTable] @TableName = N'[dbo].[Objects]';

    INSERT INTO [dbo].[Objects]
    (
        [id],
        [level],
        [object_type],
        [tag],
        [description],
        [parent_object_id]
    )
    VALUES
    (
        1,
        1,
        1,
        N'Don''t care',
        N'Absolutely don''t care',
        NULL
    );

    -- Act
    SELECT
        *
    INTO #Actual
    FROM [dbo].[GetChildObjects](1);

    SELECT
        1 AS [object_id],
        1 AS [level],
        1 AS [object_type],
        N'Don''t care' AS [tag],
        N'Absolutely don''t care' AS [description]
    INTO #Expected
    
    EXECUTE [tSQLt].[AssertEqualsTable] @Expected = N'#Expected', @Actual = N'#Actual';
END;
GO


CREATE PROCEDURE [GetChildObjects].[test returns multiple rows...say 4]
AS
BEGIN
    -- Arrange (new "fake" table).
    EXECUTE [tSQLt].[FakeTable] @TableName = N'[dbo].[Objects]';

    INSERT INTO [dbo].[Objects]
    (
        [id],
        [level],
        [object_type],
        [tag],
        [description],
        [parent_object_id]
    )
    SELECT
        [V].[number],
        [V].[number],
        [V].[number],
        N'Don''t care '+ CONVERT(varchar(2), [V].[number]),
        N'Absolutely don''t care '+ CONVERT(varchar(2), [V].[number]),
        NULLIF([V].[number] -1, 0)
    FROM [master].[dbo].[spt_values] AS [V]
    WHERE [V].[type] = 'P'
      AND [V].[number] > 0
      AND [V].[number] <= 10
    ORDER BY [V].[number]

    -- Act
    SELECT
        *
    INTO #Actual
    FROM [dbo].[GetChildObjects](7);

    SELECT
        [id] AS [object_id],
        [level],
        [object_type],
        [tag],
        [description]
    INTO #Expected
    FROM [dbo].[Objects]
    WHERE [id] >= 7
    ORDER BY [id];
    
    EXECUTE [tSQLt].[AssertEqualsTable] @Expected = N'#Expected', @Actual = N'#Actual';
END;
GO

Now we have tests to see what happens when the table is empty, when only 1 row should be returned, and when multiple rows are supposed to be returned. Hey! 4 rows still counts as multiple!

Running these tests couldn’t be simpler:

EXECUTE [tSQLt].[Run] @TestName = N'GetChildObjects';
GO

Re-Write time

The best piece of advice I heard for performance tuning, especially with a hectic schedule, is to

  1. Have a “good enough” goal and have a “time limit”

If the code has to run under a minute and you get it to 51 seconds after 30 minutes, great! Stop – I’m sure you’ve a to-do list that is only getting bigger.

/*
    All your bases are belonging to SET!!!
*/
DROP FUNCTION [dbo].[GetChildObjects]; --Don't worry, it's in source control.
GO
CREATE FUNCTION [dbo].[GetChildObjects]
(
    @ObjectId int
)
RETURNS TABLE
AS
RETURN
(
    WITH [Stack] AS
    (
        SELECT
            [O].[id] AS [object_id],
            [O].[parent_object_id]
        FROM [dbo].[Objects] AS [O]
        WHERE [O].[id] = @ObjectId


        UNION ALL

        SELECT
            [Obj].[id],
            [Obj].[parent_object_id]
        FROM [Stack]
        INNER JOIN [dbo].[Objects] AS [Obj] ON [Stack].[object_id] = [Obj].[parent_object_id]
    )
    SELECT
        [Det].[id] AS [object_id],
        [Det].[level],
        [Det].[object_type],
        [Det].[tag],
        [Det].[description]
    FROM [Stack] AS [S]
    INNER JOIN [dbo].[Objects] AS [Det] ON [S].[object_id] = [Det].[id]

);
GO

Wait! How do you… oh yeah. Unit Tests.

EXECUTE [tSQLt].[Run] @TestName = N'GetChildObjects';
GO

Perfect, now we can check out the timings!

SELECT * FROM [dbo].[GetChildObjects](11466) OPTION (RECOMPILE, MAXRECURSION 0); -- 0 seconds.
SELECT * FROM [dbo].[GetChildObjects](11000) OPTION (RECOMPILE, MAXRECURSION 0); -- 1 second.
SELECT * FROM [dbo].[GetChildObjects](10000) OPTION (RECOMPILE, MAXRECURSION 0); -- 3 seconds.
SELECT * FROM [dbo].[GetChildObjects](9000) OPTION (RECOMPILE, MAXRECURSION 0); -- 6 seconds.
SELECT * FROM [dbo].[GetChildObjects](8000) OPTION (RECOMPILE, MAXRECURSION 0); -- 8 seconds.
SELECT * FROM [dbo].[GetChildObjects](7000) OPTION (RECOMPILE, MAXRECURSION 0); -- 13 seconds.
SELECT * FROM [dbo].[GetChildObjects](6000) OPTION (RECOMPILE, MAXRECURSION 0); -- 15 seconds.
SELECT * FROM [dbo].[GetChildObjects](5000) OPTION (RECOMPILE, MAXRECURSION 0); -- 16 seconds.
SELECT * FROM [dbo].[GetChildObjects](4000) OPTION (RECOMPILE, MAXRECURSION 0); -- 18 seconds.
SELECT * FROM [dbo].[GetChildObjects](3000) OPTION (RECOMPILE, MAXRECURSION 0); -- 21 seconds.
SELECT * FROM [dbo].[GetChildObjects](2000) OPTION (RECOMPILE, MAXRECURSION 0); -- 23 seconds.
SELECT * FROM [dbo].[GetChildObjects](1000) OPTION (RECOMPILE, MAXRECURSION 0); -- 26 seconds.
SELECT * FROM [dbo].[GetChildObjects](10) OPTION (RECOMPILE, MAXRECURSION 0); -- 31 seconds.

Taking from 0 seconds for 1 row to 31 seconds for 11,456 rows, we now our new timings. And while I’m not a fan of the MAXRECURSION 0 I don’t have a major problem with adding it to an existing RECOMPILE statement.

A great side effect is that we have more information in the execution plan!

A wild index suggestion appeared

There we have it!

Thanks to our unit tests we have a more performant code to send back with confidence that it works the same as the old code…just faster.

Now if you’ll excuse me, my 30 minutes are up and my to-do list is growing.

Talking Data Bits

Words: 160

Time to read: ~ 1 minute.

Yes I am aware that these are normally posted directly after the podcast

I had the absolute pleasure of talking with Kevin Hill ( Blog | Twitter | Podcast ) on the first episode of his new podcast “Data Bits”.

You can check out Kevin’s blog post announcing it here and the actual podcast here.

It was an energising experience reliving past times, talking data tales, and cringing over shared scars received from working with data.

Next, Kevin had Andy Levy ( Blog | Twitter ) as his second guest on the show. It was uplifting hearing them talk shop and realising that maybe I should get more “aggressively friendly”.

You can check out the episode with Andy here.

I’m currently catching up with the next few episodes including Ginger Grant ( Blog | Twitter ), Jules Behrens ( Twitter ), and Louis Davidson ( Blog | Twitter ).

Thankfully, I’ve been able to scrounge some more time to listen to podcasts in this lock-down. There’s always an upside 😁!