Press "Enter" to skip to content

Category: T-SQL Tuesday

T-SQL Tuesday 114 Roundup

Matthew McGiffen wraps up another T-SQL Tuesday:

Here’s my round-up for this month’s T-SQL Tuesday.

Thanks to everyone who contributed last week. It was great reading your posts and seeing the different ways you interpreted the puzzle theme.

We had real-life problems, we had SQL coding questions, we had puzzles, we had solutions, we had games, and we had the imaginarium.

Click through for thirteen blog posts.

Comments closed

Puzzling Through Older Problems

Kenneth Fisher shares a couple of interview puzzles:

The year is 2004. You’re taking a tech test as an interview for a SQL development job. They have a page in their application that displays up to 20 rows of information. They need a piece of code that will return the rows from a given page. Oh, and it may not always be 20 rows per page. You need to write a piece of code where they can pass in a page number and page size and get back results. So for example, if the page size is 20 and the page is 3 then you need to return back rows 41 to 60.

The answers aren’t on the page, but then again, that’s the point of a puzzle.

Comments closed

The SSMS Magic 8 Ball

Bert Wagner has fun with SSMS:

As a kid, I found Magic 8 Balls alluring. There is something appealing about a who-knows-how-many-sides die emerging from the depths of a mysterious inky blue fluid to help answers life’s most difficult questions.

I never ended up buying a magic eight ball of my own though, so today I’m going to build and animate one in SQL Server Management Studio.

Now you can finally answer those important life questions without leaving Management Studio.

Comments closed

T-SQL Tuesday 113 Roundup

Todd Kleinhans takes us through T-SQL Tuesday #113:

Wow, we had a variety of responses to the April 2019 topic of “What Do YOU Use Databases For?

I think the overall response to the question and the theme is both mixed and varied.

I have been struggling with the personal use of databases for a long time. Things I wish would have been easier but seems to just get more complicated over time. Ever heard of GDPR? Although we think we have absolute control and access to data about ourselves, we really do not. The right to be forgotten is NOT the same as having access to all of the data about ourselves in all of the systems before they disappear. Sometimes companies will delete your data about you before you ask.

Todd starts out with an essay and then moves on to the roundup.

Comments closed

T-SQL Tuesday 112 Roundup

Shane O’Neill has a roundup of T-SQL Tuesday #112:

I’m going to keep this intro short and sweet. I’d like to say it’s because I know these roundups are for acknowledging and thanking the people who have contributed, and this is the case! Mainly though it’s because I wrote this bit last and I’ve already written… checks … 2,795 words!

So thank you one and all for participating. I’ve never thought the posts would raise feelings of happiness, sadness, thoughtfulness, and appreciative-ness that this one has.

Read on for the full list and Shane’s thoughts.

Comments closed

Generating SQL With Biml

Cathrine Wilhelmsen shows us you can do a lot more with Biml than just generating SSIS packages:

This actually happened to me in a previous job. We had a fairly complex ETL solution for the most critical part of our Data Warehouse. Many SSIS packages, views, and stored procedures queried the tables that were replicas of the source system tables. One day, we found out that the source system would be deploying a new version of their database the following day. In every single table, some columns were removed, others added, and many changed data types.
Oh.
There was no way that we could manually update all our SSIS packages, views, and stored procedures in less than a day. Thousands of users depended on our solution. It was too late to pause the source system changes.
Oh.

That story ends up with a happy ending.

Comments closed

Automation With Powershell Desired State Configuration

Jess Pomfret takes us on a journey to a desired state:

PowerShell DSC is a platform to support the concept of Infrastructure as Code (IaC).  It uses declarative syntax instead of the usual imperative syntax of PowerShell.  This means that you describe your desired state rather than the specific steps needed to get there.  There are two modes for DSC, push and pull, although pull mode offers more features and scalability, we’ll look at writing our configuration and using push mode for this blog post to keep it simple.

This post covers initial installation and some of the initial configuration, so check it out if you’re new to DSC.

Comments closed

A Docker-Based Sandbox For dbatools

Chrissy LeMaire takes us through using Docker to build a playground for learning the functionality inside dbatools:

I’ve long wanted to do this to help dbatools users easily create a non-production environment to test commands and safely explore our toolset. I finally made it a priority because I needed to ensure some Availability Group commands I was creating worked on Docker, too, and having some clean images permanently available was required. Also, in general, Docker is a just a good thing to know for both automation and career opportunities

Probably a little bit better to work on cmdlets you don’t know about in a sandboxed container rather than on production. Just a little bit.

Comments closed

Invoke-DbaDiagnosticQuery In dbatools

Andre Kamman walks through a particularly useful cmdlet in the dbatools package:

My answer to that is simple, I’m a major contributor to the awesome Powershell library dbatools. What I’ve contributed to that library are commands that will help automate the running and processing of queries from the DMV library of Glenn Berry
At some point in the life of a DBA we’ve all come accross his scripts. For the longest time I would advise people to google “Glenn Berry DMV”, and it will be the top result. 
The scripts however, come in a single file per SQL Server version and you can’t run them all in one go. You would have to select a script, run it, and paste the result from Management Studio into an Excel sheet. Glenn provides an empty sheet with tabs ready to paste the various result sets in. I’ve automated this part, hope you like it!

Click through for a demonstration of this cmdlet and the useful output it generates.

Comments closed