Publishing PSTimeSheets To The PSGallery

Words: 567

Time to read: ~ 3 minutes

Skip This for Your Sanity

I have created a module
cause nobody wants to do timesheets no more;
they want PowerShell to do it for ya.
Well, if this is what you need,
then this is what I’ll give ya.
(Ahem, apologies about that, songs get stuck in my head sometimes).

I Confess

I’ve worked with PowerShell for years but have never published a module before. I’ve helped write changes to a few, e.g. dbatools, dbachecks, and a few internal ones.

But the actual creating and publishing one needs adding to my experience list.

There was a lot of gnashing of the teeth, wailing of the cries, and reading of the documentation.

There were a few things that I wanted to do before I published.

Creating tests against all the functions; done.
Creating documentation for all the functions; done.

These were the easy sections; publishing the module was where I encountered the speedbumps.

So here’s a quick list of the aspects that threw me for a loop.

.PSD1 vs .PSM1 Files

I’m aware that the auto-loading of PowerShell modules boils down to a combination of the PSModulePath environment variable ($ENV:PSModulePath) and the .psm1 file for the module. But is there a default way to auto-generate that file?

I thought it was using the New-Module Manifest command, but nope, that creates the .psd1 file. At least I don’t have to worry about that.

The best practice is not to auto-load everything into the .psm1 file. It’s supposed to be more performant to re-create the functions’ definitions there. That’s not what I did.

Publishing

First of all, yes. Anyone can publish to the PSGallery – you need an account.

Did I know that you needed an account? Hell no.
Did I find out? Hell yeah.

To be fair, they say as much when you try to publish the module, asking you for a NuGetApiKey. Pop open your profile in PSGallery, and you can generate it from there.

Missing Values in the .PSD1 File

Remember a few paragraphs ago when I said I didn’t have to worry about the .psd1 file? Yeah, I was wrong. The command New-ModuleManifest is excellent. But, a few key features get missed from the default options.

The Description field doesn’t have an entry, yet it’s a required key to publish a module. Simple enough to open a text editor and update the values there; simple, if annoying.

This next bit is on me: after you have filled out the description field and tried to publish the module, you will get the same error message. That’s because the description field, starting off empty, will also be a comment. Re-open the editor, remove the hash/pound/octothorp that makes the field a comment, save, and you should be good to go.

NodeJS, I Think?

There were other tangles with the Publish-Module command that pushes to the PSGallery. I’ve chalked them down to a sinister combination.

The Linux knowledge needed for troubleshooting vs the amount of Linux knowledge I had.

I switched out of my WSL and tried to publish from my Windows Desktop. It went as smooth as… a very smooth thing.

Return 0

Overall, it was a simple process made more difficult due to lack of experience. Easy enough for anyone to pick up, annoying but unmanageable. Would I do it again?

Well, I’ve got improvements to make to PSTimeSheets, so… yeah!

Only One Join-Path Is Needed

Time to read: ~ 3 minutes

Words: 571

Update: Learning from my mistakes aka Failing Up

Update: Reliably informed that `-AdditionalChildPath` was added after 5.1

Join Me For a Moment

There’s a multitude of scripts out in the wild with a chain of Join-Path commands. Initially, when I wanted to create a path safely, a Join-Path cmdlets chain was also my go-to. However, after reading up on the documentation, I realised another way: I only need a singular instance of the Join-Path command.

Target Location

My PowerShell console is open in my home folder, and I’ve a test file: /home/soneill/PowerShell/pester-5-groupings/00-run-tests.ps1.

If I wanted to create a variable that goes to the location of that file, one of the safe ways of doing that is to use Join-Path.

Long Form

I mean, I could create the variable myself by concatenating strings, but then I’d have to take the path separator into account depending if I’m on Windows or not.

Apparently not…

$var = ".\PowerShell\pester-5-groupings\00-run-tests.ps1"

[PSCustomObject] @{
  Type      = 'Long Form'
  Separator = 'Manual entry: \'
  Variable  = $var
  Path      = try {Get-ChildItem -Path $var -ErrorAction Stop} catch {'Error!'}
}
Forward becomes back

I thought this wouldn’t work but, when running the code samples, it appears that PowerShell doesn’t mind me using a forward-slash (/) or a back-slash (\); it’ll take care of the proper separator for me.

UPDATE: This way works fine from a file but run the script from a PowerShell terminal and it’s a no-go.

No, you’re not the one for me

UPDATED UPDATE: Thanks for Cory Knox (twitter) and Steven Judd (twitter) for pointing out that this fails because it’s using /bin/ls instead of the Get-ChildItem alias:

Manual Creation

A more explicit, cross-platform method would be to use the [IO.Path]::DirectorySeparatorChar.

$sep = [IO.Path]::DirectorySeparatorChar
$var = ".${sep}PowerShell${sep}pester-5-groupings${sep}00-run-tests.ps1"

[PSCustomObject] @{
  Type      = 'Manual Creation'
  Separator = "[IO.Path]::DirectorySepartorChar: $sep"
  Variable  = $var
  Path      = try {Get-ChildItem -Path $var -ErrorAction Stop} catch {'Error!'}
}
The long way around

This method works fine but creating the path can get very long if I don’t use a variable. Even using a variable, I have to wrap the name in curly braces because of the string expansion method I used. That’s not something that I would expect someone picking up PowerShell for the first time to know.

-f Strings

In case you’re wondering, another string expansion method here would be to use -f strings.

$sep = [IO.Path]::DirectorySeparatorChar
$varf = '.{0}PowerShell{0}pester-5-groupings{0}00-run-tests.ps1' -f $sep

[PSCustomObject] @{
  Type = 'F String'
  Separator = "[IO.Path]::DirectorySepartorChar: $sep"
  Variable  = $varf
  Path      = try {Get-ChildItem -Path $varf -ErrorAction Stop} catch {'Error!'}
}
It’s hard to google for the F word

Many Join-Path Commands

Better yet would be if I didn’t have to account for the separator at all. Here’s where the multiple Join-Path cmdlets come into play.

$var2 = Join-Path -Path . -ChildPath PowerShell | Join-Path -ChildPath pester-5-groupings | Join-Path -ChildPath 00-run-tests.ps1
 
[PSCustomObject] @{
  Type      = 'Many join paths'
  Separator = 'Taken care of: Join-Path'
  Variable  = $var2
  Path      = try {Get-ChildItem -Path $var2 -ErrorAction Stop} catch {'Error!'}
}
One, Two, Many, Lots

Multiple Join-Path commands work fine. No real issue with people using this way, but there is another!

Only One Join-Path Needed

Join-Path has a parameter called -AdditionalChildPath that takes the remaining arguments from the command line and uses them in much the same way as a Join-Path command chain would.

$var3 = Join-Path -Path . -ChildPath PowerShell -AdditionalChildPath 'pester-5-groupings', '00-run-tests.ps1'

[PSCustomObject] @{
  Type = 'AdditionalChildPaths'
  Separator = 'Taken care of: Join-Path'
  Variable = $var3
  Path = try {Get-ChildItem -Path $var3 -ErrorAction Stop} catch {'Error!'}
}
One join to rule them all…

More Output than Put Out

So there you go—more than one way to join a path. Use whichever ones work for you. It’s good to know your options, though.

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.

Hi,

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

AND

column_a, column_a –> different order in the table

Thanks in advance

Michal

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

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

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 #135: The outstanding tools of the trade that make your job awesome

Welcome to T-SQL Tuesday, the brainchild of Adam Machanic ( twitter ) and ward of Steve Jones ( blog | twitter ).
T-SQL Tuesday is a monthly blogging party where a topic gets assigned and all wishing to enter write about the subject.
This month we have Mikey Bronowski ( blog | twitter ) asking us about the most helpful and useful tools we know of or use.

Tools of the trade are a topic that I enjoy. I have a (sadly unmaintained) list of scripts from various community members on my blog. This list is not what I’m going to talk about though. I’m going to talk about what to do with or any scripts.

I want to talk about you as a person and as a community member. Why? Because you are the master of your craft and a master of their craft takes care of their tools.

Store Them

If you are using scripts, community-made or self-made, then you should store them properly. By properly, I’m talking source control. Have your tools in a centralised place where those who need it can access it. Have your scripts in a centralised place where everyone gets the same changes applied to them, where you can roll back unwanted changes.

Check out Brett Miller’s ( blog | twitter ) presentation “GitOps – Git for Ops people“.

Version Them

If you are using community scripts, then more likely than not, they are versioned. That way you’re able to see when you need to update to the newest version. No matter what language you’re using, you can add a version to them.

PowerShell has a ModuleVersion number, Python has __version__, and SQL has extended properties.

Or even take a page out of Bret Wagner’s ( blog | twitter ) book and try XML comments.

Take Care of Them

If you take care of these tools, if you store them, version them, and make them accessible to those who need them, then they will pay you back a hundredfold.
You’ll no longer need to re-write the wheel or pay the time penalty for composing them. The tools will be easy to share and self-documented for any new hires.
Like the adage says: Take care of your tools and your tools will take care of you.

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.

Updating the Account Password for my Constrained Endpoint

Words: 448

Time to read: ~ 2 minutes

A while ago, I was asked about Automation and told a story about my friend James.

In that story I talked about Constrained Endpoints and how, by using them, I could do a take the best bits of automation & delegation and not have to worry about unlocking James anymore.

Well, I was wrong

A while after I created that Constrained Endpoint, I was greeted one day by James saying he was receiving a weird error when he tried to unlock his account.

Connecting to remote server ‘server_name’ failed with the following error message : The creation of a new
Shell failed. Verify that the RunAsPassword value is correctly configured and that the Group Policy setting “Disallow
WinRM from storing RunAs credentials” is Disabled or Not Configured. To enable WinRM to store RunAs credentials,
change this Group Policy setting to Disabled. For more information, see the about_Remote_Troubleshooting Help topic.

CategoryInfo : OpenError: (‘server_name’:String) [], PSRemotingTransportException
FullyQualifiedErrorId : CannotConnectRunAsFailed,PSSessionStateBroken

Unlock James has turned into Error James.

The fact that this occurrence came the day after I had reset my password, and the fact that the error message contained the words “[v]erify that the RunAsPassword value is correctly configured” was not something that was lost on me.

Luckily, PowerShell is fabulously easy to explore with it’s Get-Help command so it was a simple case to look for commands around Session Configurations – Get-Command -Name *Session*Configuration* – and look at the help contents of the Set-PSSessionConfiguration cmdlet.

Make sure you include proper help in your functions, it’ll help you immensely when you come back to it after some time.

Get-Help -Name Set-PSSessionConfiguration -Detailed
Also available by using help Set-PSSessionConfiguration -Parameter RunAsCredential

The Fix this Time

The evidence pointed to me needing to update the RunAsCredential of the endpoint and that was the overall fix for this issue.

Set-PSSessionConfiguration -Name UnlockJamesEndpoint -RunAsCredential (Get-Credential)

I threw in my username and my new password, did a quick test to see if the endpoint was available for me ( it was ), asked James to test that it was available for him ( it was ), and I closed off the ticket.

Aesop Out

Constrained Endpoints are not a technology that I am familiar with yet. It’s nice to know that I can take a look at the error messages, use some troubleshooting processes – check out the book “How to Find a Wolf in Siberia” by Don Jones ( blog | twitter ) – and figure it out.

Then again, the technology world is filled with new technologies and if you have a job where you know everything about your technology stack then congratulations to you.

For everyone else, get used to not knowing. Network, Search, Learn. You’ll be obliviously proficient in no time!

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
SrQ
QSL
LQ 
 LS
S e
eSr
rev
vre
evr
reS

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++ ) {
[PSCustomObject]@{
'This Char' = $string[$i]
'Prev Char' = $string[$i -1]
'Next Char' = $string[($i +1) % $String.Length]
}
}

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)?

Nope!

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

Nope!

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.

 

Table Column Differences with T-SQL and PowerShell – Part 2

If this was a horror movie, it would be called “The Differencing”…duh duh duh!

Time to read: ~ 5 minutes

Words: 1057

Update 2021-07-14: Marked code blocks as preformatted

The original post for this topic garnered the attention of a commenter who pointed out that the same result could be gathered using a couple of UNION ALLs and those lovely set-based EXCEPT and INTERSECT keywords.

I personally think that both options work and whatever you feel comfortable with, use that.

It did play on my mind though of what the performance differences would be…what would the difference in STATISTICS IO, TIME be? What would the difference in Execution Plans be? Would there even be any difference between the two or are they the same thing? How come it’s always the things I tell myself not to forget that I end up forgetting?

I have no idea about the last one but at least the other things we can check. I did mention to the commentor that I would find this an interesting blog topic if they wanted to give it a go and get back to me. All I can say is – Sorry, your mail must have got lost in transit. I’m sure it is a better blog post that mine anyway.

If you’re going to do it…

For this test, we’re not going to stop at a measely 4 columns per table. Oh no! For this one we’re going to go as wide as we can.

With a recent post by Kenneth Fisher ( blog | twitter ) out about T-SQL FizzBuzz, I’m going to create two tables, both of which will have incrementing column names i.e. col00001, col00002, …, col1024. Table1 will have all columns divisible by 3 removed while Table2 will have all columns divisible by 5 removed.

See, FizzBuzz can be useful!

So our table creation scripts…

SELECT TOP (1024)
CASE WHEN v.number = 0
-- Change this to 02 the second run through
THEN N'CREATE TABLE dbo.TableColumnDifference01 ('
ELSE N' col' + RIGHT(REPLICATE('0', 8) + CAST(v.number AS nvarchar(5)), 4) + N' int,'
END
FROM master.dbo.spt_values AS v
WHERE v.type = N'P'
AND (
-- Change this to '% 5' the second run through
v.number % 3 != 0
OR v.number = 0
)
FOR XML PATH('')

TableCreationScript
See Note

NOTE: When you copy and paste the results of this query into a new window to open it, it is going to fail. Why? Well the end of the script is going to be along the lines of colN int, and it needs to be colN int). Why is it like this? Well it was taking to damn long to script that out. Feel free to change this to work for you. Hey if you do, let me know!

Now, how I’m going to do test this, is run each method 3 times (PIVOT, UNION, and PowerShell), then measure the third run of each method. This is mainly as I want to get rid of any “cold cache” issues with SQL Server where the plan has to be compiled or the data brought into memory.

…do it Pivot

So first up is the Pivot method from the last blog post. In case you’re playing along at home (and go on, do! Why should kids get all the fun) here is the code that I’m running.

And here is our results:

PivotMethodGridResults
Yup, those be columns

What we are really after though is the stats, execution plan and time to complete for our 3rd execution. Now as much as I love reading the messages tab for the stats information, I feel with blog posts that aesthetics is king, so I’m going to be using the free tool by Richie Rump ( twitter ) “Statistics Parser

Stats:

PivotMethodGridStats
Elapsed time: 00:00:00.136

Execution Plan:

PivotMethodGridPlan
Probably the first plan I’ve seen where the SORT isn’t the most expensive!

..do it UNION

Secondly we have what I dubbed “the UNION method” (no points for figuring out why) and the only change I’ve made to this script is to add in PARSENAME() and that’s only so that the script would..you know…work.

Results be like:

UnionMethodGridResults
Yep, Yep, Yep, Yep, Nope, Yep…

Stats:

UnionMethodGridStats
Elapsed time: 00:00:00.624

hmm…less Scan Counts but 5 times the reads…also 5 times slower than the PIVOT method. Maybe the execution plan will be prettier?

Execution Plan:

UnionMethodGridPlan.png
ehh…WHAT!

Yeah…so…that’s…that’s different from the first plan! I was right in my comment though, there is a concatenation operator (there’s actually 2, you may need to zoom in to find them though)

…do it PowerShell

Finally we have the PowerShell method. No messing about here, let’s get straight to it! I’m going to lump all the code together in one gist and I’ll be wrapping it in Measure-Command to get the speed of the command.

Get-Results

PoSHMethodGridResults
Yeah I’m liking VS Code more and more…

Get-Stats:

PoSHMethodGridStats.png
Elapsed time: 00:00:00.249

help *execution*; help *plan*

Would you believe that I couldn’t figure out how to get an execution plan for PowerShell 🙂

If anybody knows, hit me up!

Finishing off

You know at the start of this, I was fully expecting the PowerShell to win out, followed by the UNION method, because it’s use of UNION, EXCEPT, and INTERSECT which are basically made for this kind of problem, and the PIVOT method bringing up a distant last since PIVOTs have this complexity stigma attached to them and what is complex is normally slow.

From a sheer speed point of view, the actual results are:

  1. Pivot
  2. PowerShell
  3. Union

Who knew!?

I don’t think this is the end of my use of PowerShell or Union operators though. I’m not going to replace all the stuff that I can with Pivots. For one I just think that PowerShell and the Union operators are just too cool!

I actually like this result for two reasons.

  1. There are multiple way to do something in SQL, there are good ways and better ways. The main point is whatever option you choose, make sure you know what it entails and can justify it.
    Whatever works for you, works for you!
  2. You don’t know something, test it and find out! What you think the outcome may be, may not be true.

Now if you’ll excuse me, I want to figure out if there’s a way to return execution plans with PowerShell.