A Fine Slice Of SQL Server
Vitaly Bruk won’t be late to meetings anymore:
Today, I have late for the team meeting … š
When I joined a Teams meeting, the team discussed ANSI_PADDING. One of the teammates fires at me with the teacher’s voice: “What are we talking about? What is ANSI_PADDING? Shoot!”.
That was a joke, but … just in case, let’s see what is this.
Read on to learn what the ANSI_PADDING attribute is and why it’s relevant.
Aaron Bertrand writes a query, or maybe two:
At Stack Overflow, our environment has multiple implementations of a largely ā but not 100% ā identical schema. (By āschema,ā I mostly mean the set of tables within a database.) I sometimes need to retrieve or update data across a large number of sites. Letās say, pulling or removing information from theĀ
UsersĀ table, and related tables, across all of our databases.
Most of the time, we think of “schema drift” as “different from a Platonic ideal of our schema” and try to correct it. In this case, Aaron shows how we can still write queries while embracing schema drift as a basic fact.
Comments closedKevin Wilkie counts the months before his release:
Figuring out the number of years or months between 2 dates shouldnāt be hard. For some reason though, for a lot of database systems it is.
Kevin gives us answers in SQL Server and Snowflake, including a bonus bit of code to correct a problem that he spotted in the first bit of code. Date math is hard.
Comments closedErik Darling has another update:
In this post, Iām going to talk about a couple cool changes to sp_HumanEventsBlockViewer, a procedure I wrote to analyze the blocked process report via Extended Events, and wish I had given a snazzier name to.
You see, when I wrote it, I pictured it as a utility script for sp_HumanEvents, which will set up the blocked process report and an extended event. But it turns out I use it a lot more on its own.
Read on for Erik’s update, including a neat trick around using an aggregate within a window function to generate ordering.
Comments closedSQL Server has had the fn_xe_file_target_read_file function for a while, but starting with SQL Server 2017, a column called timestamp_utc was added to the output.
Somewhat generally, it would be easier to filter event data out using this columnā¦ if it worked correctly. The alternative is to interrogate the underlying extended event XML timestamp data.
Thatāsā¦ not fun.
Erik shows us the problem and also provides a workaround, as well as the Microsoft Feedback issue you can vote on to get this done sooner.
Comments closedHugo Kornelis talks trade-offs:
But I wanted to contribute anyway. So here is a recent example of code that probably would have made me feel a way if I had been the type of person that gets emotional over code. Or put differently, here is the story of how I gained performance by reducing readability and maintainability.
For the record, and to prevent confusion, I am not going to name actual customers, nor name the ERP system used, and the description I give is highly abstracted away from the original problem, and heavily simplified as well. I describe the basis of what the issue was with the code I encountered and how I fixed it, but without revealing any protected information.
My internal motto is:
The first time I saw FOR XML PATH being used to generate a comma-delimited list, I think I stared at it, shook my head to clear the cobwebs, stared at it some more, and then closed the code editor thinking it was complete witchcraft.
And that same thing probably happened the next several times, too.
But eventually, I took a deep breath and read the code more closely to understand what it was doing.
I do like to joke that this is cryptic code that gets handed down from generation to generation, with each generation saying “Don’t touch the code, for you do not understand it.” But as of SQL Server 2017, you don’t need to do this anymore and can use STRING_AGG().
Steve Jones talks tally tables:
That being said, years ago I got an article fromĀ Jeff Moden on the tally table. I hadnāt used this, and was fascinated. I know Itzik had written about numbers tables early on, but it hadnāt caught my attention. However, in a follow-up, Jeff wrote about aĀ splitter function, which would use the tally table to split strings efficiently. This is the function (credit to Jeff in his article):
Click through for that function. The CLR-based tally table function is still faster, but if you can’t have CLR in your environment, and you split strings so frequently that you need a splitter, and STRING_SPLIT() just doesn’t do it for you (or you’re on an older version of SQL Server), this is a good solution. It’s also the foundation for a principle I have with T-SQL query tuning: sometimes you need to think in terms of pivoted or unpivoted data and operate on it versus the raw data. Here, we’re unpivoting a string into one row per character to get what we need out of it much more efficiently than if we tried to work the string by itself.
Erik Darling isn’t satisfied with “good enough”:
Query tuning is hard work, especially when queries run for a long time and you have to test various changes trying to get it down to a reasonable runtime.
There are, of course, things you pick up intuitively over time, and figuring out where problems in actual execution plans has gotten easier with operator runtimes.
Beyond basic query tuning intuition comes the really creative stuff. The stuff youāre amazed anyone ever thought of.
Click through for two really interesting examples.
Comments closedGreg Larsen builds a subquery:
Did you know you can include a
SELECTstatement within anotherSELECTstatement? When aSELECTstatement is embedded within another statement it is known as a subquery. There are two types of subqueries: basic subquery and correlated subquery.In this article I will be discussing both types of subqueries and will be providing examples of how to use a subquery in different places within in a
SELECTstatement.
Greg has a good write-up on the topic of subqueries and does well to separate correlated from non-correlated subqueries.. And if you want to learn more about those, as well as common table expressions, I put out a video on the topic just last week.
Comments closed