TSQL2sday #149: Advice you’d give your younger self

Words: 459

Time to read: ~ 3 mins

Welcome to TSQL Tuesday 149! The monthly blogging party where we are given a topic to write a post around.

This month Camila Henrique (blog) asked us about the advice we would give our younger selves.

It’s not that I’m smart; it’s just that I stay with problems longer…mainly cause I’ve caused them.

Albert Einstein, probably

Nobody likes a show-off

Really? There’s no need for a stored procedure because you can write out the syntax to update a business order by memory? Wow, that’s great. </sarcasm>

Are you expecting everyone new to learn the code as well?
How useful is that going to be when you leave?

Run this procedure and pass in the business order id” versus “So, what you gotta do here is join these tables based on these ids but only when the square root of -1 isn’t i.
Which do you think is the better option, Einstein?

Also, do you realise how much time you are wasting by manually typing out all the T-SQL code each and every time?!

Simple advice here; do not turn down a good idea because you think you don’t need it. You’re not half as smart as you think you are, and you’re twice as dumb as you feel.

Pride and Prejudice

If you want to go fast, go alone. If you want to go far, go together. Whatever you do, go away!

My sister, I guess

This is another instance of not knowing a good thing when it’s staring you in the face! If programming languages could stare, that is.

You’re soon going to be shown PowerShell as a way to automate some work. Yes, it’s version 2, so it will be rough around the edges. Hell, you’re rough around the edges! You’d like people to take a chance on you, so take a chance with it.

It, like you, will improve. However, it’ll improve at a rate and level that you can only hope to achieve.

Thanks to PowerShell, you will meet people that you never would otherwise. You will learn aspects that will improve every facet of your life. Prejudice ruins a lot of things, don’t let it ruin you!

There are two mistakes one can make along the road to truth… not going all the way, and not starting. No documentation is a close third though

Buddha, I’m assuming

To tie these up together and link back to a recent post by Ken Fisher (blog | Twitter). There is no such thing as a one-off request.

There is nearly always a request that starts with, “Hey, you know that query you ran for me the last day?”.

Learn to document, learn to automate, and learn to use Source Control, ya git.

The Surprising Working of TrimEnd

Time to read: ~ 2 minutes

Words: 397

A couple of days ago, I was running some unit tests across a piece of PowerShell code for work and a test was failing where I didn’t expect it to.

After realising that the issue was with the workings of TrimEnd and my thoughts on how TrimEnd works (versus how it actually works), I wondered if it was just me being a bit stupid.

So I put a poll up on Twitter, and I’m not alone! 60% of the people answering the poll had the wrong idea as well.

Let’s have some code to show what we mean.

'Shanes_sqlserver'

Incorrect Ideas

The vast majority of code that I have seen out in the wild has strings as the inner portion of TrimEnd

'Shanes_sqlserver'.TrimEnd('sqlserver')


The code works how I thought that it would, removing the “sqlserver” portion of the string at the end. Now, let’s try it again and remove the underscore as well.

'Shanes_sqlserver'.TrimEnd('_sqlserver')



See! Where has my “s” and “e” gone?!

Let’s look at the overload definitions for TrimEnd by running the code without the brackets after the method.

'Shanes_sqlserver'.TrimEnd


No overload definition takes a string; they either take a char or an array of chars. Is that what’s happening here?

# Takes an array of chars
'Shanes_sqlserver'.TrimEnd('_', 's', 'q', 'l', 'e', 'r', 'v')

# Turns a string into an array of chars
'Shanes_sqlserver'.TrimEnd('_sqlerv')

# Order doesn't matter either
'Shanes_sqlserver'.TrimEnd('vrelqs_')

A New Way of Thinking

So TrimEnd takes the characters that we provide inside the method and removes them from the end until it reaches the first non-matching character.

This example explains why our first example, with TrimEnd('sqlserver'), removes everything up to the underscore.

'Shanes_sqlserver'.TrimEnd('sqlserver')
# -----^ First non-matching character (_)


However, when we include the underscore, the first non-matching character shuffles back.

'Shanes_sqlserver'.TrimEnd('_sqlserver') 
# --^ First non-matching character (n)

Initial Problem

Now that we have a new understanding of how TrimEnd works, how can we remove the “_sqlserver” part of the string?

Split it in two.

'Shanes_sqlserver'.TrimEnd('sqlserver').TrimEnd('_')
# -----^  First non-matching character (_)
# ----^  First non-matching character after first TrimEnd (s)

This rewrite works for us since we have a defined character that acts as a stop-gap. If that stop-gap isn’t possible, then -replace may be our best option.

'Shanes_sqlserver' -replace '_sqlserver'

Always good to get a better understanding of PowerShell. If my tests catch more of these misunderstandings that I can learn from, then I’m OK with that!

T-SQL Tuesday #140: What have you been up to with containers?

Time to read: ~ 2 minutes

Words: 335

Kubernetes

So this is a post that will not be educational, but it’s the latest encounter that I’ve had with containers, so it’s the most present in my mind.
Hey, hopefully, it brings a laugh to some people.

I’ve been looking into Kubernetes. I’ve not gotten very far with it, but I managed to set up a replica in Ubuntu WSL2 on my laptop.


Everything was all well and good apart from being unable to connect to the database from Azure Data Studio but again, all good.

Fast forward a couple of days where I’m trying to share screen, and my laptop started getting very slow, the fans started getting very loud, and the performance just tanked.

Taking a look at the ol’ Task Manager, I saw a “vmmem” process taking a massive amount of memory. A quick google search led to the culprit being virtual machines.

Here started what I can only describe as a Benny Hill sketch where I tried to remove the pods only to have the Kubernetes create the pods again!

Remove the pods – check for pods – the same amount created a few seconds ago!
Argh!!!

Containers

Eventually, I dropped the pods and managed to get my laptop under control.
Still wanting to have a SQL instance to work with, I managed to spin up a Docker container and have a developer instance of SQL 2019 up and running on my laptop.

Thankfully I know enough about containers to stop the instance when I don’t need it and only start it up again when I do.

It’s strange to think that the day has arrived where I resort back to my knowledge of containers as the familiar option!
There’s a good thing in there somewhere, maybe put a backstop into my learnings? Just enough to know how to stop if the situation goes wrong or go too far.

I still intend to continue researching Kubernetes, but maybe I’ll deepen my knowledge on Containers in the meantime.

Start-IncreasingPushback

Words: 498

Time to read: ~3 minutes

Intro

If you look back over some of the posts that I wrote in October this year, you may have realised that there was a motif going on.

I used a homebrew pushup tracker as a data source for a couple of blog posts. A group of friends and I were attempting to “push out” (excuse the pun) 3,000 pushups over the month.

Spoilers: We didn’t reach the target. 

Try Again

I’m okay with failure. If you learn from your failures, then I don’t even consider them as failures. This scenario didn’t fall into this case, though. The only reasons that I could think that I didn’t reach the target are:

  1. I started after nearly a week into the month had passed, and
  2. I tried to do too much, too fast, in as little rounds as possible per day.

So, with these lessons under my belt, I decided to try again.

Smarter

I figured that it was simple enough to fix my first mistake, I’d start on the first day of the month this time.

The second mistake was something that I figured would also be simple. Rather than attempting to do as many as I could in as little rounds as possible, I’d do ten sets a day and that was it. If I focus more on the process than the goal, I figured that it would get me over the line eventually.

Challenge 01

If I do a set every half hour, I’d have the ten completed in 5 hours. I mean, hey, we’re in lockdown. I have 5 hours to spare.

But I didn’t.

Work, meetings, calls, focus and flow all sapped the time away from me.

So I tried again.

I’ve started getting up early in the mornings do to research and blog posts (like this one for example), so I’d try and get them done then.

Ten sets every 5 minutes should have me completed in just under an hour; more than enough time to spare.

Challenge 02

Pushups are hard! Even when I’m not trying to rep out as many as I can, they still take a toll on the body. Soon a five-minute break is not enough, and I’m taking longer and longer rests.

Fine, if that’s the way we’re going to do this, then I’m going to go with the flow.

Scripting

Seeing as I needed a little extra rest each round, I decided to create a PowerShell script that would help calculate that rest for me.

https://github.com/shaneis/RandomScripts/blob/master/Start-IncreasingBackoff.ps1

Using the Script

For once, I’ve added comment based help to my script so I can run

Get-Help -Name Start-IncreasingBackup -Examples

and get examples of what the script does!

Now, I can run this script and get a timer that will let me know when to start my activities and that will give me more and more rest each time!

Now to see what else I can use this for!

Uncommon SQL

Words: 612

Time to read: ~ 3 minutes

Intro

Recently the DBA Team Lead and I were reviewing some SQL code, and we came across some SQL that neither of us had frequently encountered before. This led to a brief watercooler moment where we shared some uncommon SQL that we had seen. Perfect blog post material, I think.

ODBC Date Functions

From a previous post, I talked about ODBC date functions.

I’m using AdventureWorks2014 here.

/* The 10 employees who have been the longest at the company */
SET NOCOUNT ON;

SELECT TOP (10) WITH TIES
        HE.JobTitle,
        HE.HireDate,
        {d '2006-06-30'} AS start_of_company,
        DATEDIFF(DAY, {d '2006-06-30'}, HE.HireDate) AS days_since_company_start
FROM    HumanResources.Employee AS HE
ORDER BY    days_since_company_start;

INSERT Alias

An unexpected item that we found recently was that INSERT INTO statements care about correct column names. That’s all though, nothing else seems to faze them.
This means that you can add the most ridiculous aliases or part names to the column and SQL Server won’t care. As far as I can tell, it will just ignore them.

/* Prefixes get prefixed */
SET NOCOUNT ON;

IF OBJECT_ID(N'dbo.Hires', N'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.Hires;
END;

CREATE TABLE dbo.Hires (
    hire_id int IDENTITY(1, 1) NOT NULL
        CONSTRAINT [PK dbo.Hires hire_id] PRIMARY KEY CLUSTERED,
    job_title varchar(50) NOT NULL,
    hire_date datetime2(7) NOT NULL,
    is_on_salary bit NULL
        CONSTRAINT [DF dbo.Hires is_on_salary] DEFAULT (0)
);

TRUNCATE TABLE dbo.Hires;

WITH OldestHires AS (
SELECT TOP (10) WITH TIES
        HE.JobTitle AS job_title,
        HE.HireDate AS hire_date,
        ROW_NUMBER() OVER (ORDER BY HE.HireDate) AS rn
FROM    HumanResources.Employee AS HE
ORDER BY    HE.HireDate
)
INSERT INTO dbo.Hires (
    [0].[1].[2].[3].[4].[5].[6].[7].[8].[9].job_title,
    a.b.c.d.e.f.g.h.i.j.k.l.m.n.o.p.q.r.s.t.u.v.w.x.y.z.hire_date,
    [1/0].[%].[OUT_OF_BOUNDS].[   ].is_on_salary
)
SELECT  OH.job_title,
        OH.hire_date,
        CASE 
            WHEN OH.rn % 3 = 0 THEN NULL
            ELSE 1
        END AS is_on_salary
FROM    OldestHires AS OH;

SELECT  *
FROM    dbo.Hires;
GO

Default Option

Let’s contrive an example. Let us say that we have a table called dbo.Hires and we’ve added a column called is_on_salary.
Since most of the hires are salaried, we have added a new default constraint setting the value to 0.
Unfortunately, it looks like the default constraint hasn’t been applied yet…

/* Our dbo.Hires table */
SET NOCOUNT ON;

SELECT  *
FROM    dbo.Hires;

= DEFAULT

Recently, my DBA Team Lead pointed me to a piece of code where the syntax was: UPDATE T SET COLUMN = DEFAULT

Now, I had never seen this before, and I wasn’t quite sure that this method would work. I wasn’t wholly surprised, though when a quick test proved that it does.

/* UPDATE DEFAULT */
SET NOCOUNT ON;

UPDATE  dbo.Hires
SET     is_on_salary = DEFAULT
WHERE   is_on_salary IS NULL;

SELECT  *
FROM    dbo.Hires;

What about with no default?

Okay, that seems to add the default constraint to a column. What about when there is no defined constraint on the column. Will it error out then?

/* Removing our default constraint */
ALTER TABLE dbo.Hires
    DROP CONSTRAINT [DF dbo.Hires is_on_salary]

SELECT  'Pre update' AS [status],
        *
FROM    dbo.Hires;

UPDATE  dbo.Hires
SET     is_on_salary = DEFAULT
WHERE   is_on_salary = 0;

SELECT  'Post update' AS [status],
        *
FROM    dbo.Hires;

Nope! As mentioned in the docs – if there is no default, and the column can become NULL, then NULL will be inserted.

CURRENT

Finally, we have CURRENT.
While the vast majority of scripts manually define the database context for commands, such as ALTER DATABASE AdventureWorks, etc., you can tell SQL Server:
Hey! Use the current database context!

/* CURRENT Database Context */
SET NOCOUNT ON;

ALTER DATABASE AdventureWorks2014 SET PAGE_VERIFY NONE;

SELECT 'Pre change' AS [status], [name], page_verify_option_desc FROM [sys].[databases] WHERE [name] = N'AdventureWorks2014';

ALTER DATABASE CURRENT SET PAGE_VERIFY CHECKSUM;

SELECT 'Post change' AS [status], [name], page_verify_option_desc FROM [sys].[databases] WHERE [name] = N'AdventureWorks2014';

And so forth

Thre’s probably a lot more but these are the ones that we talked about. If you have any uncommon SQL, let me know!

Using PowerShell for Lazy Maths

Words: 317
Time to Read: ~ 2 minutes

Pre-Coffee Question

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

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

2 ? 2 ? 2 ? 2 ? 2

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

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

So I got lazy.

I know PowerShell.

I know that I can create the formula in PowerShell.

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

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

The Script

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

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

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

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

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

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

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

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

On to my Actual Job

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

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

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

Pluralsight’s VS Code Extension

Words: 276

Time to read: ~ 1 minute

I’m a fan

I’m open about this – I like Pluralsight.

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

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

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

The Pluralsight extension page in VS Code

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

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

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

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

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

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

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

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

Insert Dad joke response here

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

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

… I never said I wrote good SQL…

Talking Data Bits

Words: 160

Time to read: ~ 1 minute.

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

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

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

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

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

You can check out the episode with Andy here.

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

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

T-SQL Tuesday #122 – Imposter Syndrome

Imposter syndrome.

If there was ever a phrase that brings about the dreaded Writer’s Block it would be Imposter Syndrome. I’m going to fall back to what I know I can talk about while the back part of my brain ruminates on what to write.

T-SQL Tuesday

T-SQL Tuesday is the brainchild of Adam Machanic ( Twitter | Blog ). It is a monthly blogging call by the Data community. Each month a topic for the community is offered up and a blog post is written by all wishing to take part.

This month we have John Shaulis ( Twitter | Blog ) as the host and John has given the topic of Imposter Syndrome.

I want to read your stories about when you’ve experienced, seen, or overcome imposter syndrome!

John Shaulis

You can find the invitation to this month’s topic at John’s blog post here.

Imposter Syndrome

I’ve gone through 2 cups of coffee staring at a blank page while waiting for my brain to dredge up some relatable Imposter Syndrome moment. It’s taken this long for me to realise that it’s a futile exercise. Imposter Syndrome is, by the definition that John has kindly given us, a personal experience. 

One’s success […] result of one’s […] efforts or skills.

Imposter Syndrome Definition

So, with a third cup of coffee in hand, and a deep refusal to believe that I have a caffeine dependency, allow me to tell you about the times that I feel the Imposter Syndrome the most.

Unsurprisingly

Normal working days are not the days that I feel Imposter Syndrome.

Fielding questions at work doesn’t bother me.

There’s a sense of investigation, an aim to teach, and a feeling of joy that people are taking an active interest in SQL Server.

Investigating issues and researching requests don’t trigger it either.

There are set goals here; either fix the issue or can the request be done? Plus, there is an amazing community out there that can be called upon on Twitter, Slack, etc. for answers or guidance.

Unsurprisingly the days when I am the sole person interested in SQL Server are not the days when I feel Imposter Syndrome.

Surrounded but Isolated

Imposter Syndrome hits like a brick when I’m not alone. Conferences, User Groups, and meetups are the main culprits.

Probably stemming from being a Junior DBA going to my first User Group and realising the extent of the topics I didn’t even know that I didn’t know!

Trying to learn the basics of administrating databases and going to a meeting where people are arguing about single-thread CPU performance and NUMA nodes…

Talking to people who are dealing with columnstore partitioning problems and massive memory grants because they’re bulk-loading 3 trillion rows at once.

All the while I’m there thinking my main problem was telling developers “set-based = good, row-by-row = bad”.

The same is true for conferences. 

Can’t Stop, Won’t Stop

Please don’t get me wrong. I love going to conferences, I do! 

There are few other places where you can get curated knowledge to that extent! Where you can talk to people who get it when you say “Can you believe that they wanted sysadmin privileges just to take backups!?!”.

I love seeing what people are doing, what new technologies are coming up, and catching up with people who experience the same problems that I do.

And there’s no way I’m going to stop, the “fors” don’t just outweigh the “againsts”, they completely smother them!

But it fires off my Imposter Syndrome like nothing else does!

T-SQL Tuesday #121: Gifts received for this year

Words: 401

Time to read: ~ 2 minutes.

T-SQL Tuesday, the monthly blogging party created by Adam Machanic, is still going strong. This months T-SQL Tuesday is hosted by Malathi Mahadevan ( blog | twitter ) who asks us…

“[…] what are a few things would you consider as gifts, and why?”

Invitation Post

There are many items that I consider gifts. More than I can reasonably write down in a blog post. So, in the interest of brevity, I’m going to break it down into gifts of SQL past, the gifts of SQL present, and the gifts of SQL future.

Gifts of SQL Past

I have been gifted with being mentored by DBAs that were knowledgeable and passionate about the Data Community.

I have been gifted with mentors who encouraged me to go to User Groups, volunteer at conferences and soak up as much data learnings as I could.

I have been gifted with mistakes that I have made in the past that have allowed me to learn from them. (I have definitely been gifted with the knowledge of how important test labs are!)

Gifts of SQL Present

I am gifted with a job that seems to actively repel me away from any sort of comfort zone. 

I am gifted with an active Data Community on Twitter, Slack, and in person for any issues that I run into.

I am gifted with a PowerShell community on Discord and Slack who seem to be constantly battling for the right to call themselves the “most welcoming community”.

I am gifted with a shrinking of borders so that anyone anywhere can be considered a helpful resource to a data question.

Gifts of SQL Future

I’m being gifted with new technologies to help out with old and new problems.

I’m being gifted with a field that is ever-changing. Fluid, dynamic, and will never become stagnant nor boring.

I’m being gifted with putting names to welcome faces and meeting up with new Data Community friends.

Mostly though, I’m being gifted with the opportunity to help others out in the future. Whether that be with blog posts, tweets, talks, examples, or just encouragement.

As good as all the previous gifts are, I believe the best gift is the gifts that you can give back. So, here’s hoping that the next year sees us all help more than hinder, learn more than laze, and teach more than troll.

Those are what I consider gifts.