A Fine Slice Of SQL Server
Mike Robbins takes us through some relatively new functionality:
PowerShell, a powerful scripting language and automation framework, provides features that enhance script development and execution. Among these features is the
cleanblock, a lesser-known yet beneficial component in PowerShell functions. This article explores thecleanblock, its purpose, and how to use it effectively in PowerShell scripts.
Read on to learn more about the block and how it works.
Comments closedLukas Eder notes a bit of ANSI SQL:
The following aggregate function computes the number of rows per group which satifsy the
FILTERclause:
SELECTCOUNT(*) FILTER (WHEREBOOK.TITLELIKE'A%'),COUNT(*) FILTER (WHEREBOOK.TITLELIKE'B%'),...FROMBOOKThis is useful for pivot style queries, where multiple aggregate values are computed in one go. For most basic types of aggregate function, it can be emulated simply by using
CASEexpressions, because standard aggregate functions ignoreNULLvalues when aggregating.
Lukas shows how you can also implement this logic using JSON_TRANSFORM() though I think I’d just as soon stick with COUNT(CASE WHEN BOOK.TITLE LIKE 'A%' THEN 1 END) and try hard not to think about shredding JSON.
Abhiman Tiwari announces a new function and a new operator:
We are excited to announce that the UNISTR intrinsic function and ANSI SQL concatenation operator (||) are now available in public preview in Azure SQL Database. The UNISTR function allows you to escape Unicode characters, making it easier to work with international text. The ANSI SQL concatenation operator (||) provides a simple and intuitive way to combine characters or binary strings. These new features will enhance your ability to manipulate and work with text data.
Click through to learn more about both. Honestly, I’d rather stick with CONCAT() versus using || because of how CONCAT() handles NULL without me having to check every operand first.
Joe Gavin shows how to use a function:
A common task while working with data in Microsoft SQL Server is converting from one data type to another. Most often, it’s done to change the way data is presented, but sometimes it is needed to make sure the right data types are being used for comparisons, joins, or sorting.
The SQL CONVERT function, which has long been part of the SQL language, and as the name implies, can be used to convert a value of one data type into a specified data type with optional formatting attributes. CONVERT gives you the ability to format, whereas the ISO Compliant CAST function does not.
My very strong recommendation for 99% or so of the audience: use TRY_CONVERT() instead. TRY_CONVERT() came out in SQL Server 2012 (sorry for the 1% stuck pre-2012) and has the same performance profile as CONVERT(), except that, when conversion fails, TRY_CONVERT() returns NULL rather than throwing an error.
There is also a TRY_CAST() that does exactly what you think it would.
Vlad Drumea troubleshoots a common problem:
The code in this case is the GetStatsInfoForWholeDB.sql script that’s part of PSBlitz’s resources.
This script is used for, you wouldn’t believe by the name alone, getting statistics information for a specific database.Due to the fact that it might be ran on Azure or on older versions of SQL Server, as well as on databases with incremental statistics, the best option for it was to use dynamic SQL.
In this case it uses a variable
@SQLdefined as NVARCHAR(MAX) to store the query that’s built at runtime and execute it viaEXEC.
Read on for one of the most common issues you may run into around generating dynamic SQL.
Comments closedKevin Wilkie talks common table expressions:
Most of our coding these days has Common Table Expressions, also known as CTEs. If not, you’re either working on an older version of SQL Server or you haven’t been introduced to this piece of goodness.
CTEs can make reading SQL queries a lot easier if the logic is convoluted. For example, let’s use the following in a CTE.
I’ll admit that I probably over-use common table expressions, but I like them more than sub-queries—I find them easier to read, and if they’re going to perform as well (or poorly) as sub-queries, I’d might as well use the form that makes more intuitive sense to me.
Comments closedErik Darling makes a comparison:
I often see clients using forced plans or plan guides (yes, even still, to this day) to deal with various SQL Server performance problems with plans changing.
There’s usually an execution plan or two floating around that seems to be a good general idea for a given query, and a couple weird high-end and low-end outliers for very specific populations of values.
Read the whole thing, of course.
In defense of plan guides, the company I used to work for had a few—maybe three or four in total—because of really weird data skew problems on database 106 out of 700 (or so)—because there’s always one customer that makes wildly different use of the system than everyone else. And so a query that worked perfectly fine for 699 databases (or so) flops like a fish out of water for this one database with this one customer’s data in it. So the plan guide was a nicer expediency than optimizing for mediocre on all 700 (or so) databases.
1 CommentI will leave the finer points of the problems with MERGE statements to the Michaels and Aarons of the SQL Server world.
This is just a… But why? post about them, because I’m honestly a bit puzzled by this missing implementation detail.
Read on to see what has Erik wishing could be true.
Comments closedSamay Sharma and Jason Petersen have an announcement:
We are excited to launch
pg_timeseries: a PostgreSQL extension focused on creating a cohesive user experience around the creation, maintenance, and use of time-series tables. You can now use pg_timeseries to create time-series tables, configure the compression and retention of older data, monitor time-series partitions, and run complex time-series analytics functions with a user-friendly syntax. pg_timeseries is open-sourced under the PostgreSQL license and can be added to your existing PostgreSQL installation or tried as a part of the Timeseries Stack on Tembo Cloud.
Read on to learn more about how it works. The syntax and concepts do remind me a good bit of InfluxDB, as well.
Comments closedKristyna Ferris does a bit of twisting:
Picture this, your data ingestion team has created a table that has the sales for each month year split into different columns. At first glance, you may think “what’s the big deal? Should be pretty easy, right? All I need to do is unpivot these columns in Power BI and I’m good to go.” So you go that route, and the report works for one month. Next month, you get an urgent email from your stakeholders saying they can’t see this month’s numbers. That’s when you realize that this table will grow with new columns every month. That means that any report you make needs a schema refresh every single month. Unfortunately, Power BI will not grab new columns from a table once it’s published into the online service. The only way for the Power Query to pivot the new columns is for you to open the report in your desktop, go to Power Query, and refresh the preview to get all the columns in that table.
Which is quite the pain. But Kristyna has a solution using the UNPIVOT operator in T-SQL.