No Column Name

T-SQL Tuesday 204 – Are Blogs Still Relevant

Words: ~ 880 words
Time to read: ~ 4 minutes

Welcome!

Welcome to T-SQL Tuesday, the monthly blogging party created by Adam Machanic, hosted by Steve Jones, now hosted by Jeff Iannucci, stewarded by Mike Walsh, and supported by StraightPath Solutions.¹

T-SQL Tuesday is where somebody creates a topic and invites others to write a blog post about it. This month, we have Jeff Iannucci asking, “Are Blogs Still Relevant?“²

What is Relevant?

I’m not trying to be philosophical – I ain’t smart enough for that. However, I don’t think you can answer a question about “being relevant” if you don’t know what you’re relevant to.

Relevancy

Money³

I’m vaguely aware that people can make money off of blogging; I think that’s how Tim Ferris started, but you’d need thousands, if not tens of thousands, if not millions of readers for that. I have subscribers, sure, but I’m pretty sure that my mother is one of them, and even she is too tempted by the mark-as-read button when a new post comes out.

If you’re lucky, though, you’ll get some spam emails about people loving your posts on “<include_latest_minus_1_blog_from_potential_victim_here>, and how much they’d appreciate writing a guest post on your site.

No, mate. Just no.

Fame

Sure, to a degree. I don’t think the vast majority of people are interested in database stuff. Don’t get me wrong, they should be! It’s fascinating! They’re not, though.

I don’t remember which DBA said it, but to paraphrase: only another DBA will know what it truly means when you say “I refactored a view to remove it calling multiple nested views and scalar functions in its WHERE clause”. Some people may infer that it’s a good thing; some people may know that it’s going to be faster; but only another DBA will give you that little groan of “who thought putting them in would be a good thing in the first place?!”

So, yeah, fame-ish-ness is available within the community if you continuously blog, have expertise, charm, and a way with words, but only on a level of “oh! That’s Person X. They blogged about Columnstore Indexes, they may know”, not a level of “Squueeeeee!!! It’s Person X! Sign my 1,000-page DB book” while you flake it at their head.

Respect⁴

Nah, mate.

The joys of an anonymous internet is that you are free to express yourself however you want. The horrors of an anonymous internet – not those horrors; tamer horrors – is that there will be constant armchair-warriors, and Captain Hindsights who will critique your work viciously.

Formatting? Hideous! Style? Garbage! Points? Banal? Vocabulary? Too posh – who says banal anyway? What is he trying to prove?

Etc.

There’s probably more, but this is more than I’ve written for a blog post in quite a while.

I don’t know if blogging is still relevant. I’m still going to do it. Slowly. Incrementally. Fighting imposter syndrome, burnout syndrome, laziness syndrome, and all other sins and/or dromes with every keystroke and backspace.

Here’s my answer:

Yes, blogging is still relevant, even if it doesn’t seem worth it to you to write them right now.

Why?

Cause while you or I won’t get Fame, Money, or Respect from blogging, it gives

Recognition:
- I know who’s blogging, so I know who’s still into the community. I know who’s using AI sensibly to expand their knowledge, or help build test cases, and who’s letting it write for them⁵, or worse, think for them.
Awareness:
- I know what people are blogging about⁶. I know what the latest bugs are. I know when the latest versions of tools are out. I know what new DB features are out, and I know when people are experimenting with new technology.
Technical Currency:
- I know what problems people have faced. I know what they’ve encountered, and, if I’m lucky enough that they’ve added the code or test cases, I even have something I can review and run to hand when I hit that scenario.

Thanks for the post, Jeff.

“I apologise for such a long post – I didn’t have time to write a short one” – Mark Twain, DBA

Interesting to see what is needed when people retire or step back. Will the new generation take over this blogging party? Did I just instinctively say I wasn’t the new generation? AM I OLD?!?! ↩︎
I haven’t seen a stronger case against Betteridge’s Law of Headlines in a while! ↩︎
Wait…you can make money off this thing??? Wait…you can not lose money on this thing!?!?! ↩︎
I once had a lovely thing happen where someone put a PowerShell blog post of mine up on Reddit. Hahahaha… ah! That was a lesson in humility. I received a lovely DM giving off to me for not explaining that what I had blogged about was already available in Microsoft Docs! ↩︎
Yes, you! You absolute “SQL Server does X. That’s the problem”, “The last one surprises people”, and “Here’s the part nobody tells you about” waste of….! deep breaths, Shane. Deep breaths ↩︎
For a while there, I couldn’t open my blog feed without being inundated with Fabric. I don’t know Fabric, I’ll learn it if I have to, but damn if I wasn’t aware that was the latest thing Microsoft was pushing. ↩︎

What’s Wrong With This Query?

Words: 375
Time to read: ~2 minutes

Hey!

…

Now that I have your attention, can you see what’s wrong with this query?

Prep

Like every food recipe out there, I’m going to make you sit through backstory before you can get to the good stuff.

One of the fun and annoying aspects of performance tuning is making sure that the results of the tuned version match the results of the pre-tuned version.

In this case, tuning went great – it’s amazing how much faster you can get things done when you don’t waste resources – but the results didn’t match.

Now, I was sure that the queries were logically the same, so I pinged a developer who had worked on the query last, and asked them to confirm what the query was supposed to do:

We want all records where the second table T2 has the specific value in ID2, or the first table T1 has the specific value in ID
– Said developer

Well, great news for me! I made your query better and fixed a previous bug.

Now, can you figure out the bug?

Example

Here are your example tables: T1 and T2

USE [tempdb];
GO
DROP TABLE IF EXISTS
	[dbo].[T1],
	[dbo].[T2];
CREATE TABLE [dbo].[T1]
(
	[ID] INT NOT NULL
);
CREATE TABLE [dbo].[T2]
(
	[ID1] INT NOT NULL,
	[ID2] INT NULL
);
GO

Here’s some records for you:

USE [tempdb];
GO
INSERT INTO [dbo].[T1]
(
	[ID]
)
VALUES 
	(1),
	(2),
	(3),
	(4);
INSERT INTO [dbo].[T2]
(
	[ID1],
	[ID2]
)
VALUES
	(1, 1),
	(1, 2),
	(2, 2),
	(3, 1),
	(3, 2),
	(4, NULL);
GO

Here’s what the expected outcome should be (and what it was post-tuning):

And here’s what the outcome actually was pre-tuning:

Buggy Code

DECLARE @ID INT = 1;
SELECT
	[T1].[ID] AS [T1_ID],
	[T2].[ID1] AS [T2_ID1],
	[T2].[ID2] AS [T2_ID2]
FROM [dbo].[T1]
LEFT JOIN [dbo].[T2]
	ON
	(
		[T1].[ID] = [T2].[ID1]
		AND 
		[T2].[ID2] = @ID
	)
	OR 
	[T1].[ID] = @ID
WHERE
	(
		[T1].[ID] = @ID
		OR 
		[T2].[ID1] IS NOT NULL
	);

Sin É

So here’s the assignment!

Which line or lines in the code causes the bug?
Why?

It feels strange to sign off without explaining what the issue is, but I’ll return after a few days and fill this in.

Once people who love these kinds of questions get a chance to give it a go!

Ádh mór!

T-SQL Tuesday #201 – Temp Tables, Friend or Foe?

Words: 1100
Time to read: ~ 6 minutes

Nothing

Welcome to T-SQL Tuesday, the monthly blog party started by Adam Machanic and maintained by Steve Jones.

Each month, a different host chooses a topic for discussion. This month, we have Jeff Taylor asking us about “temp tables, friend or foe?“

Clicka-de-bait

Ages ago, I broke one of the stored procedures in Brent Ozar‘s First Responder Kit.

I filed that feat under the heading “Cool; Good to Know“, and then promptly forgot about it. Part of me thinks that I forgot about it because I didn’t understand how I accomplished it. While another part is sure I forgot about it cause “good to know” wasn’t on the list of tasks with looming deadlines.

I’m a man of many parts, but of more deadlines. Now, let’s understand it together!

First, there was the ooze

It’s a tactic of dealing with volatile amounts of data to call different stored procedures based on the number of records coming into it. I swear, I didn’t make this up!

Small numbers of records may be fine, but an odd, massive number may trigger a new plan. One that frequent executions and resources on the machine may not agree with.

So, normal, small, fast, very frequent executions get the “happy” path, but the big guy can go his own way. Free to roam, hog, and frizzle away everything he has!

We encountered this scenario once and decided to implement this tactic.

However, while reviewing this new stored procedure volatility countermeasure, I came across a temp table that wasn’t created anywhere in the procedure.

Stored Procedures are isolated. Aren’t they?

Or was it the sludge?

Let’s talk about Dynamic SQL for a moment.

I’m sure, although I won’t make up statistics, that you’ve used Dynamic SQL to query over different databases and collated their results.

Here’s a completely made-up example (Don’t do this. I’m sure it’s stupid, but it’s also very late right now)

DROP TABLE IF EXISTS #tables;
CREATE TABLE #tables
(
	tables_ID		int				NOT NULL	IDENTITY(1,1),
	[database_name]	nvarchar(255)	NOT NULL,
	table_count		int					NULL
);
INSERT INTO #tables ([database_name]) 
SELECT [name] FROM sys.databases;
SELECT * FROM #tables;
GO
DECLARE @i				int = 1;
DECLARE @max			int;
DECLARE @context		nvarchar(MAX);
DECLARE @context_stub	nvarchar(MAX) = N'.sys.sp_executesql';
DECLARE @sql			nvarchar(MAX) = N'WITH
	table_counts AS
	(
		SELECT COUNT(*) AS database_table_count FROM sys.tables
	)
	UPDATE #tables 
	SET table_count = database_table_count
	FROM table_counts
	WHERE tables_ID = @ID';
SELECT @max = MAX(tables_ID) FROM #tables;
WHILE @i <= @max
BEGIN
	SET @context = CONCAT(
			QUOTENAME(
				(
					SELECT TOP (1) [database_name] FROM #tables WHERE tables_ID = @i
				)
			),
			@context_stub
		);
	EXEC @context @sql, N'@ID int', @ID = @i
	SET @i += 1;
END;
SELECT * FROM #tables

I mean, look at line 25 in the code above. We didn’t create any temp table in that dynamic sql, but we’re updating it fine!

There was something I was forgetting: Scope!

Scopé (the é is pronounced)

I had never fully thought about this before.

I thought Dynamic SQL was just strings being executed (and it still kinda is), but it operates under a nested scope of the session.

So, when we say EXEC [sys].[sp_executesql] @stmt = N'INSERT INTO #temp…, for a temp table we’re created in the parent session, we’re ignoring scope boundaries.

The same thing happens with Stored Procedures.

Table Variables? No.

CREATE OR ALTER PROC dbo.TableVar2
AS
BEGIN
	INSERT INTO @table_variable (ID) VALUES (1);
END;
GO
CREATE OR ALTER PROC dbo.TableVar1
AS
BEGIN
	DECLARE @table_variable TABLE (ID int);
	EXEC dbo.TableVar2;
END;
GO
EXEC dbo.TableVar1;

Msg 1087, Level 15, State 2, Procedure TableVar2, Line 5 [Batch Start Line 0] Must declare the table variable “@table_variable”. The module ‘TableVar1’ depends on the missing object ‘dbo.TableVar2’. The module will still be created; however, it cannot run successfully until the object exists. Msg 2812, Level 16, State 62, Procedure dbo.TableVar1, Line 7 [Batch Start Line 17] Could not find stored procedure ‘dbo.TableVar2’.

Variables? No.

CREATE OR ALTER PROC dbo.Var2
AS
BEGIN
	SET @i = 2;
END;
GO
CREATE OR ALTER PROC dbo.Var1
AS
BEGIN
	DECLARE @i int;
	EXEC dbo.Var2;
END;
GO
EXEC dbo.Var1;

Msg 137, Level 15, State 1, Procedure Var2, Line 4 [Batch Start Line 0] Must declare the scalar variable “@i”. The module ‘Var1’ depends on the missing object ‘dbo.Var2’. The module will still be created; however, it cannot run successfully until the object exists. Msg 2812, Level 16, State 62, Procedure dbo.Var1, Line 7 [Batch Start Line 15] Could not find stored procedure ‘dbo.Var2’.

Temp Tables? Yes!

CREATE OR ALTER PROC dbo.Proc1
AS
BEGIN
	CREATE TABLE #t1
	(
		ID int NOT NULL
	);
	INSERT INTO #t1 (ID) VALUES (1);
	SELECT ID FROM #t1;
END;
GO
EXEC dbo.Proc1;
GO
CREATE OR ALTER PROC dbo.Proc2
AS
BEGIN
	CREATE TABLE #t2
	(
		ID int NOT NULL
	);
	EXEC dbo.Proc3;
	SELECT ID FROM #t2;
END;
GO
CREATE OR ALTER PROC dbo.Proc3
AS
BEGIN
	INSERT INTO #t2 (ID) VALUES (3);
END;
GO
EXEC dbo.Proc2;

That’s what the temp table and the stored procedures were doing. Boundary violations don’t really count when it’s my nested scope!

It’s Actually a Donut

Now that that tangent is all squared away, let’s return to how I broke a First Responder Kit stored procedure.

 CREATE OR ALTER PROC dbo.NormalProc
AS
BEGIN
	CREATE TABLE #BlitzFirstResults
	(
		ID int NOT NULL
	);

	INSERT INTO #BlitzFirstResults VALUES (1);

	SELECT ID FROM #BlitzFirstResults;
END;
GO

EXEC dbo.NormalProc;
GO

/*
DROP PROC dbo.NormalProc;
DROP TABLE IF EXISTS #BlitzFirstResults
*/

 /*
DROP TABLE IF EXISTS #BlitzFirstResults
*/
IF OBJECT_ID(N'tempdb..#BlitzFirstResults') IS NULL
BEGIN
	CREATE TABLE #BlitzFirstResults
	(
		BlitzFirstResults_ID int NOT NULL
	);
END
SELECT * FROM #BlitzFirstResults;
EXEC dbo.NormalProc

(0 rows affected) Msg 207, Level 16, State 1, Procedure dbo.NormalProc, Line 11 [Batch Start Line 0] Invalid column name ‘ID’.

**Different Columns Does Not A Stored Procedure Temp Table Boundary Violation Good Time Make**

So, yeah … in truth, I didn’t so much break the stored procedure as I interfered with it…but that just doesn’t sound great when I say it like that.

Now, I know that my temp table in the parent session …got in the way… of the stored procedure’s inner temp table.

Anyway, things happened

What does this have to do with T-SQL Tuesday? Oh! The barest of passing similarities, but I’m team temp tables. They’re weird and (more) wonderful than I realised, but I’ll leave it to smarter people than myself to give you the raw numbers.

All I know is that I still have billion-row tables, procedures with multiple WHERE col1 = @param1 OR @param1 IS NULL) […] filters, and uptime requirements that make finding an outage window for UPDATE STATS more difficult than a greyscale Where’s Wally book.

I’ll take every trick in the book.

[Speaking of books, all this is wonderfully documented in the Microsoft Docs page, but someone didn’t think to read that part… – Editor Shane]

T-SQL Tuesday #200: It’s Bad If I See

Words: 578
Time to read: ~3 minutes

I think that I shall never see
this developer’s query is blinding me

– Joyce Kilmer (if she was a DBA)

Welcome to T-SQL Tuesday, the monthly blog party started by Adam Machanic ( blog ) and maintained by Steve Jones (blog | social media).

Each month, a different host chooses a topic for discussion. This month, we have Brent Ozar ( blog | social media ) asking us “if [we’re] looking at a query, it’s bad if [we] see…“

Musings

This post, inspired by a recent one by Hannah Vernon ( blog | social media ), is about WHILE loops and CURSORs.

Here’s my prejudiced take: if your query has WHILE loops and/or CURSORs, then I’m going to assume it’s bad.

Now, the astute among you will note what I just said with the blog post I linked to, and that’s a fair point. This is a knee-jerk reaction to opening up a piece of code and skimming its contents.

So far, in my relatively short stint as a DBA (I’m going to keep saying that no matter how long I am one, btw), I have only encountered 4 scenarios where these Row-By-Agonising-Row (RBAR) methods are acceptable.

See Hannah’s post – and it has to be commented that it’s an informed decision.
You need to call a stored procedure on the records – I don’t know how to get around that
You’re doing batching on mass writes – it’s more set-based inside RBAR than RBAR instead of set-based then
Temporal tables – I’m still salty about this…

The Feck It It’s Worth It Scenario

Read her blog post (I linked it again and everything.)

I’m normally an advocate for “write it anyway in case the way you say it gets through to someone”, but her blog posts have been on fire lately, and more people should be reading them.

Wrench and Sproc it

Apart from gutting the stored procedure and inserting its code in your set-based method, there’s no real way around this.

And if that stored procedure calls another stored procedure? Boy howdy, I ain’t touching that one with a ten-foot pole!

Turtles All the Way Down

Today is not Link to Other People’s Blog Day, but I’m going to pretend it is.

Here’s Micheal J Swart ( blog ) on batching.

I’m going to call this Set-By-Agonising-Set instead of Row-By-Agonising-Row.

Whatever name it goes by, whatever the reason for it, e.g. transaction log space, locking, etc., batching solves issues that come around at scale.

Saltier Than the Old Man and the Sea

Temporal tables… _deep breaths, Shane_

Do you know how to query temporal tables using the FOR SYSTEM_TIME syntax?

Well, you can do:

... FOR SYSTEM_TIME ALL ...
... FOR SYSTEM_TIME AS OF '<date time value> ...
... FOR SYSTEM_TIME AS OF @<date time variable>...

This is great…for uniformity. And all of our result sets are uniform, right?

However, if you have a table of results and you want a different FOR SYSTEM_TIME AS OF value for each record in the results table…

Granted, I have no idea how to implement this, or if it’s even possible, but I’d like it!

RBAR = Crowbar

If I’m looking at a query, it’s bad if I see WHILE loops, or CURSORs, or RBAR options without a documented or commented reason why.

If you’ve come across any other scenario, please let me know. The temporal table is the most recent one for me, and I’m relying on that saltiness to keep me young and preserved. Anything extra would be appreciated.

Ordering and Choices in PowerShell

Ah! Hash tables!

Ever since I saw a presentation by Anthony Howell ( Blog ), aka PoshWolf, about them, read the blog post by Kevin Marquette ( Blog ) about them, and was enlightened by multiple PowerShell community members (e.g. Chris Dent, Mathias “IISResetMe” Jessen, etc.) about them, I’ve loved hash tables.

However, there is one drawback I have with hash tables: they don’t default to the order in which they are inserted. I’m OK with this since I come from a DB background, and I’m used to order not being enforced unless I specify an ORDER BY. Not everyone is as lenient as we are, though, and the vast majority of the louder masses expect this ordering.

Now, there are ways around this unordered aspect of hash tables. The main way is the [ordered] type accelerator; you can add this to the start of your hash table to keep the default orderings.
I use this when I want hash tables, but also want guaranteed orderings.

Well, that was a short blog post. Thanks!

Sacrificing Idiomatic for Changeable

I know about using [ordered], and now you’ve learned about using [ordered], but what about people using my scripts that aren’t part of either knowledge group?
Oh, I could add a comment, and I could train the people, but what happens if I ~~get hit by a bus~~ win the lottery in the morning? There will eventually be a time when I’m not there, and people will have to use my scripts.

So, [ordered] seems a bit too unknown at the moment for the people who regularly use my scripts.

Which is a pity, cause the current version uses [ordered] hash tables pretty extensively.
Is there something else that we can use instead?

Yes, Arrays

Ah, the humble array. They’re not that bad once you get to know them.
Once I started thinking about these little data types, I realised that they could be a potential answer to this problem.

$pscustomobjectArray = @(
    [PSCustomObject] @{ ID = 0; Name = 'Test'; Type = 'Dev' }
    [PSCustomObject] @{ ID = 1; Name = 'Test2'; Type = 'Dev' }
    [PSCustomObject] @{ ID = 2; Name = 'PreProd_1'; Type = 'PreProd' }
    [PSCustomObject] @{ ID = 3; Name = 'PreProd_2'; Type = 'PreProd' }
    [PSCustomObject] @{ ID = 4; Name = 'Prod_1'; Type = 'Prod' }
    [PSCustomObject] @{ ID = 5; Name = 'Prod_2'; Type = 'Prod' }
    [PSCustomObject] @{ ID = 6; Name = 'Prod_3'; Type = 'Prod' }
)

for ($i = 0; $i -lt $pscustomobjectArray.Count; $i++) {
    "The $i-th object is $($pscustomobjectArray[$i])"
}

It would just need a bit of a rewrite…

Script

Initialize-Choice_v2.ps1

Old = Left. Rewrite = Right.
The default is because there’s always one person who just runs these things as is…

Thankfully, arrays keep their order, so I can rely on them in the script.

In Use

What does this mean? This means that you can use the standard input that people are accustomed to in PowerShell, such as PSCustomObject, CSV, JSON, etc.

As long as we can select the property, we can then filter it down to this specific property.

Example

For each of these examples, we’re passing in a list of choices. Then we’re filtering down the list to the option we chose.

We can do this multiple times, if we want.

Here’s our list of choices to… aha… choose from.

Example PSCustomObject


$ChoicesCustomObject = [PSCustomObject]@{
    Name = 'Test01'
    Value = '1a'
}, [PSCustomObject]@{
    Name = 'Test01'
    Value = '1b'
}, [PSCustomObject]@{
    Name = 'Test02'
    Value = '2a'
}, [PSCustomObject]@{
    Name = 'Test02'
    Value = '2b'
}, [PSCustomObject]@{
    Name = 'Test03'
    Value = '3a'
}, [PSCustomObject]@{
    Name = 'Test03'
    Value = '3b'
}
<# Get a unique list of available choices #>
$AvailableChoices = $ChoicesCustomObject | Select-Object -ExpandProperty Name -Unique
<# Get the chosen index from the user #>
$ChosenIndex = Initialize-Choice_v2 -Title "Test Get-Choice" -Caption "Select an option" -Choices $AvailableChoices 
<# Filter down to the chosen option #>
$ChosenOption = $ChoicesCustomObject | Where-Object Name -eq $AvailableChoices[$ChosenIndex] 
$ChosenOption

Example Csv

$ChoicesCSV = @'
"Name","Value"
"Test01","1a"
"Test01","1b"
"Test02","2a"
"Test02","2b"
"Test03","3a"
"Test03","3b"
'@ | ConvertFrom-Csv
<# Get a unique list of available choices #>
$AvailableChoices = $ChoicesCSV | Select-Object -ExpandProperty Name -Unique
<# Get the chosen index from the user #>
$ChosenIndex = Initialize-Choice_v2 -Title "Test Get-Choice" -Caption "Select an option" -Choices $AvailableChoices 
<# Filter down to the chosen option #>
$ChosenOption = $ChoicesCSV | Where-Object Name -eq $AvailableChoices[$ChosenIndex] 
$ChosenOption

Example JSON

$ChoicesJSON = @"
[
  {
    "Name": "Test01",
    "Value": "1a"
  },
  {
    "Name": "Test01",
    "Value": "1b"
  },
  {
    "Name": "Test02",
    "Value": "2a"
  },
  {
    "Name": "Test02",
    "Value": "2b"
  },
  {
    "Name": "Test03",
    "Value": "3a"
  },
  {
    "Name": "Test03",
    "Value": "3b"
  }
]
"@ | ConvertFrom-Json
<# Get a unique list of available choices #>
$AvailableChoices = $ChoicesJSON | Select-Object -ExpandProperty Name -Unique
<# Get the chosen index from the user #>
$ChosenIndex = Initialize-Choice_v2 -Title "Test Get-Choice" -Caption "Select an option" -Choices $AvailableChoices 
<# Filter down to the chosen option #>
$ChosenOption = $ChoicesJSON | Where-Object Name -eq $AvailableChoices[$ChosenIndex] 
$ChosenOption

Example Multiple

$ChoicesCustomObject = [PSCustomObject]@{
    Name = 'Test01'
    Value = '1a'
}, [PSCustomObject]@{
    Name = 'Test01'
    Value = '1b'
}, [PSCustomObject]@{
    Name = 'Test02'
    Value = '2a'
}, [PSCustomObject]@{
    Name = 'Test02'
    Value = '2b'
}, [PSCustomObject]@{
    Name = 'Test03'
    Value = '3a'
}, [PSCustomObject]@{
    Name = 'Test03'
    Value = '3b'
}
<# Get a unique list of available choices #>
$AvailableChoices = $ChoicesCustomObject | Select-Object -ExpandProperty Name -Unique
<# Get the chosen index from the user #>
$ChosenIndex = Initialize-Choice_v2 -Title "Test Get-Choice" -Caption "Select an option" -Choices $AvailableChoices 
<# Filter down to the chosen option #>
$ChosenOption = $ChoicesCustomObject | Where-Object Name -eq $AvailableChoices[$ChosenIndex] 
<# Filter down to the value #>
$ChosenValue = Initialize-Choice_v2 -Title "Choose Value" -Caption "Select a value for $($ChosenOption.Name)" -Choices $ChosenOption.Value
<# Return the chosen option #>
$ChosenOption[$ChosenValue]

Caveat

Yes, I am aware that the values are off by one; for example, the first value shows 1 on the confirmation screen but returns a 0. I bowed to requests from people who would be using the code but were not comfortable with indexes starting at 0.

Feel free to change that in your script.

Overall

This is a case of “exception making the rule”. I’ve said before that I prefer not to have user prompting in my scripts, since a script that requires human intervention is not something that can be fully automated.
However, whether it’s risk-averse business leaders or novices to scripting, there are times when an expectation of human interaction is present.

If there is no getting away from this, then I can make the process easier. Here’s hoping that making it easier and easier to use will lead to more automation… or at least more.

Hopefully, this script can help.

How SQL Server’s Compute Scalar Will Impede You

Time to read: ~ 2 minutes

Words: 417

Postgres Blogs

We’re getting more and more Postgres instances at work. To get up to speed on Postgres, I’ve started to expand my RSS feed to include Postgres blogs.

It was one of those blogs, by David Stokes, that captured my attention; namely, “How PostgreSQL’s Aggregate FILTER Will Spoil You“

It got me thinking, “Huh, does SQL Server not have filters? And, if not, how can we work around that?”

So, cheers, David, for the idea for this post.

The Big Set up

We’re going to use the same setup that David has in his post

USE tempdb;
GO

CREATE TABLE z (a int, b int, c int);
GO

INSERT INTO z VALUES (1, 10, 100), (2, 20, 200), (3, 30, 300), (4, 40, 400);
GO

SELECT a,b,c FROM z;
GO

Stupid, but does it work

Now, SQL Server doesn’t have the filter option, but we can do some pretty weird things, like a SELECT...WHERE statement with no FROM clause.

SELECT
	a,b,c,
	[filter?] = (SELECT b WHERE b > 11)
FROM z;
GO

So, is that it? Can we just throw that into a COUNT() function and essentially have the same thing as Postgres?

SELECT
	all_rows	= COUNT(*),
	b_gt_11		= COUNT((SELECT b WHERE b > 11))
FROM z;
GO

Workaround

We can rewrite that strange little filter we have using an OUTER APPLY.

SELECT
	a,b,c,
	[filter?] = bees.bee
FROM z
OUTER APPLY (SELECT b WHERE b > 11) AS bees (bee);
GO

Now, can we throw that in a COUNT() function?

SELECT
	all_rows	= COUNT(*),
	b_gt_11		= COUNT(bees.bee)
FROM z
OUTER APPLY (SELECT b WHERE b > 11) AS bees (bee);
GO

Things I don’t know

Postgres, yet. Still learning this one.
Why the OUTER APPLY works, but the subquery doesn’t?
Let’s review the plans and see if we can identify a culprit.

Strangely, the first Compute Scalar (from the left, after the SELECT) is simply a scalar operator applied to the other Compute Scalar.

It’s enough to block aggregation, though, but I’ll leave it to more specialised people than I to tell me why. I’ll just store this under “errors out” for future reference.

Performance Implications
This is a test on 4 rows. What would happen if this were tens of millions of rows?
…
I leave that to the reader.

Getting SQL Server Version using dbatools

Time to read: ~ 4 minutes

Words: 730

I will discuss different ways to get the SQL Server version using PowerShell.

I’ll explore a function you can use, and how a dictionary/hash table could also work.

Finally, I’ll discuss how neither of these are needed since dbatools/SMO has a better way.

The Initial Issue

I’m sure you’ve needed the version of a SQL Server instance for a report a few times.
And, surprisingly, not many people are up for parsing the output of @@VERSION.

I can’t see why not, it’s perfectly fine. It is not the easiest thing in the world, but it is also not the hardest. But, you do you.

Invoke-DbaQuery -SqlInstance localhost\SQL2019 -Query "SELECT version = @@VERSION;" | FL

No, I haven’t updated this instance in a while. Thank you for asking

My team lead has a function that he uses to get these versions, but it encountered an issue. We recently updated to a later version of SQL Server, and the function stopped working.

$instance = Connect-DbaInstance -SqlInstance localhost\SQL2019

$instance.Version.Major

Get-WhatVersionAmI -Version $instance.Version.Major

It seems to work fine if I pass in an older version of SQL Server.

Get-WhatVersionAmI -Version 14

Oh! 14! Yeah, I know 14!

So, what’s the issue?
I’m going to take a look under the hood of that function, and see if I can spot what’s wrong.

Checking under the function’s hood

If you don’t have the code, or are screen-sharing, you can query the function provider to get the definition of most PowerShell functions.

Get-ChildItem function:\Get-WhatVersionAmI | Select-Object *

There’s more, but it boils down to “this is a simple function”.

Taking a look at the function, here’s the gist of the function block:

param([string] $Version)

    switch ($Version) {
        14 { "SQL Server 2017" }
        13 { "SQL Server 2016" }
        12 { "SQL Server 2014" }
        default { "Unknown" }
    }

This seems simple enough. Hell, it’s just a switch statement that takes the input and spits out the matching output.

Seeing something this simple I’d almost suggest using a dictionary

Using a dictionary instead

This is the equivalent code of the function, but using a hash-table.

$dict = @{
    14 = "SQL Server 2017" 
    13 = "SQL Server 2016" 
    12 = "SQL Server 2014" 
}

$version = $dict[15]
if (!$version) {
    $version = "Unknown"
} 

"Version: $version"

The benefit of this approach is:

it’s easier to modify. I don’t need to update the function and either dot-source it, e.g. . <path_to_function_file.ps1>, or re-run it in memory to use it. I only need to add an entry to the hash table.

$dict = @{
    15 = "SQL Server 2019" 
    14 = "SQL Server 2017" 
    13 = "SQL Server 2016" 
    12 = "SQL Server 2014" 
}

$version = $dict[15]
if (!$version) {
    $version = "Unknown"
} 

"Version: $version"

The problem with this approach is:

I have to keep it updated, and I’ll find it difficult if I have to add it to multiple scripts.

Shift left?

The above approaches have common issues. They have to be ported to every script I have, run into memory, be in sync, etc., etc.

Can I do something about that? Can this be shifted left somehow? Can I remove some of the work that I’ve to do? In the words of Homer Simpson “can’t someone else do it?”.

Why yes, yes they can.

Using dbatools

When dbatools connects to a SQL Instance, the usually returned object is a Microsoft.SqlServer.Management.Smo.Server, and that object has a few methods. The one I’m interested in is GetSqlServerVersionName.

Connect-DbaInstance -SqlInstance localhost\SQL2019 | Get-Member -MemberType Method -Name GetSql*

Many methods have been returned, but I only care about one now.

Now, I can connect to an instance, and query what version it is without any external functions or scripts:

Connect-DbaInstance

$instance = Connect-DbaInstance -SqlInstance localhost\SQL2019
$Instance.GetSqlServerVersionName()

Hey, the function is even there on Microsoft.SqlServer.Management.Smo.Database from Get-DbaDatabase, in case I get the strange notion that the version changes from database to database.

Get-DbaDatabase

$databases = Get-DbaDatabase -SqlInstance localhost\SQL2019
$databases.GetSqlServerVersionName()

I don’t think they change, by the way.

End

I’m a dbatools fan. I’m also slightly lazy, which is why I like automation so much.

Now that I know that there’s a built-in way to get a proper human readable version of an instance. You better believe I’m using that!

T-SQL Tuesday 183 – Tracking Permissions

Words: 593

Time to read: ~3 minutes

PowerShell Scripts for Checking SQL Server Login SIDs

Welcome back to T-SQL Tuesday, the monthly blogging party where we are given a topic and asked to write a blog post about it.

This month, we have Steve Jones [ blog ] asking us to talk about permissions.

Before

Normally, I would have skipped this month because I’ve talked about permissions before, and I feel like it’s one of the better posts I’ve written.
But I’m trying to write more, so that can’t be an excuse anymore.

So, let’s talk about AGs, DistAGs, and SQL Server authentication login SIDs.

AGs and DistAGs

In work, we have Availability Groups (AGs) and Distributed Availability Groups (DistAGs), depending on the environment level, e.g. Prod, Pre-Prod, QA, Dev, etc.

When we plan failovers for maintenance, we run through a checklist to ensure we can safely complete the failover.

One of these checks is to ensure that the correct logins with the correct SIDs are on each replica. Otherwise, we could get flooded with a lot of “HEY! My application doesn’t connect anymore! Why you break my application“, when apps try and log into the new primary and the user SIDs don’t match the login SIDs.

While I don’t have a problem with doing a query across registered servers, I threw together a quick script in PowerShell with dbatools that does the job of checking for me. And, like most things that happen in business, this temporary solution became part of our playbook.

Who knows! Maybe this quick, throwaway script could also become part of other people’s playbooks!

I’m not sure how I feel about that…

Scripts

We’re using dbatools for this because I think it’s the best tool for interacting with databases from a PowerShell session regardless of what Carbon Black complains about.

Getting the AG Replicas

Getting the AGs for each server is relatively simple, using a combination of Get-DbaRegServer and Get-DbaAvailabilityGroup.

Then, we can use the ever-respected Get-DbaLogin to get a list of logins and their SIDs per replica.
If we have a mismatch, we will have an issue after we failover. So best nip that in the bud now (also, I had this phrase SO wrong before I looked it up!).

$InstanceName = 'test' 
# $InstanceName = $null # Uncomment this line to get ALL
$Servers = Get-DbaRegServer

if ($InstanceName) {
	# Find the server that ends in our instance name
	$Servers = $Servers | Where-Object ServerName -match ('{0}$' -f $InstanceName)
}

# Get all replicas per Instance name that are part of AGs...
$DAGs = Get-DbaAvailabilityGroup -SqlInstance $Servers.ServerName |
	Where-Object AvailabilityGroup -notlike 'DAG*' | # Exclude DAGs which just show our underlying  AG names, per our naming conventions.
	ForEach-Object -Process {
		$instance = $null
		$instance = $_.InstanceName

		foreach ($r in $_.AvailabilityReplicas) { # I'd like 1 replica per line, please.
			[PSCustomObject] @{
				InstanceName = $instance
				Replica      = $r.Name
			}
		}
	} |
	Select-Object -Property InstanceName, Replica -Unique |
	Group-Object -Property InstanceName -AsHashTable

# Get a list of Logins/SIDs that don't match the count of replicas, i.e. someone forgot to create with SIDs...
foreach ($d in $DAGs.Keys) {
	Write-Verbose "Working on instance: $d" -Verbose
	Get-DbaLogin -SqlInstance $DAGs[$d].Replica |
		Group-Object -Property Name, Sid |
		Where-Object Count -ne $DAGs[$d].Count |
		Select-Object -Property @{ Name = 'Instance'; Expression = { $_.Group[0].InstanceName }},
			@{ Name = 'Occurances'; Expression = { $_.Count }},
			@{ Name = 'Login/SID'; Expression = { $_.Name }}
}

I got 99 problems, and Login/SIDs are one! Or I have 5 Login/SID problems.

Cure

They say “prevention is better than cure”, and I’d love to get to a stage where we can “shift left” on these issues. Where we can catch them before someone creates a login with the same name but different SIDs on a replica.

But we’re not there yet. At least, we can find the issues just before they impact us.

That’s something, at least!

T-SQL Tuesday #182 – Integrity

Time to read: ~ 3 minutes
Words: 531 words

Welcome to T-SQL Tuesday, the monthly blogging party where we are given a topic and have to talk about it. Today, we have Rob Farley ( blog | bluesky ), talking about integrity.

I’ll admit that it’s been a while since I’ve written a blog post. It’s been a combination of either burnout or busyness, but let’s see if I still have the old chops. Plus, I’m currently sick and resting in bed, so I have nothing better to do.

I’m one of the few who haven’t had experiences with corruption in our database. Apart from Steve Stedman’s ( blog ) Database Corruption Challenge, that is.

With that being said, what do I have to say about this topic then? Well, let’s talk about the first version of corruption checking automation that we introduced in work.

Now, this is just the bare bones and many different iterations since then, but the essence is here.

Overview

Like many shops out there, we can’t run corruption checking on our main production database instance. So, then, what do we do? We take the backups and restore them to a test instances, and then run corruption checking on those restored databases.

At least this way we can test the backups we take can be restored, as well.

But, I don’t want to spend every day manually restoring and corruption checking these databases, so let’s automate this bit…

Limitations

A quick scour of the interwebs brought back something extremely close to what I want by Madeira Data Solutions. It had some extras that I didn’t want, though.

More importantly, it used some functions that our dreaded antivirus software still screams false positives about. So, they would stop our script from running if we even tried.

So, a script re-write was required.

Script

Here’s the link to the script in GitHub if you want to check it out.
Use at your own peril. Also, default gist embedding on this platform is visible off-putting.

General Rundown

Here’s a general rundown of the parts of the code, just to make it easier to grok. It’s not to bump the word count on this post.

Cave Canum

This is an old version of the script. The first version, if my flu-riddled brain can remember correctly, so there are small bugs here.

Off the top of my head, I think it still:

only works if the backup file is called FULL
only works if there is a single FULL backup file per database ( _that got fixed real fast!_ )
A.N.Other

Grab the Full Backup

Does it expect FULL in the file name? Yes. Should it? Arguably not.

Restore Everything on the Path

We did a database per database basis. “RBAR” is fine outside a DB, right?

Faire le Corruption Check

Report on the results

Because if you don’t tell people, have you actually done it?

Ar aon nós

Like I said, that script is the first iteration.

Use at your own peril.

Here be dragons and krakens and DBCC WRITEPAGE.

My main point is have the INTEGRITY to say “Yes, I have regular corruption checking of my databases”.

See what I did there?

#TSQL2sday 171: Describe the Most Recent Issue you Closed

What happens when nothing changes, sp_send_dbmail gets given sysadmin, and you still can’t get emails.

Words: 817

Time to read: ~4 minutes

Invitation

Welcome to TSQL2sday, the monthly blogging party where we are given a topic and are asked to write a blog post about it.

This month we have Brent Ozar ( b ) asking us about the latest issue closed.

I don’t like posting about issues unless I fundamentally understand the root cause. That’s not the case here. A lot of the explanation here will be hand-waving while spouting “here be dragons, and giants, and three-headed dogs”, but I know enough to give you the gist of the issue.

sp_send_dbmail

Like most issues, even those not affecting production, this one was brought to the DBA team as critical and needed to be fixed yesterday.

A Dev team had raised that a subset of their SQL Agent jobs had failed. The error message stated:

Msg 22050, Level 16, State 1, Line 2
Failed to initialize sqlcmd library with error number -2147467259

That makes sense; the only jobs that were failing were ones that called sp_send_dbmail using a @query parameter. And I know that when you use that parameter, the code is given to the sqlcmd exe to run it for you.

Google fu

From research (most of the time, I ended up in the same post), the error fragment “failed to initialize sqlcmd library with error number” could be related to a number of things.

The database object not existing
No, the agent fails even when the query is SELECT 1;
The wrong database context used
No, SELECT 1;
sqlcmd not being installed or enabled
It was working beforehand, so I would say not.
Permissions

Permissions

Well I had already tested that the query worked as long as it was valid SQL, so let’s try permissions.

I increase permissions…no luck.
I grant a bit more permissions…nope.
A bit more permissions…still nothing.
ALL the permissions… MATE, YOU’RE A SYSADMIN! WHAT ARE YOU ON ABOUT?!! …ahem… nothing.

Workaround

Strangely enough, the post mentioned that using a SQL Authentication account worked.
So we tested it using EXECUTE AS LOGIN = 'sa'; and it worked.
Which was weird, but I’ll take a workaround. Especially since it gave us time to investigate more.

Thanks to dbatools, I threw together a PowerShell script that went through all of the SQL Agent jobs that contained sp_send_dbmail and wrapped them up.

EXECUTE AS LOGIN = 'sa'; EXEC dbo.sp_send_dbmail ...; REVERT

I’m not going to share that script here cause it is a glorious mess of spaghetti code and if branches.
I gave up on regex and did line-by-line thanks to the massive combinations of what was there e.g.

EXEC msdb.dbo.sp_send_dbmail .
EXECUTE sp_send_dbmail.
EXEC msdb..sp_send_dbmail.
sp_send_dbmail in a cursor so we need to revert after each call in the middle of the cursor.
sp_send_dbmail where there are spaces in the @body parameter so I can’t split on empty lines.

What’s Happening?

After getting the workaround in place, the team lead and I noticed something strange.

Sure, EXECUTE AS LOGIN = 'sa'; worked, but try it as a Windows Domain login and you get something different.

Could not obtain information about Windows NT group/user '<login>', error code 0x5

Something weird was happening between SQL Server and Windows.
Great for me! I get to call in outside help.
Not great for ye! The remaining explanation is going to be shallower than the amount of water I put in my whiskey.

What Changed

Nothing!

Or so we were told. Repeatedly.
We did not believe that. Repeatedly.

Next, we started to get “The target principal name is incorrect. Cannot generate SSPI Context” on the servers.

Not being able to send emails from a SQL Agent Job is one thing, but not being able to Windows Authenticate into a SQL Instance at all is another thing all together.

Eventually, as awareness of the issue increased, the problem was narrowed down to a server configuration on a Domain Controller. I’m assuming that the name of this server configuration is “nothing”.

“Nothing” was set on one server but not the other meaning that using one DC over another meant Kerberos did something that it was not supposed to. I’m reliably informed that “nothing” has something to do with encryption and gatekeeping. I reliably replied that Kerberos should be spelt Cerberus but was ignored.

Testing

With “nothing” in place properly, the SSPI Context errors disappeared.

I reverted the workaround EXECUTE AS wrapper on sp_send_dbmail and emails started flowing again, even without the wrapper. Even with permissions reduced back to what they were.

Research

Sometimes the problem is actually outside SQL Server. Those are the good days. Other days it is a SQL Server problem and you have to fix it yesterday.

All I can do is take what happens, do a bit more research, and learn from them. That way, if it were to happen again, I can speed up the resolution process.

At least this way, if someone ever asks me if I know anything about Kerberos, I can tell them that I know about “nothing”.

I’ll enjoy that.

	Shane O'Neill on T-SQL Tuesday #200: It’s…
	Temp Tables and Stor… on T-SQL Tuesday #201 – Tem…
	Martin Guth on T-SQL Tuesday #200: It’s…
	T-SQL Tuesday Roundu… on T-SQL Tuesday #200: It’s…
	Brent Ozar on T-SQL Tuesday #200: It’s…

Welcome!

What is Relevant?

Relevancy

Money3

Fame

Respect4

Etc.

Here’s my answer:

Why?

Prep

Example

Buggy Code

Sin É

Nothing

Clicka-de-bait

First, there was the ooze

Or was it the sludge?

Scopé (the é is pronounced)

It’s Actually a Donut

Anyway, things happened

Musings

The Feck It It’s Worth It Scenario

Wrench and Sproc it

Turtles All the Way Down

Saltier Than the Old Man and the Sea

RBAR = Crowbar

Sacrificing Idiomatic for Changeable

Yes, Arrays

Script

In Use

Example

Example PSCustomObject

Example Csv

Example JSON

Example Multiple

Caveat

Overall

Postgres Blogs

The Big Set up

Stupid, but does it work

Workaround

Things I don’t know

The Initial Issue

Checking under the function’s hood

Using a dictionary instead

Shift left?

Using dbatools

Connect-DbaInstance

Get-DbaDatabase

End

PowerShell Scripts for Checking SQL Server Login SIDs

Before

AGs and DistAGs

Scripts

Getting the AG Replicas

Cure

Overview

Limitations

Script

General Rundown

Grab the Full Backup

Restore Everything on the Path

Faire le Corruption Check

Report on the results

Ar aon nós

Invitation

sp_send_dbmail

Google fu

Permissions

Workaround

What’s Happening?

What Changed

Testing

Research

Money³

Respect⁴