PowerShell Equality Comparison Operators

Dave Mason - PowerShell Equality Comparison Operators

When comparing two values in a programming or scripting language, you are most likely going to use syntax consisting of characters you learned as a youngster in math. Those who are new to programming will almost certainly recognize them. Python, Java, C, C++, Javascript, C#, and R all use those same characters and syntax. They're on a list of the top programming languages of 2021 along with a few others. The comparison operators and their syntax are as follows:


Comparison Syntax
Equals ==
Not Equals !=
Greater Than >
Greater Than or Equal >=
Less Than <
Less Than or Equal <=

Against The Norm

When comparing two values in PowerShell, you'll have to march to the beat of a different drum. The syntax is drastically different:

Comparison Syntax
Equals -eq
Not Equals -ne
Greater Than -gt
Greater Than or Equal -ge
Less Than -lt
Less Than or Equal -le

I've quipped that people should stop creating programming languages out of spite. In the same vein, I can't help but wonder what the creators of PowerShell were thinking when they chose the syntax for equality comparison. Why, PowerShell? Why?


PowerShell Variables and Scope

Dave Mason PowerShell Variable Scope

Let's talk a little bit about PowerShell variables and how long they exist within the scopes they're defined. I've encountered some behavior that for me, was unexpected. It's made my development efforts unproductive--especially when it comes to debugging.

Here's some initial code. It declares a $Birthday variable, assigns a date to it, and outputs the value of the variable:

$Birthday = [System.DateTime]::Today
Write-Host $Birthday.ToString() -ForegroundColor Yellow 
PS C:\Users\Dave> C:\My Scripts\PowerShell\Variable Test.01.ps1
11/28/2021 12:00:00 AM 

So far, so good. But I don't like the variable name $Birthday. Let's refactor the code, change the variable name to $Birthdate, and re-run the script:

$Birthdate = [System.DateTime]::MinValue
Write-Host $Birthday.ToString() -ForegroundColor Yellow 
PS C:\Users\Dave> C:\My Scripts\PowerShell\Variable Test.02.ps1
11/28/2021 12:00:00 AM 

You may have noticed a couple of things. I changed the date value of the variable to the minimum value of a DateTime (Jan 1st, 0001). However, when I output the value, it still shows today's date. What happened? Well, I got sloppy with my refactoring. I did not rename the second occurrence of the variable.

In the current state of the script, $Birthday is neither declared nor assigned a value. There was no exception, and the variable returned a non-null value. Is that what you were expecting? We'll come back to that. For now, let's fix our mistake and re-run the script:

$Birthdate = [System.DateTime]::MinValue
Write-Host $Birthdate.ToString() -ForegroundColor Yellow
PS C:\Users\Dave> C:\My Scripts\PowerShell\Variable Test.02.ps1
1/1/0001 12:00:00 AM 

There, that looks correct. The correct date is output and the $Birthday variable is completely gone from the script. Now, about that variable...is it still in scope and available in the console? In a word, yes. You can see evidence of this from Intellisense in your development tool. Here's what you'd see in the PowerShell ISE:

Dave Mason PowerShell Variable Scope

What's Hiding In The Console?

I don't know of an ideal way to programatically list out just the user-defined variables that are in scope for the current session and available in the console. There is a Get-Variable cmdlet that helps some. It's got a handfull of parameters that can narrow down the list of returned variables. But it includes variables that are defined as constants and variables owned by the system. Here is the default output (truncated for readability):

PS C:\Users\Dave> Get-Variable

Name                           Value 
----                           ----- 
$                              MinValue
?                              True
^                              [
args                           {}
Birthdate                      1/1/0001 12:00:00 AM
Birthday                       11/28/2021 12:00:00 AM
ConfirmPreference              High
ConsoleFileName
DebugPreference                SilentlyContinue
Error                          {}
...

You can see both the $Birthdate and $Birthday variables are in scope. To remove $Birthday from scope, there is a Remove-Variable cmdlet that works well for named variables. This line of code will do the deed:

Remove-Variable -Name Birthday 

Running Get-Variable again verifies that $Birthday is not in scope and no longer exists.

If you are looking for consistent, repeatable script results (especially during the development process), the code examples above might be a little disconcerting. It might make sense to remove all user-defined variables as one of the first steps of a script. Much like its Get-Variable counterpart, I'm not seeing a way to specify just user-defined variables for the Remove-Variable cmdlet. Using a wildcard character, Remove-Variable * will attempt to remove everything. However, it returns a slew of errors, telling you read-only and system-owned variables can't be removed. You can suppress the errors to get the desired effect via Remove-Variable * -ErrorAction SilentlyContinue. It's effective, if not heavy handed.


Variables Not In Scope

By default, if you attempt to reference a variable that is not in scope, PowerShell will return a value of 0 (zero) or $Null, depending on type. Here is an example:

Write-Host "Hello"
Write-Host $UndelcaredVariable -ForegroundColor Yellow
Write-Host "World" 
PS C:\Users\Dave> C:\My Scripts\PowerShell\Variable Test.03.ps1
Hello

World 

PowerShell offers a Set-StrictMode cmdlet that can help. Strict mode is "off" by default, so let's set turn it on and try the previous example again:

Set-StrictMode -Version Latest
Write-Host "Hello"
Write-Host $UndelcaredVariable -ForegroundColor Yellow
Write-Host "World" 
PS C:\Users\Dave> C:\My Scripts\PowerShell\Variable Test.03.ps1
Hello
The variable '$UndelcaredVariable' cannot be retrieved because it has not been set.
At C:\My Scripts\PowerShell\Variable Test.03.ps1:3 char:12
+ Write-Host $UndelcaredVariable -ForegroundColor Yellow
+            ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (UndelcaredVariable:String) [], RuntimeException
    + FullyQualifiedErrorId : VariableIsUndefined
 
World 

This behavior is more sensible, don't you think? If you do intend to use it, keep in mind Set-StrictMode won't save you in every scenario. A variable can be loaded into the console and in scope during your development session. If you rename/refactor the variable and accidentally reference the "old" variable name, you'll get no error message from PowerShell. So be aware that removing variable references from your script doesn't remove it from scope.


PowerShell Functions and Return Types

Dave Mason PowerShell Functions

As a result of some struggles trying to automate a process, I've learned some things about PowerShell. After getting to the bottom of a time-consuming problem, I thought it worth a blog post that might save someone else some time and heartache.

Let's begin with this simple function named Get-RandomDate. It generates and returns a random date that is between today and X days ago. It has an input parameter $DaysAgo, which is of type [System.Int32]--it is a mandatory parameter.

function Get-RandomDate()
{
    Param (
        [parameter(Mandatory=$true)]
        [System.Int32]
        $DaysAgo
    )
    [System.DateTime]$ret = [System.DateTime]::Today
    $randomDays = Get-Random -Minimum 0 -Maximum $DaysAgo
    $ret = [System.DateTime]::Today.AddDays($randomDays * -1)
    
    return $ret
}

$randomDate1 = Get-RandomDate(365)
Write-Host $randomDate1.ToString() -ForegroundColor Yellow 

Here is our output when we run the script:

PS C:\My Scripts\PowerShell\SQL Server> C:\My Scripts\PowerShell\Function Test.01.ps1
12/8/2020 12:00:00 AM 

The $RandomDate variable that is assigned the return value of the function is not typed. But we can make it type-safe, if desired. This produces the same result as above:

[System.DateTime]$randomDate1 = Get-RandomDate(365)

Now we'll make things go sideways. Add a line for the "Hello world!" string and another for the integer 42, which despite what you've heard, is not an answer for anything in this exercise:

function Get-RandomDate()
{
    Param (
        [parameter(Mandatory=$true)]
        [System.Int32]
        $DaysAgo
    )
    [System.DateTime]$ret = [System.DateTime]::Today
    $randomDays = Get-Random -Minimum 0 -Maximum $DaysAgo
    $ret = [System.DateTime]::Today.AddDays($randomDays * -1)

    "Hello world!"
    42
    
    return $ret
}

$randomDate3 = Get-RandomDate(365)

Write-Host $randomDate3.ToString() -ForegroundColor Yellow 

PS C:\My Scripts\PowerShell\SQL Server> C:\My Scripts\PowerShell\Function Test.03.ps1
System.Object[] 

What happened? When we tried to write out the value of our date variable, we got System.Object[]. Is our function no longer returning a System.DateTime type? Maybe we need to specify the output type of the function? Let's try that...

function Get-RandomDate()
{
    [OutputType([System.DateTime])]
    Param (
        [parameter(Mandatory=$true)]
        [System.Int32]
        $DaysAgo
    )
    [System.DateTime]$ret = [System.DateTime]::Today
    $randomDays = Get-Random -Minimum 0 -Maximum $DaysAgo
    $ret = [System.DateTime]::Today.AddDays($randomDays * -1)

    "Hello world!"
    42
    
    return $ret
}

$randomDate3 = Get-RandomDate(365)

Write-Host $randomDate3.ToString() -ForegroundColor Yellow 

PS C:\My Scripts\PowerShell\SQL Server> C:\My Scripts\PowerShell\Function Test.03.ps1
System.Object[] 

Well that didn't work. What if we make the variable type safe? Does that get us back on track?

function Get-RandomDate()
{
    [OutputType([System.DateTime])]
    Param (
        [parameter(Mandatory=$true)]
        [System.Int32]
        $DaysAgo
    )
    [System.DateTime]$ret = [System.DateTime]::Today
    $randomDays = Get-Random -Minimum 0 -Maximum $DaysAgo
    $ret = [System.DateTime]::Today.AddDays($randomDays * -1)

    "Hello world!"
    42
    
    return $ret
}

[System.DateTime]$randomDate4 = Get-RandomDate(365) 

PS C:\My Scripts\PowerShell\SQL Server> C:\My Scripts\PowerShell\Function Test.04.ps1
Cannot convert the "System.Object[]" value of type "System.Object[]" to type "System.DateTime".
At C:\My Scripts\PowerShell\Function Test.04.ps1:19 char:1
+ [System.DateTime]$randomDate4 = Get-RandomDate(365)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : MetadataError: (:) [], ArgumentTransformationMetadataException
    + FullyQualifiedErrorId : RuntimeException 

Nope, that didn't work either. Let's go back to the example that seemingly produced an output of System.Object[]. Here it is again, for reference (with two extra lines of code):

function Get-RandomDate()
{
    [OutputType([System.DateTime])]
    Param (
        [parameter(Mandatory=$true)]
        [System.Int32]
        $DaysAgo
    )
    [System.DateTime]$ret = [System.DateTime]::Today
    $randomDays = Get-Random -Minimum 0 -Maximum $DaysAgo
    $ret = [System.DateTime]::Today.AddDays($randomDays * -1)

    "Hello world!"
    42
    
    return $ret
}

$randomDate3 = Get-RandomDate(365)
Write-Host $randomDate3.ToString() -ForegroundColor Yellow
$randomDate3.GetType()
$randomDate3.Count 

PS C:\My Scripts\PowerShell\SQL Server> C:\My Scripts\PowerShell\Function Test.ps1
System.Object[]

IsPublic IsSerial Name                                     BaseType                                                                                                      
-------- -------- ----                                     --------                                                                                                      
True     True     Object[]                                 System.Array                                                                                                  
3 

$randomDate3.GetType() tells us $randomDate3 is indeed an array of objects. $randomDate3.Count also tells us there are three items in the array. Can you guess what's in there? Let's find out with this bit of code:

function Get-RandomDate()
{
    [OutputType([System.DateTime])]
    Param (
        [parameter(Mandatory=$true)]
        [System.Int32]
        $DaysAgo
    )
    [System.DateTime]$ret = [System.DateTime]::Today
    $randomDays = Get-Random -Minimum 0 -Maximum $DaysAgo
    $ret = [System.DateTime]::Today.AddDays($randomDays * -1)

    "Hello world!"
    42
    
    return $ret
}

$randomDate5 = Get-RandomDate(365)

Write-Host (“return value index 0:  Type = {0}, Value = {1}” –f $randomDate5[0].GetType(), $randomDate5[0]) -ForegroundColor Yellow
Write-Host (“return value index 1:  Type = {0}, Value = {1}” –f $randomDate5[1].GetType(), $randomDate5[1]) -ForegroundColor Yellow
Write-Host (“return value index 2:  Type = {0}, Value = {1}” –f $randomDate5[2].GetType(), $randomDate5[2]) -ForegroundColor Yellow 

PS C:\My Scripts\PowerShell\SQL Server> C:\My Scripts\PowerShell\Function Test.ps1
return value index 0:  Type = System.String, Value = Hello world!
return value index 1:  Type = System.Int32, Value = 42
return value index 2:  Type = System.DateTime, Value = 11/4/2021 12:00:00 AM 

Well lo and behold, the first two items in the array are the "Hello world!" string and integer value 42 respectively. And the last item is the DateTime "return" value. Everything from the output stream of the function gets piled onto an array of objects and sent back to whatever called it.


Hello world?

Now the obvious question: why would you add "Hello world!" (or 42) as a line of code? Well, you wouldn't. At least I wouldn't. But it demonstrates the problem nicely: any output not captured needs to be accounted for.

It's not just senseless string literals or vagabound integers you need to worry about. Consider some .NET Framework methods that have a return type, but are called as if the return type was void. System.Text.StringBuilder.Append() and System.Data.SqlClient.SqlParameter.AddWithValue() are a couple of good examples. Let's take a look at the StringBuilder:

function Get-RandomDate()
{
    [OutputType([System.DateTime])]
    Param (
        [parameter(Mandatory=$true)]
        [System.Int32]
        $DaysAgo
    )
    [System.DateTime]$ret = [System.DateTime]::Today
    $randomDays = Get-Random -Minimum 0 -Maximum $DaysAgo
    $ret = [System.DateTime]::Today.AddDays($randomDays * -1)

    [System.Text.StringBuilder] $sb = New-Object System.Text.Stringbuilder
    $sb.Append("Hello ")
    $sb.Append("world!")
    
    return $ret
}

$randomDate5 = Get-RandomDate(365)

Write-Host $randomDate5.ToString() -ForegroundColor Yellow
Write-Host (“return value index 0:  Type = {0}, Value = {1}” –f $randomDate5[0].GetType(), $randomDate5[0]) -ForegroundColor Yellow
Write-Host (“return value index 1:  Type = {0}, Value = {1}” –f $randomDate5[1].GetType(), $randomDate5[1]) -ForegroundColor Yellow
Write-Host (“return value index 2:  Type = {0}, Value = {1}” –f $randomDate5[2].GetType(), $randomDate5[2]) -ForegroundColor Yellow 

PS C:\My Scripts\PowerShell\SQL Server> C:\My Scripts\PowerShell\Function Test.ps1
System.Object[]
return value index 0:  Type = System.Text.StringBuilder, Value = Hello world!
return value index 1:  Type = System.Text.StringBuilder, Value = Hello world!
return value index 2:  Type = System.DateTime, Value = 8/8/2021 12:00:00 AM 

Our much more realistic scenario still has the same problem as the contrived examples. How do we address this issue? One option is to cast the expression as [void]:

[System.Text.StringBuilder] $sb = New-Object System.Text.Stringbuilder
[void] $sb.Append("Hello ")
[void] $sb.Append("world!") 

This approach is not only counterintuitive to me, it feels profoundly wrong. It's a bad attempt at a jedi mind trick.

.NET Framework: Oh, I see you're using the Append() method with a StringBuilder. Nice choice! Its return value is a StringBuilder.
PowerShell: No, it isn't.
.NET Framework: Maybe it isn't. I'm gonna beat up the fool that told me those lies!

Another option is to hide the output via Out-Null, instead of sending it down the pipeline or displaying it:

[System.Text.StringBuilder] $sb = New-Object System.Text.Stringbuilder
$sb.Append("Hello ") | Out-Null
$sb.Append("world!") | Out-Null 

This is considerably more intuitive and palatable. But you'd need to know the lines where it's necessary. I don't see any indication from the PowerShell ISE or Visual Studio Code where output isn't being captured. What are we supposed to do? Append | Out-Null to every line of code in the function?

As you can probably tell, I find all of this frustrating. Defining a function's type is pointless if functions aren't type-safe. PowerShell's exceptions are red herrings. And "return" doesn't mean what I think it should mean.

At least I've gained a better understanding, which may make my future PowerShell development less unproductive. Hopefully some of this helps you too.

Dave | Out-Null


A Faster PIVOT for SQL Server?

Dave Mason - SQL Server - PIVOT - OPENJSON

While working with some poorly performing code in T-SQL that used a PIVOT operator, I wondered if there was a more efficient way to get a result set of pivoted data. It may have been a fool's errand, but I still wanted to try. It dawned on me that I could use the STRING_AGG() function to build a delimited list of pivoted column names and values. From there, I'd have to "shred" the delimited data to rows and columns. But how?

SQL Server's longtime support for XML came to mind. But I quickly dismissed it--it's not my cup of tea and I've never thought of SQL Server combined with XML as a well-performing combination. What about using JSON? Could that work? And if so, would it perform well?

Below is the design pattern I settled on. There is a @Json variable that (as the name implies) holds all of the data from the result set, formatted as JSON. It gets passed to the OPENJSON() function, which returns a result set of typed columns based on what is specified in the WITH clause. The columns we *don't* want to pivot should be known at design time when the query is written--they're represented in green. The columns and data we do want to pivot may or may not be known ahead of time--they're represented in yellow. In my experience, they usually aren't known, and dynamic T-SQL must be used. I'll assume that's the case and use dynamic T-SQL for an example.


The Design Pattern


DECLARE @Json VARCHAR(MAX) = 
'[
{ "ColName_1":"Val_1","ColName_2":"Val_2", ... "ColName_N":"Val_N", "PivColName_1":"PVal_1","PivColName_2":"PVal_2", ... "PivColName_N":"PVal_N"},
{ "ColName_1":"Val_1","ColName_2":"Val_2", ... "ColName_N":"Val_N", "PivColName_1":"PVal_1","PivColName_2":"PVal_2", ... "PivColName_N":"PVal_N"},
{ "ColName_1":"Val_1","ColName_2":"Val_2", ... "ColName_N":"Val_N", "PivColName_1":"PVal_1","PivColName_2":"PVal_2", ... "PivColName_N":"PVal_N"},

{ "ColName_1":"Val_1","ColName_2":"Val_2", ... "ColName_N":"Val_N", "PivColName_1":"PVal_1","PivColName_2":"PVal_2", ... "PivColName_N":"PVal_N"}
]';

SELECT *
FROM OPENJSON(@Json)
WITH (
	ColName_1 <datatype>, ColName_2 <datatype>, ... ColName_N <datatype>, PivColName_1 <datatype>, PivColName_2 <datatype>, ... PivColName_N <datatype>
)


Sample Code

Here are two queries you can try for yourself with the [WideWorldImporters] database.

USE WideWorldImporters;

DECLARE @SQL AS VARCHAR(MAX);
DECLARE @WithCols VARCHAR(MAX);
DECLARE @Json VARCHAR(MAX);

;WITH ColNames AS
(
	SELECT DISTINCT AccountsPersonID
	FROM Sales.Invoices
)
SELECT 
	--Pivoted column names that will be specified in the OPENJSON WITH clause.
	@WithCols = '[InvoiceID] INT ''$.InvoiceID'','  + 
		STRING_AGG('[' + CAST(AccountsPersonID AS VARCHAR(MAX)) + '] INT ''$."' + CAST(AccountsPersonID AS VARCHAR(MAX)) + '"''', ', ') 
		WITHIN GROUP ( ORDER BY AccountsPersonID )
FROM ColNames;

SELECT @Json = '[' + STRING_AGG(CAST(t.JsonData AS VARCHAR(MAX)), ',') + ']'
FROM
(
	SELECT 
		N'{' + ' "InvoiceID":' + CAST(s.InvoiceID AS VARCHAR(MAX)) + 
			COALESCE(',' + STRING_AGG('"' + CAST(s.AccountsPersonID AS VARCHAR(MAX)) + '":' + CAST(s.Quantity AS VARCHAR(MAX)), ','), '') + 
		'}' 
		AS JsonData
	--Derived table with aggregated data.
	FROM (
		SELECT i.InvoiceID, i.AccountsPersonID, SUM(l.Quantity) AS Quantity
		FROM Sales.Invoices i
		JOIN Sales.InvoiceLines l
			ON l.InvoiceID = i.InvoiceID
		GROUP BY i.InvoiceID, AccountsPersonID
	) AS s
	GROUP BY s.InvoiceID
) t

--Parse the JSON data and return a result set.
SET @SQL = '
SELECT *
FROM OPENJSON(''' + @Json + ''')
	WITH
	(
		' + @WithCols + '
	)
';
EXEC(@SQL);
GO
USE WideWorldImporters;
DECLARE @Cols VARCHAR(MAX);

--Pivoted column names that will be specified in the PIVOT operator.
;WITH ColNames AS
(
	SELECT DISTINCT AccountsPersonID
	FROM Sales.Invoices
)
SELECT @Cols = STRING_AGG(QUOTENAME(AccountsPersonID), ',') WITHIN GROUP (ORDER BY AccountsPersonID)
FROM ColNames;

DECLARE @SQL VARCHAR(MAX) = '
SELECT *
FROM   
(
	SELECT l.InvoiceID, i.AccountsPersonID, l.Quantity
	FROM Sales.Invoices i
	JOIN Sales.InvoiceLines l
		ON l.InvoiceID = i.InvoiceID
) p  
PIVOT  
(  
	SUM(Quantity)  
	FOR AccountsPersonID IN  
	( ' + @Cols + '
	)  
) AS pvt';

EXEC(@SQL);
GO

Results and Comparison

Both query execution plans include a nonclustered columnstore index scan on [Sales].[InvoiceLines], and a nonclustered row index scan on [Sales].[Invoices]. And they each have the same number of reads. If I return the results to the SSMS grid, PIVOT takes longer--both in terms of duration and CPU.

Dave Mason - OPENJSON - STRING_AGG

Perhaps a better comparison is to discard grid results after execution. With that setting specified in SSMS options, the execution plans remain the same. But duration and CPU are down for both plans. Here again, the plan using PIVOT takes longer.

Dave Mason - OPENJSON - STRING_AGG

Conclusions

So did I beat PIVOT? Definitely maybe. At least for this one contrived example. I do have another example that is running in a test environment with one of my clients, and it beats PIVOT too. It has a result set of about 200 columns and 44 thousand rows. I'm not ready to declare victory, but it is intriguing. I'd like to test many, many more queries. The slight gains demonstrated here (even if consistently reproducible) are arguably not worth the hassle. I'll let you be the judge.


Analytic Function FIRST_VALUE

Dave Mason SQL Server FIRST_VALUE

Last week, I found myself with a T-SQL problem. A customer has a database where multiple "widget" records might exist in a table. End users might enter the duplicate rows, or they might be inserted by other automated processes. My task was to keep the most recent widget record. But, if there were any NULL values, look in the previous "duplicate" rows and use the most recent missing value for each column.

Here's a contrived data example. I needed to take the following result set...

Row_ID Tomash_ID Hoppler_ID Atrovel_ID Mogotrevo_ID Buwheal_ID Izzillent_ID
1 NULL 2 3 14 55 16
2 21 22 3 4 5 NULL
3 12 NULL 3 44 NULL 6
4 11 22 NULL 4 NULL 66
5 11 2 3 14 NULL NULL
6 1 2 3 NULL NULL NULL

...and reduce it to this:

Row_ID Tomash_ID Hoppler_ID Atrovel_ID Mogotrevo_ID Buwheal_ID Izzillent_ID
6 1 2 3 14 5 66

There was an existing stored procedure that resolved the duplicates. But it had quite a bit of procedural code. There were separate queries that looked up the most recent value for each column, followed by an update to the "good" row. I was looking for a set-based approach. I'd made a few various attempts. Some were close, others were ridiculous, all were unsuccessful. Then I stumbled upon the FIRST_VALUE analytic function for T-SQL.


FIRST_VALUE

As its name indicates, FIRST_VALUE returns the first value in an ordered set of values. It requires an OVER clause along with an ORDER BY clause. There are optional PARTITION BY and ROWS/RANGE clauses -- check out the documentation for all the details.

Below is the query I used to get the desired result (along with some sample data so you can try yourself). The CASE expression is used to avoid returning NULL values--it orders them last. (There are IGNORE NULLS | RESPECT NULLS parameters that would seem to work well here, but the documentation says those only apply to Azure SQL Edge.)


SELECT TOP(1)
	Row_ID,
	FIRST_VALUE(Tomash_ID) OVER (ORDER BY CASE WHEN Tomash_ID IS NULL THEN 1 ELSE 0 END, Row_ID DESC) AS Tomash_ID_MostRecent,
	FIRST_VALUE(Hoppler_ID) OVER (ORDER BY CASE WHEN Hoppler_ID IS NULL THEN 1 ELSE 0 END, Row_ID DESC) AS Hoppler_ID_MostRecent,
	FIRST_VALUE(Atrovel_ID) OVER (ORDER BY CASE WHEN Atrovel_ID IS NULL THEN 1 ELSE 0 END, Row_ID DESC) AS Atrovel_ID_MostRecent,
	FIRST_VALUE(Mogotrevo_ID) OVER (ORDER BY CASE WHEN Mogotrevo_ID IS NULL THEN 1 ELSE 0 END, Row_ID DESC) AS Mogotrevo_ID_MostRecent,
	FIRST_VALUE(Buwheal_ID) OVER (ORDER BY CASE WHEN Buwheal_ID IS NULL THEN 1 ELSE 0 END, Row_ID DESC) AS Buwheal_ID_MostRecent,
	FIRST_VALUE(Izzillent_ID) OVER (ORDER BY CASE WHEN Izzillent_ID IS NULL THEN 1 ELSE 0 END, Row_ID DESC) AS Izzillent_ID_MostRecent
FROM #Test
ORDER BY Row_ID DESC
DROP TABLE IF EXISTS #Test;

CREATE TABLE #Test (
	Row_ID INT IDENTITY NOT NULL,
	Tomash_ID INT, 
	Hoppler_ID INT, 
	Atrovel_ID INT, 
	Mogotrevo_ID INT, 
	Buwheal_ID INT, 
	Izzillent_ID INT
);

INSERT INTO #Test VALUES(NULL,2,3,14,55,16);
INSERT INTO #Test VALUES(21,22,3,4,5,NULL);
INSERT INTO #Test VALUES(12,NULL,3,44,NULL,6);
INSERT INTO #Test VALUES(11,22,NULL,4,NULL,66);
INSERT INTO #Test VALUES(11,2,3,14,NULL,NULL);
INSERT INTO #Test VALUES(1,2,3,NULL,NULL,NULL);

The presence of a FIRST_VALUE function might lead you to believe there is a LAST_VALUE function. And there is! It returns the last value in an ordered set of values, but is otherwise the same in syntax and usage as FIRST_VALUE is. Happy coding, everyone.


NBA Games Are Harder Than Ever To Watch

The 2020-2021 NBA season hasn't been as fulfilling as I had hoped. It's not just because my beloved Boston Celtics aren't doing as well as hoped. It's because of the in-game camera work. I noticed things started getting weird last season during the playoffs. And it's gotten weirder and weirder since then.


Traditional Camera Angle

Here's an example of the classic camera angle that has always worked well. You can generally see all five players on each team, frequently from head to toe. The entirety of the half-court is visible. Even Grant Williams, waiting patiently in the lower left for an open "corner three" is fully visible. This works. It is perfection.


Low Camera Angles

I think ABC and/or ESPN is to blame for this. With the low camera angle that seems to be en vogue, there is still some elevation--the camera isn't ground-level. But it's significantly lower than the camera height of the traditional angle. Players that are far apart can seem bunched up. Three players for the Wizards (labeled 1, 2, 3) look like a pile of bodies. Note that two of them are on opposite sides of the lane, which is 16 feet wide. Aaron Nesmith for the Celtics (labeled 4) might be mistaken for a bench player standing up from the bench.

NBA Camera Angles - Too Low

Here's another example. Again we see multiple players standing significantly apart (players 2 and 3 for Washington). One of them is badly obscurred by the other. Making matters worse, the fifth Celtic can't even be seen. What you can't tell from this camera angle is that Carsen Edwards is standing behind the three-point line near the baseline. This looks like five on four. It is inexscusable.

NBA Camera Angles - Too Low

The low camera angle stinks in the half court. And it stinks when teams are in transition too. Here you can only see two players from each team.

NBA Camera Angles - Too Low

Other Strange Angles

It's not just the low camera angle. There are other questionable choices with the camera work. Here's what was shown when Grant Williams inbounded the ball from the sideline from the front court. You can only see part of the court, several players are off camera, and you can't see the basket at all.

NBA Camera Angles - Inbound

This is essentially the same thing, but with a birds-eye view. You still can't see all of the players or the basket.

NBA Camera Angles - Inbound

Who is Semi Ojeleye inbounding the ball to?

NBA Camera Angles - Inbound

Here we get an odd camera angle for a jump ball. That's 7' 6" Tacko Fall (one of the 40 tallest people in the world) for Boston and 6' 6" Troy Brown Jr. for Washington. They look the same size.

NBA Camera Angles - Jump Ball

This is another bad angle. Pop-quiz: who is shooting this free throw?

NBA Camera Angles - Free Throw

There's an abundance of bad camera angles for free throws. I counted around ten in the game where these images are from. It's too much.


Closing Thoughts

If I had the ear of those responsible, I'd want them to know they're trying too hard. I'd ask them to take to heart the old addage KISS: keep it simple, stupid. Stick with the traditional camera angles.


T-SQL Tuesday #135: Outstanding Tools

Dave Mason T-SQL Tuesday

For T-SQL Tuesday #135, Mikey Bronowski (b|t) asks us about the tools we use. What are the most helpful and effective tools you use or know of? Great questions, Mikey! I've got a few in mind for sure. And although I usually blog about SQL Server or something data-related, these are more generic minded tools...at least for us Windows folks.


Notepad

Yep, Notepad. I use it for all sorts of things. Whether it's code files, configuration/settings files, files for third-party apps, or just plain old text/log files, Notepad is my go-to plain text viewer and editor. Want to level up your Notepad skills? Create a shortcut to the Notepad.exe file and drop it in the "SendTo" folder. Now you can open any file with Notepad easily from Windows Explorer. Neat!


Paint.NET

I'm a proponent of "Show me, don't tell me". Screen captures go a long way toward that. Sure, Windows has Paint, but Paint.NET is a bit more advanced. I almost always have an instance of it open in the background. Hit the Print Screen keyboard button and paste (CTRL + V) it into Paint.NET (you can also use the ALT + Print Screen key combination to screen capture just the active window). From there you can do anything: trim down to just a specific part of the image, add some red arrows or circles for emphasis, blur/obscur any sensitive data that's in the image, etc. I take tweaked screen shots and paste them into just about anything...Word documents, email, even Twitter.


Spacesniffer

Running low on disk space and having a hard time figuring out where all your free storage went? There are numerous utilities that can help out. My favorite is SpaceSniffer. There's a zipped download that doesn't require a typical setup.exe installation. This makes it very convenient to copy to other machines you may be repsonsible for.


KeePass

If you're not using a password manager, I strongly suggest it. There are many good ones out there with different features and pros & cons. I've settled on KeePass. But if it's not KeePass, please use something!


7-Zip

Windows' built-in zip/compression functionality does what I need most of the time. If I need to password protect a zipped file, though, 7-Zip is the tool I use. It also has many additional features above and beyond what Windows provides (archive format, compression level, split to volumes, etc.).


Remote Desktop Connection Manager

If you find yourself connecting to a large number of virtual machines, managing those connections can be a chore. Before using Remote Desktop Connection Manager (aka "RDCMan"), I used to open the built-in Windows Remote Desktop Connection tool, hope the computer I wanted to connect to was in the drop-down list of computers, and manually enter my credentials. I got fancy and started using a DOS batch file for each computer I connected to regularly. But I still had to manually enter credentials. Enter Remote Desktop Connection Manager: it lets you create and organize groups of remote desktop connections. It handles credentials and a few other things too. Unfortunately, a vulnerability in the tool was discovered and Microsoft decided to retire the tool. If you have a different one to suggest, please drop me a note in the comments!


Headphones

The pandemic has resulted in many of us working from home. I'm sure we've all struggled during online meetings with poor audio and feedback that makes it hard for everyone to hear each other. Headphones are immensely better than using the built-in speakers and microphone of a laptop or desktop computer. This headset that I've been using for a few years is a Logitech model. It was cheap too--only about $30 (US). The microphone arm can be adjusted in different positions, and you can even flip it up and entirely out of the way if you just want to use the headset to listen to music. There's a physical mute button that is very handy, along with volume controls. It's USB too--I didn't have to install anything on Windows. Audiophiles may turn their noses up at this model. You can spend more (a lot more!) on a better microphone/headset setup that sounds ostensibly better. But compared to everyone else without a headset, you'll be playing chess while they're playing checkers.