Press "Enter" to skip to content

Category: T-SQL

Sparse Columns in SQL Server

Published 2023-05-02 by Kevin Feasel

Chad Callihan occasionally inserts something:

Have you ever maxed out the SQL Server table column limit yet still needed more columns? Hopefully not considering SQL Server has a max limit of 1024 columns per table. But as I found out, it’s possible for someone to reach out and ask for even more. Sparse columns are an option to consider when you can’t get enough. Let’s take a look at what sparse columns are and how they can be used.

Sparse columns have very little utility, except in the most “I don’t think you’re doing it right” scenarios. Still, if you happen to end up in that scenario, there is a way out, though I’d really want to understand the nature of the data in that problem and, knowing just the amount of detail in the scenario that I do, would lean toward storing the data either in an unpivoted fashion (one row per entity * attribute in an EAV-style “‘additional attributes” table) or as a JSON string and let the client sort it out.

Comments closed

Tips for Using psql

Published 2023-05-02 by Kevin Feasel

Ryan Booz shows off a tool:

Having access to the psql command-line tool is essential for any developers or DBAs that are actively working with and connecting to PostgreSQL databases. In our first article, we discussed the brief history of psql and demonstrated how to install it on your platform of choice and connect to a PostgreSQL database.

In this article we’ll get you up and running with all of the essential things you need to know to start on your journey to becoming a psql power-user. From basic command syntax to the most common (and helpful) meta-commands, it’s all covered throughout the rest of the article.

Also check out the comments for a link to a pager which works with psql.

Comments closed

Updating an Always Encrypted Column

Published 2023-04-27 by Kevin Feasel

Chad Callihan makes an update:

When recently troubleshooting an issue, I needed to update a database record to test application functionality. Because the table had an Always Encrypted column, some extra steps were needed to make the UPDATE successfully. Let’s look at the error encountered and how it was resolved.

Click through for the error and see how Chad got around the problem. This is definitely one of those head-scratcher solutions, where you can kind of understand why it’s necessary but still think the required process is dumb.

Comments closed

Getting Started with Postgres’s psql

Published 2023-04-25 by Kevin Feasel

Ryan Booz provides a primer:

PostgreSQL has a separate command-line tool that’s been available for decades and is included with any installation of PostgreSQL. Many long-term PostgreSQL users, developers, and administrators rely on psql to help them quickly connect to databases, examine the schema, and execute SQL queries.

Knowing how to install and use basic psql commands is an essential skill to have for anyone that will connect to PostgreSQL.

Most of the article covers installation and starting up, but before you can write queries, you’ve got to connect to the server and database.

Comments closed

A Use Case for Removing Schema Prefixes

Published 2023-04-21 by Kevin Feasel

Aaron Bertrand threatens to angry up the blood:

I’ve long been a huge advocate for always referencing objects with a schema prefix in SQL Server.

In spite of what may be a controversial title to many of my regular blog readers, I don’t really want you to stop that practice in most of your T-SQL code, because the schema prefix is important and useful most of the time. At Stack Overflow, though, there is a very specific pattern we use where not specifying the schema is beneficial.

Mind you, Aaron’s use case is a rather niche example, so I don’t plan on burning him in effigy. Just maybe singeing him a tiny bit.

Comments closed

Getting Last-Used Report Parameters for SSRS

Published 2023-04-14 by Kevin Feasel

Brian Bønk shares some history:

A couple of years back I needed to find the latest used parameters from a reporting services – SSRS (yes, it is old!) report. The functionality was to find the latest used parameters for all parameters dynamically and use them as the default values for each parameter in the report, when tjhe user opened the report. If the uesr had not used the reprot before, it should ask for the parameters.

This was a alot of fun for me to do, as I needed to query the internal tables from the reporting services database. The tables ExecutionLogStorage and the Catalog was on play. This due to the fact that I needed the active user’s latest used parameter values from each specific report and this in runtime when the report was opened.

Click through to see how Brian did it.

Comments closed

A Thought on Query Granularity

Published 2023-04-14 by Kevin Feasel

Chris Johnson shares some thoughts:

This query is pretty simple, we’re wanting to return a set of data about the orders placed including the total cost of each order. However the orders in the database are split into an Orders table that holds the order level information, and an OrderLines table that holds information on each product ordered in each order, and we need that OrderLines table to get the cost.

So we write our query, and we join to the OrderLines table, and we sum the cost. But then, because we have an aggregate, we need to group by every other column we’re returning. And to me that just doesn’t look right, and it doesn’t convey the intention of the query properly.

In Chris’s simple example, I’m not sure I’d push it very much, but Chris does have a good point in terms of explaining query intent. Also, depending on how many order lines there are relative to orders (the next step in the chain for that query), aggregation in a common table expression could be faster than waiting until after the join to aggregate on all of the columns. In reality, that’s the most likely reason I’d make this change, assuming that it made a big enough performance difference. But if you take a much more complicated query of this sort, then I’d be more amenable to the argument.

Comments closed

Manual Halloween Problem Protection

Published 2023-04-13 by Kevin Feasel

Jared Poche takes us through Halloween problem protection and builds out his own method, with blackjack and hookers:

Well, if SQL Server is trying to separate the read from the write, why don’t I just do that myself? I had the idea to read the data I needed in an INSERT…SELECT statement, writing into a memory-optimized table variable (motv). I could make sure the read included all the columns I need to calculate the new value, including the CASE statement I had in the SET clause of the UPDATE.

Click through for Jared’s explanation of how to implement it, as well as the circumstances in which it might be faster than what you get by letting a single T-SQL statement handle the job.

Comments closed

Finding the Max (or Min) Value across Columns

Published 2023-04-13 by Kevin Feasel

Greg Dodd tries out some new syntax:

Have you ever had 2 columns in a table that you need to select the maximum value from? Not the maximum value from each column, but the biggest value after comparing the columns?

Usually where I have this problem, I’d use a CASE statement, but there’s another way. 

I do like GREATEST() and LEAST() (or argmax/argmin if you’re used to those terms), though Greg does include how you can get the same functionality in versions prior to SQL Server 2022.

Comments closed

Building a Wordle Solver in Powershell and T-SQL

Published 2023-04-12 by Kevin Feasel

Shane O’Neill takes this T-SQL Tuesday to heart:

I got swept up in that wave for a while in the same way I got swept up in the other trends of my time, like Pokemon, Sodoku, and Angry Birds.

Eventually, I stopped when I found a PowerShell script by Kieran Walsh ( github | twitter ) where you could narrow down to the correct answer by putting in the results of your guess each round.

This hack led to me realising how much time I was spending on Wordle and that I should stop, much like I did with Pokemon, Sodoku, and Angry Birds.

So, what better thing to do than to try and recreate that PowerShell script in T-SQL

And given Shane’s time restriction, I’d say the end result is a good one.

Comments closed