A Fine Slice Of SQL Server
Tomaz Kastrun would like to play a game:
T-SQL Code for the popular game of “Tower of Hanoi”, that can be played in Microsoft SQL Server, Azure Data Studio or any other T-SQL editor with support of query execution.
Given that this is the game you use to teach students recursion, I figured a T-SQL based solution would be interesting. Well, Tomaz has the solution and the workspace to play it yourself.
Comments closedJared Poche explains why the TOP clause is superior to using SET ROWCOUNT:
I was presenting on how to use the TOP clause to break down large operations into short, fast, bite-sized operations. The mechanics are things I learned from writing processes that do garbage collection, backfill new columns, and anonymizing PII data on existing tables. I’ve just posted the slides and example scripts here if you are interested.
ARE THEY THE SAME?
The question was whether the SET ROWCOUNT command would work just the same, and the answer is sometimes yes but largely no.
Read on to see what Jared means.
Comments closedAndy Mallon provides a public service:
The thing I hate the most about these two functions is that you need to know the right magic spells to make them work. Let’s look at
SERVERPROPERTY()first. The syntax for the function isSERVERPROPERTY( 'propertyname' ), which is easy enough syntax, but the list of values forpropertynameisn’t discoverable from SQL Server metadata, DMVs, or even IntelliSense. Instead, I need to check the docs for the list of allowable values. These property names are essentially magic words, and I need to check my spell book to make sure I get it right.Invalid values for
propertynamejust returnNULL–which is easy enough to handle, but also means your code will compile and run, but might do unintended things if you get your magic spell wrong, due to a typo.
Click through for Andy’s solution to the problem.
Comments closedItzik Ben-Gan posted an interesting T-SQL challenge on SQL performance dot com. I’m writing up my solution in my own blog post because I have a lot to say and getting code formatting right can be tricky in blog post comments. For reference, my test machine is using SQL Server 2019 CU14 with an i7-9700K CPU @ 3.60 GHz processor. The baseline cursor solution completes in 8465 ms on my machine.
Click through for a bit of formal logic and a lot of tuning.
Comments closedTom Collins has an answer for us:
Do you have a powershell script or t-sql script to find if ssl encryption is enabled on SQL Server? I don’t want to check if connections are SSL enabled but actually check the Force Encryption flag is enabled
Click through for two answers, one in T-SQL and one in Powershell.
Comments closedEitan Blumin explains what hypothetical indexes are and why they might be useful:
Using Hypothetical Indexes, you can generate an estimated execution plan for a given query, that would essentially assume the existence of a “hypothetical” index as if it actually exists as a real index. Compare that estimated execution plan to its counterpart without the hypothetical index, and you’ll be able to determine whether creating this index for real is worth the time and effort.
Hypothetical Indexes are actually nothing new in SQL Server. It existed since SQL Server version 2005. However, its use is still not widespread to this day. Most likely because it’s not very easy to use and the relevant commands are undocumented.
Click through to see how to use them and an important warning if you try it in production.
Comments closedJared Poche looks at a procedure to track query variation over time:
Since Query Store stores statistics long term, we can use it to look for trends or major changes in performance. The Query Variation component of the QDS Toolbox helps us review changes and has a lot of options to allow us to select the kinds of changes we are interested in.
So, let’s review our options and go through some examples.
Click through for some examples of how to use the query variation procedure.
Comments closedErik Darling has a recommendation:
When working with ORMs, care has to be taken to strongly type your parameters to match the data type, length, precision, and scale of the columns those parameters will be compared to. Time and time again, I see the same patterns with string parameters:
– They’re unnecessarily typed as Unicode/nvarchar
– They’re not defined with an appropriate length
– They’re used as catch-all parameters for temporal types (dates, etc.)
Spoiler: these aren’t benefits.
Comments closedTomas Zika takes us through a conundrum:
If it’s the first time you’re seeing a problem like this, it can be confusing. How can
15be less than13?My first train of thought was the same as the original poster’s. Ok, so both values are converted to
varcharaccording to data type precedence – similar to this:
It’s not due to a varchar comparison, but Tomas does have an answer for us.
Comments closedAaron Bertrand preps us for SQL Server 2022:
In an earlier tip, “Find MAX value from multiple columns in a SQL Server table,” Sergey Gigoyan showed us how to simulate GREATEST() and LEAST() functions, which are available in multiple database platforms but were – at least at the time – missing from Transact-SQL. These functions are now available in Azure SQL Database and Azure SQL Managed Instance, and will be coming in SQL Server 2022, so I thought it was a good time to revisit Sergey’s methods and compare.
Read on to see how the workaround compares.
Comments closed