A Fine Slice Of SQL Server
Daniel Hutmacher gives us an eye test:
The developer wrote this pretty little query to show us which accounts are up for review (which in our case means they have a “30” flag).
SELECT account, balance, 'For review' AS [status]FROM #accounts WHERE account IN (SELECT account FROM #accountFlags WHERE flag=30) ORDER BY account;Did you spot it?
I did, but in fairness, I’ve been burned enough times by this that I check for it.
Comments closedAlways On Availability Groups can support up to nine availability replicas, and while we don’t use anywhere near that many replicas in each of our clusters, we do have 2 replicas per cluster (3 servers total), with the replicas being used as a readable secondary.
Since we use readable secondaries in our environments, the application needs to connect to both the primary and the secondary servers with the same login. The catch is, logins don’t automatically sync across replicas. If the logins don’t sync, the application won’t connect to a secondary, which results in login failures.
Read on for one way to solve the problem.
Comments closedHugo Kornelis conclues a mini-series on foreign key constraints:
Welcome to part fourteen of the plansplaining series, where I wrap up the mini-series on how simple foreign keys have huge effects on execution plans for data modifications.
We already looked at inserting data in the referencing (child) table, and at deleting data from the referenced (parent) table as well as updates in the child table. We did not and will not look at deleting from the child table or inserting in the parent table: those operations can by default never violate the foreign key constraint, so no additional logic is needed.
So that means there is only one thing left to explore: updating the parent. Perhaps surprisingly, this is actually quite complex, so it warrants an entire post of its own.
Read on to see why.
Comments closedServerless Synapse SQL pools in Azure Synapse Analytics have a new set of features that will enable you to analyze your Azure data more efficiently. The new Transact-SQL (T-SQL) language features that you can use in serverless SQL pools are STRING_AGG, OFFSET/FETCH, PIVOT/UNPIVOT, SESSION_CONTEXT, and CONTEXT_INFO.
Old T-SQL hands will likely know what all of this does, but click through if something looks unfamiliar. All of this is available in SQL Server 2017 and later (and everything but STRING_AGG() is available going back to 2008).
David Smith has a demo for us:
It’s important to note that the model prediction is not being generated by the Shiny app: rather, it’s being generated by an Azure Function running R in the cloud. That means you could integrate the model estimate into any application written in any language: a mobile app, or an IoT service, or anything that can call an HTTP endpoint. Furthermore, you don’t need to worry how many apps are running or how often estimates will be requested by the app: Azure Functions will automatically scale to meet the demand as needed.
Read the whole thing. Given that R isn’t naturally supported by Azure Functions, I think this is quite interesting.
Comments closedDrew Skwiers-Koballa gives us an update on where SQL Server tooling is at:
The December releases of Azure Data Studio 1.25 and SQL Server Management Studio (SSMS) 18.8 are now generally available. Additionally, the mssql extension for Visual Studio Code has recently been updated to version 1.10.0. Read on to learn more about each of these updates and grab the latest versions of SSMS, Azure Data Studio, or the mssql extension for VS Code.
Read on to learn more.
Comments closedNick Edwards shows off the Switch activity in Azure Data Factory:
Prior to the switch statement I could achieve this using 4 ‘IF’ activities connected to a lookup activity as shown in the snip below using my ‘Wait’ example pipeline.
However a neater solution is to use the ‘Switch’ activity to do this work instead. I’ll now jump straight into a worked example to show you how I achieved this.
Click through for the demo.
Comments closedAdam Saxton has answers to questions:
There were a lot of good questions on our Power BI Premium Per User video. We are going to look at some of the questions that were asked and get you some answers!
Click through for the video.
Comments closedThe December 2020 version of Power BI desktop has just been released, and it is undoubtably the most important release since the first version way back in 2015. The super feature that has been released is an update to composite models using direct query of online datasets. The implications of this release are massive. Anyone with Power BI Desktop can now build their own, local version of a data model and enhance it with their own additional data without the need to have edit access to original data model. Oh, and it is a Pro feature, not a Premium feature!
Read on to see this in action.
Comments closedErik Darling puts on his lab coat and goggles:
After blogging recently (maybe?) about filters, there was a Stack Exchange question about a performance issue when a variable was declared with a max type.
After looking at it for a minute, I realized that I had never actually checked to see if a recompile hint would allow the optimizer more freedom when dealing with them.
Read on for Erik’s findings.
Comments closed