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.

My Function Won’t Accept Parameters? Get-Help!

Getting Get-Help Help

The following is a recounting of an issue that I had and how I went about resolving it. No computers were harmed in the making of this post.


Ask me for one PowerShell command that everyone should know and I can answer you: Get-Help.

Fairly descriptive name if you ask me. Today I’m focusing on using Get-Help selectively to help me figure out why my custom function just won’t accept parameters!

You say Test Case. I say Basket Case.

We are going to need a custom test function for the audience to play along with at home, luckily Shane’s got you covered.
This is a Tactical Estimation of Shane’s Test function – aka T.E.S.T. function; very simple but all the important parts are there.

Function Test-FunctionByParameter {
    [cmdletbinding()]
    Param(
        [Parameter(Mandatory = $true,
                   ValueFromPipelineByPropertyName = $true)]
        [string]$Parameter
    )
    process {
        "Success, I'm [$Parameter]"
    }
}

If I’ve done my maths right, and I always do my maths right (as far as you know), then this function should take input from the pipeline and output it in the string “Success, I’m …”

Do I do my maths right?

Get-Service -Name *sql* |
Select Name -first 1 |
Test-FunctionByParameter

FirstFailure
It’s the “carrying the 1” that always gets me!

Huh, parameter problem.

I thought this was supposed to work like this. You pipe in information, magic happens, and functions work, no?

Well, when in doubt, Get-Help.

Get-Help:

Before I go any further though, just so that everyone knows how to use Get-Help, I’m going to show you one of the secret techniques for using Get-Help.

Get-Help *help*

GetHelp_Help

Yup, I use dbatools

Why does help exist?

When you think about it, why is there even a function called help?
As far as I’m aware it’s basically the same as Get-Help except it automatically pipes the output to | more so we get pages rather than a wall of text.

Is there more that we can do with Get-Help though? Is there a way that we can return the examples only? Syntax only? Parameters only?

Is there not a way that we can do such things?!

Yessum, the Possums cousin

Okay I cheated on the first one; examples are pretty easy. PowerShell actually already takes care of that for you.

Get-Help Get-Help -examples

GetHelp_GetHelp_Examples
Help me if you can I’m feeling examples…I mean down!

The other two, while not laid out for you as pretty as that, are not that difficult to do. What needs to be remembered about Get-Help is that it is a cmdlet. And what do cmdlets normally output?…

What?! No! Objects!
They normally output Objects! Wow…next time just pipe it to Get-Member if you don’t know.

I Object!

Now I first saw this done in a blog post by Adam Bertram ( blog | twitter ) but I do believe that it warrants further highlighting.

If you did pipe Get-Help to | Get-Member you would have seen a NoteProperty called syntax, so if we want the syntax for a cmdlet, we can specify that using:

(Get-Help Get-Help).syntax

GetHelp_GetHelp_Syntax.PNG
Syntax, useful for all languages

So for parameters we need…yup .parameters.

(Get-Help Get-Help).parameters

GetHelp_GetHelp_Parameters
Parameters…languages use them as well I guess

Hmm, not as handy as I thought it would be. What happens if we pipe that to Get-Member (Alias gm as I’m getting lazy here)?

(Get-Help Get-Help).parameters | gm

GetHelp_GetHelp_Parameters_GM.PNG
Well lookie here, another NoteProperty!

Let’s try that and see what we get, shall we?

(Get-Help Get-Help).parameters.parameter

GetHelp_GetHelp_Parameters
…the exact same >:( Fine, have the same screenshot then!

It’s always brightest before the dawn

And the answer comes always before you smash your screen in rage.

If we pipe the above information to Get-Member again, we get more useful information this time (I’m not going to show it, you know how to pipe to gm by now).

GetHelp_GetHelp_Parameters_GM_Useful
This looks like something we can work with 🙂

I’m from a database background so can we make this pretty, all I care about is the name and the pipeline input.

(Get-Help Get-Help).parameters.parameter |
    Select-Object -Property name,pipelineinput

GetHelp_NamePipelineInput.PNG
ByPropertyName…what’s that?

By Odin’s Beard! I mean PropertyName

You know one of these days I should really read this help file (you should too) because half way down the results of the following code is some interesting info…

help about_pipelines

METHODS OF ACCEPTING PIPELINE INPUT

Cmdlets parameters can accept pipeline input in one of two different ways:

— ByValue: Parameters that accept input “by value” can accept piped objects
that have the same .NET type as their parameter value or objects that can be
converted to that type.

For example, the Name parameter of Start-Service accepts pipeline input
by value. It can accept string objects or objects that can be converted to
strings.

— ByPropertyName: Parameters that accept input “by property name” can accept piped
objects only when a property of the object has the same name as the parameter.

For example, the Name parameter of Start-Service can accept objects that have
a Name property.

(To list the properties of an object, pipe it to Get-Member.)

Some parameters can accept objects by value or by property name. These parameters are
designed to take input from the pipeline easily.

So that’s the problem?! The names need to match up! I can do that with Select-Object!

All I need to do is add a custom label using @{Label='<custom label>';Expression={'<custom expression>'}}

Try{}Catch{}Finally{}

Get-Service -Name *sql* |
Select-Object -First 1 -Property @{l='Parameter';e={$_.Name}} |
Test-FunctionByParameter

ParameterName_TestFunction
I always do my maths right!

So now when I run a command and get the crazy…

The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or the input and its properties do not match any of the parameters that take pipeline input.

I can just run:

(Get-Help &lt;cmdlet name&gt;).parameters.parameter |
Select-Object Name,pipelineInput

And know exactly where to fix! 🙂

SQL Server Configuration Manager: Where has it gone?

Why this blog post?

Now this is a short one.

A while back I was testing attempting to access SQL Server using a defined port number.

To do that, you have to access SQL Server Configuration Manager to specify the port number, or at least I think you do (If you don’t have to do it this way, please, oh please, let me know!).

So, since my laptop is running on Windows10, I open up the Start menu, type in “config” and…nothing!
No SQL Server Configuration Manager! The closest I got was the configuration manager for Reporting Server.

I’m shocked by this especially because when I type it into the Start menu now, I get…

sqlserverconfigurationmanager
Windows 10 making a liar out of me…

Ignoring the fact that it shows up in the result pane now, I had to go to MSDN and figure out where it’s default path is.

But WHY this blog post?

Well it turns out that, for me, the default path is now in…

C:\Windows\sysWOW64\SQLServerManager”<nn>”.msc

I say “<nn>” because it’s a number dependant on what version of SQL Server that you are running.
For example, I have a 2012, a 2014 and a 2016 version on my laptop so I have 3 versions of SQL Server Configuration Manager.

sqlserverconfigurationmanager_we
I may have been msc-taken, get it? 🙂

Seriously, is that it?

Nope, that ain’t it.

Opening up Windows Explorer, going all the way down to that level just to get the config manager? Ain’t nobody got time for that!

They say “imitation is the greatest form of flattery” so taking ideas garnered from dbatools and the fact that I’m just finished a pre-con for SQL Saturday Boston in PowerShell from Mike Fal ( b | t ), thank you Andy Mallon ( b | t ) for pushing me out of my comfort zone even if it was only to just sign up!, I’ve created a little PowerShell script to let me choose and open a SQL Server Configuration Manager.

Check it out! And let me know what you think.

Script me!

Get-ChildItem -Path C:\Windows\sysWOW64\ -Filter &quot;*SQLServerManager*&quot; |
Out-GridView -PassThru |
Invoke-Item

 

scriptme
Seriously “OutGridView -PassThru” should get an award!

Now, I only have 2014 service up on this time so when Out-GridView pops up, I’m going to choose SQLServerManager12.msc and click “Ok”

out-gridview
This doesn’t count as a GUI!

Which will open up our “missing” configuration manager!

SQLServerConfigurationManagerEnd.PNG
I may have just been doing something stupid though…

Exit:

PowerShell! Helping make my life easier since…whenever I actually figure it out. 😐

Why I Powershell my Laptop off

Could be my shortest blog post so far…

Intro

Kalen Delaney ( blog | twitter ) has an excellent blog post about Windows Fast Startup and, while I’m not going to repeat what she has said here because, like I already mentioned, it’s an excellent post and I encourage you to read it ( and maybe give her a lil’ subscribe 😉 ), what I will mention is that I encountered this feature with my new laptop and had it interfering with my SQL Server testing (again read her post as to possible causes why).

Using Powershell for documenting Replication had me wondering if there was a way I could get around this using Powershell. So while this is another post that is not about SQL Server, it is about Powershell.

Hey, at least I’m consistent in my consistencies.

What’s the Problem?

A quick lmgtfu, brought me to the following page and command:

shutdown /s

Which pops open a window saying the computer will shutdown and, after a delay, that’s what it does.

At this stage I’ve read enough documentation to know that
shutdown /s
doesn’t follow the standard Verb-Noun convention of Powershell and that delay was slightly annoying.

Plus, everyone raves about the Get-Help commandlet so I figured I would try that.

Get-Help *shutdown*

Gave me a list of commands and one of them seemed to fit what I wanted.

Get-Help Stop-Computer;

Powershell_stopcomputer

Summary

3 things here.

  1. You now know how I turn my computer off all the time
  2. It’s amazing what you can do with Powershell, and
  3. Kalen says

    So you might already know, but I didn’t know, until I learned it, of course.

I didn’t know, but found a work-around so didn’t learn it.
I’d advise you to follow Kalen’s approach (as I’m going to try from now on) but, hey, at least you now know mine.

T-SQL Tuesday #80 – Can Powershell Get What T-SQL Cannot?

No matter who wins Powershell or T-SQL, the GUI loses!

It’s T-SQL Tuesday time! tsql2sday-150x150

Chris Yates (blog | twitter) has given the T-SQL bloggers a “carte blanche” with regard to this month’s theme so even though this T-SQL Tuesday falls on his birthday, he’s the one giving us a gift (awfully nice of him I think).

So a white blank page to work with…in this case it seems only appropriate to write about Powershell. Mainly because if I were to write about it normally, all you would be getting is a white blank page. Basically, about Powershell, I don’t know much…

Therefore to start off this blog post, a little back story about why I’m talking about Powershell is appropriate…

Documenting Replication.

If you really want to get up to scratch with something that you are working with then you can’t go wrong with documenting it. Or at least that’s what my Senior DBA told me just before he went back to his laptop laughing maniacally.

So needing a high level documentation of the publications, articles and article properties of what we replicate, I turned to the only thing I knew at the time; the GUI.

GUI.

Now, due to an unfortunate incident when I was a Software Support Engineer that involved a 3 week old backup and a production database, I prefer to not to use the GUI if I can help it.

I’m not joking about that as well, if there is ANY way that I can accomplish something with scripts instead of the GUI, I will take it!

Especially when the need was to document the properties of over 100 articles, I was particularly not looking forward to opening the article properties window for each of the articles and copying them out individually.

Replication_ArticleProperty
100 X 40 = 4000 no thanks

 

Scripts

Unfortunately, in this case, the scripts were only partially useful.

Oh they were great for the publications

EXEC sys.sp_helppublication;

and to get the articles

EXEC sys.sp_helparticle @publication = publication_name;

but the article properties themselves remain elusive!

From BOL, the only way to actually interact with them seemed to be when you were creating the articles or if you wanted to change them, yet nothing for just viewing the states of them.

Finally after a lot of Google-fu, I managed to get most of the schema options with a good few temp tables and Bitwise operators

Replication_PreCreationCommand_SchemaOptions

but nothing I could find helped me with the create commands.

Replication_PreCreationCommand

These create commands are kinda important when you think about what they do.

Replication_PreCreationCommand_Options

Drop the object, truncate all data and the delete data. The delete data option is probably most dangerous if you have a row filter set up as you may not even be aware that data has been deleted until it’s too late and users are screaming at your door!

So in a blind fit of panic and a desperate attempt to thwart my GUI foe, I turned to Powershell.

Powershell

I was thankfully able to find an elegant, well-explained script by Anthony Brown and then proceeded to butcher it without remorse until it returned what I wanted.

I’ve included the full script at the end of this post with a few…shall we say…forewarnings.

The main point that I had to add was simply this:

PseudoCode:

For whatever article on now,
get the article properties
where the source article is what we’re looking for
return only the PrecreationCommands
formatted in a list
and returned in a string:


$publicationobject.TransArticles `
| Where-Object SourceObjectName -Like $WorkOnNow `
| Select-Object PreCreationMethod `
| Format-List `
| Out-String

Finally returning what I want, which is a simple copy and paste into the relevant section of a Word document

Replication_PreCreationCommand_Final

Time taken:

  • Powershell: 100 + articles all finished in around 4 seconds 🙂
  • GUI: 100+ articles hand typed out in a time I’d not like to figure out, plus however long I spent washing my hands afterwards.

Final Word

As I’ve said before

one of the best thing about SQL Server is, that for all it’s restrictive syntax and rules, there is no 1 way to do anything.

…and there is no excuse for relying on the GUI, unless you want to!
Powershell is an amazing tool to add to your belt and one that I’m definitely going to learn more about.

I challenge you to think about an aspect of your work that is not automated or for which you use the GUI for (shudder).

Now see if there’s a way around it…

Final Powershell Script

The following is the final script used to get the code. I make no apologies for it as I don’t know Powershell yet it’s served it’s purpose and then some. It has returned my creation commands, taught me some fundamentals of the language and ignited a desire to learn it.

However I do apologise for the look of the script. There is something configured with the blog that squashes the script and requires a scroller, I’m working on fixing it.

# Load the assembly needed. (Only required once at the start).
[System.Reflection.Assembly]::LoadWithPartialName(&quot;Microsoft.SqlServer.Rmo&quot;)
# Clear screen before each run
Clear-Host;

# Connect to the server.
$servername = &quot;insert server here&quot;
$repserver = New-Object &quot;Microsoft.SqlServer.Replication.ReplicationServer&quot;
$srv = New-Object &quot;Microsoft.SqlServer.Management.Common.ServerConnection&quot; $servername
$srv.Connect()
$repserver.ConnectionContext = $srv

# Connect to the database
$databasename = &quot;insert database here&quot;
$repdb = $repserver.ReplicationDatabases[$databasename]

# Connect to the publication.
$publicationname = &quot;insert publication here&quot;
$publicationobject = $repdb.TransPublications[$publicationname]

&lt;#
# Everything (troubleshooting)
$publicationobject.TransArticles | Where-Object SourceObjectName -EQ $article
#&gt;

# Get everything. (from here on out, it's Butcher town :-( )
$Schoptions = ($publicationobject.TransArticles | Select-Object SourceObjectName, SchemaOption, PreCreationMethod )
$Schoptions `
| ForEach-Object `
{ `
$NewLine = &quot;`n&quot;
$WorkOnNow = $_.SourceObjectName

# Get SchemaOptions details.
$Schoptions = ($publicationobject.TransArticles | Where-Object SourceObjectName -Like $WorkOnNow | Select-Object SchemaOption | Format-List | Out-string )
$schemaoptions2 = (($Schoptions -split &quot;, &quot;).Trim() ) -csplit &quot;SchemaOption : &quot;
$OptFormatted = ($schemaoptions2 | Where-Object {$_ -ne &quot;&quot;} | Where-Object {$_ -ne &quot;PrimaryObject&quot;} `
| ForEach-Object -Process `
{
Switch ($_)
{
&quot;Identity&quot; {&quot;Identity columns are scripted using the IDENTITY property`t:`tTrue&quot;}
&quot;KeepTimestamp&quot; {&quot;Convert TIMESTAMP to BINARY`t:`tFalse&quot;}
&quot;ClusteredIndexes&quot; {&quot;Copy clustered index`t:`tTrue&quot;}
&quot;DriPrimaryKey&quot; {&quot;Copy primary key constraints`t:`tTrue&quot;}
&quot;Collation&quot; {&quot;Copy collation`t:`tTrue&quot;}
&quot;DriUniqueKeys&quot; {&quot;Copy unique key constraints`t:`tTrue&quot;}
&quot;MarkReplicatedCheckConstraintsAsNotForReplication&quot; {&quot;Copy check constraints`t:`tFalse&quot;}
&quot;MarkReplicatedForeignKeyConstraintsAsNotForReplication&quot; {&quot;Copy foreign key constraints`t:`tFalse&quot;}
&quot;Schema&quot; {&quot;Create schemas at Subscriber`t:`tTrue&quot;}
&quot;Permissions&quot; {&quot;Copy permissions `t : `t True&quot;}
&quot;CustomProcedures&quot; {&quot;Copy INSERT, UPDATE and DELETE stored procedures`t:`tTrue&quot;}
default {&quot;Extras present, please check&quot;}
}
})

# Get PreCreationMethod details.
$CreationMethod = ($publicationobject.TransArticles | Where-Object SourceObjectName -Like $WorkOnNow | Select-Object PreCreationMethod | Format-List | Out-String)
$CreationMethod2 = (($CreationMethod -split &quot;:&quot;).Trim() | Where-Object {$_ -ne &quot;&quot;} | Where-Object {$_ -ne &quot;PreCreationMethod&quot;} `
| ForEach-Object -Process `
{
Switch ($_)
{
&quot;None&quot; {&quot;Action if name is in use `t : `t Keep existing object unchanged&quot;}
&quot;delete&quot; {&quot;Action if name is in use `t : `t Delete data. If article has a row filter, delete only data that matches the filter&quot;}
&quot;drop&quot; {&quot;Action if name is in use `t : `t Drop existing object and create a new one&quot;}
&quot;truncate&quot; {&quot;Action if name is in use `t : `t Truncate all data in the existing object&quot;}
default {&quot;Error! Creation Method Switch has failed&quot;}
}
})

#Report the details.
$NewLine
$WorkOnNow
Write-Host '----------'
$OptFormatted
$CreationMethod2
$NewLine
}