2022-05-10 – Curated SQL

Chronological Snobbery in the Tech World

Published 2022-05-10 by Kevin Feasel

Joking aside, chronological snobbery is a bias rarely discussed in technological circles. As a result, our (me included) tendency is to judge newer technology as better technology. Newer technology often sports cool new features, but it’s not entirely accurate to claim newer is always better. Forty-seven years in this field informs me this is not always the case; there is often some baby in the bathwater of the pervious generation of technology.
It’s more accurate to claim newer technology is often better for some applications.

It’s hard to draw that line between “new technology is better” and “old technology is venerable” appropriately. A lot of this is risk appetite, as we’ve known for decades (Crossing the Chasm is 30 years old, for example): some people will gravitate toward novel technologies faster than others. It’s hard for me to describe the tangled mess of beliefs I have around the topic, so I’ll just say to read Andy’s thoughts and maybe I’ll turn it into an episode of Shop Talk or something…

1 Comment

Searching for Strings in a Database

Published 2022-05-10 by Kevin Feasel

Aaron Bertrand updates an old tip:

Back in 2015, I wrote a tip called Search all string columns in all SQL Server databases. That tip focused on finding strings within string-based columns in all tables across all user databases. I was recently asked if this could be made more flexible; for example, can it search views as well, and can it search only in a specific database?

Click through for a new version which works in SQL Server 2016 and later.

Comments closed

Stored Functions in MySQL

Published 2022-05-10 by Kevin Feasel

Robert Sheldon continues a series on MySQL:

In the previous three articles in this series, I focused on creating basic database objects that you can use to get started with MySQL. You learned how to build an initial database and then add tables, views, and stored procedures. In this article, I cover one more important type of object, the stored function, a routine that is stored in a database and can be invoked on-demand, similar to a user-defined scalar function in SQL Server or other database systems.

I’m not familiar enough with stored functions to know if they have the same performance limitations as what we have in SQL Server (specifically around needing to run everything in the function once for each row) but based on a comment at the end of Robert’s post, it does seem that way.

Comments closed

Partial Update Operations in Cosmos DB

Published 2022-05-10 by Kevin Feasel

Hasan Savran partially deflates the partial update bubble:

Partial Update was one of the most wanted features by Cosmos DB customers. In a regular update operation, you need to send the whole JSON document to Cosmos DB. This can be silly if your data model is large and you want to update one field in it. With a regular update, your request object will be large because you need to send the whole data model. Regular Update operation needs more resources from the client/SDK and network bandwidth.
You might think that partial updates might cost fewer request units. Unfortunately, this is not the case. Because Cosmos DB still needs to open the JSON document, change the necessary properties and save the data. Cosmos DB uses almost the same amount of CPU and memory for this operation for a regular update or a partial update.

That it costs just about as much as a full write does reduce the value of partial updates. Still, there is some value in reducing bandwidth requirements or making changes where you don’t know the entire contents of the document up-front.

Comments closed

Counting in KQL

Published 2022-05-10 by Kevin Feasel

Robert Cain continues a series on KQL operations:

The previous post in the series covered the take operator. In that post I mentioned that take was one of the simplest operators in KQL. But it is not the simplest, that honor goes to the count operator.
The count operator does nothing more than takes the piped in dataset and returns the number of rows in it. We’ll see more in a moment.

Click through to see more.

Comments closed

Thoughts on Technical Interview Questions

Published 2022-05-10 by Kevin Feasel

Steve Jones shares some thoughts:

Redgate had a discussion recently among our developers about our interview process and questions. There has been a standard question asking candidates about 2D arrays, but as one developer pointed out, we don’t use these in our code base. So, why do we ask candidates about this topic?
The developers came up with a different question, actually a series of questions that ask about a class and then how to test parts of this class. We mostly work in C# in a DevOps culture, so this seemed like a good idea. They proposed a scenario with a few questions and then asked current developers to solve the questions and give feedback on the language, structure, and difficulty of the problem.

Read on for Steve’s thoughts. It’s been a minute since I’ve given an interview (a plus side to having a really stable pair of database teams the past couple of years) but one of the things I enjoy doing is taking screenshots of Management Studio in various phases of work and ask “What do you see here? There are no right or wrong answers.” I say the latter because I don’t want you to enumerate through every string you see on the screen; I want you to explain what information of importance you’ve caught.

One big tip for interviewers: instead of algorithmic or gotcha questions, show actual code at the 25th, 50th, 75th, and 95th percentiles of difficulty within your code base, focusing on things a person could understand with about 20-30 lines of code and zero context. “Difficulty” can mean that this code was tough to write, is tough to maintain, or that you have included common (and sometimes uncommon) errors to an otherwise real segment of code. For database developers, that might include things like invalid NULL checks, incorrect assignments, etc. Ask the person to perform a code review and point out what they see that is interesting. That way, you get an opportunity to check their technical bona fides in a realistic but relatively low-pressure scenario by simulating the activities that a person actually would do in the job.

I have more advice but I’ll save that for another day.

Comments closed