A Fine Slice Of SQL Server
Tim Mitchell hires some data detectives:
Earlier this month, I hosted the monthly T-SQL Tuesday invitation in which I asked, “What’s in your data detective toolkit?” We got some great responses which I’ll recap here, and I’ll share a few thoughts of my own at the end.
Click through this month’s responses, as well as Tim’s answer to the question.
Comments closedKristina Mishra takes us down an alley of pain:
If you’ve bought a P1 reserved capacity, you may have been told “No worries – it’s the same as an F64!” (Or really, this is probably the case for any P to F sku conversion.) Just as you suspected – that’s not entirely accurate. And if you are trying to create Fabric shortcuts on a storage account that uses a virtual network or IP filtering – it’s not going to work.
The problem seems to lie in the fact that P1 is not really an Azure resource in the same way an F sku is. So when you go to create your shortcut following all the recommend settings (more on that in a minute), you’ll wind up with some random authentication message like the one below “Unable to load. Error 403 – This request is not authorized to perform this operation”:
On the “oof” scale, this rates as “big oof.” Kristina shows some of the differences between P SKUs and F SKUs and why it matters, as well as two unpalatable solutions if you happen to be using a P SKU.
Comments closedThe ability to write an efficient query starts with a well-designed database. If the database you are working with is poorly designed, your choices are limited. Even in those scenarios, you can still enhance your query design and follow best practices.
The goal of this post is to provide simple guidelines for writing efficient queries. These guidelines aren’t advanced SQL techniques. These are just the basics that anyone can use to write fast, efficient queries. There are many functions and keywords available in TSQL, so many scenarios aren’t covered by these guidelines. But – they are a good place to start for any query.
Click through for the process.
Comments closedI haven’t had a rant post in a while. There is a saying: “Anything before the word ‘but’ is ignored”. I love Extended Events, but … reading the extended event file is so much pain.
It feels like there is a conspiracy between Microsoft and Big
PharmaSQL Monitoring because the best analytics tool available in SQL Server (and I mean Extended Events and Query Store of course) have the worst GUI and supporting tools. I’m focusing on XE in this post.
Read on for Tom’s rant. To add on to it, the feedback item Tom links to now has a new update from Dimitri Furman, indicating (based on how I’m reading it) that the fix will be in SQL vNext, not SQL Server 2022.
4 CommentsSemab Tariq does a bit of replication:
Bi-directional replication, often referred to as multi-master replication, allows data to be written and read from multiple database nodes. This means that changes made on one node are automatically replicated to the other node, and vice versa. This setup ensures that all nodes have the same data, providing high availability and fault tolerance.
This example covers replicating a single table, but the code itself was rather easy to follow. I imagine things get a bit more challenging when you add some of the more common types of complexity to this, including what happens when your app tries to insert the same record on both nodes.
Comments closedAdron Hall shares some advice on writing a tutorial (or any kind of technical documentation):
Here’s the deal, tutorial writers: lay out ALL the prerequisites clearly at the beginning. And I mean ALL of them. Don’t assume I have jq installed for your GraphQL tutorial. Don’t assume I’m running the latest version of Python (and for the love of code, specify WHICH Python – 2.x or 3.x?).
And here’s a novel idea: how about actually telling me where to find and install these prerequisites? Give me links, give me version numbers, give me command line instructions. Assume I’m starting from scratch on a fresh machine. Because guess what? Sometimes I am!
I think that the set of tips Adron provides is aspirationally solid, meaning that there may be some things you can’t actually do but you should certainly try to do all of them. And I’m certainly not perfect at this.
Comments closedSome time ago I wrote a post about how you can use the DISCOVER_CALC_DEPENDENCY DMV to get a list of all the tables, columns and relationships referenced by a DAX query. That’s really useful and indeed the new INFO.CALCDEPENDENCY DAX function allows you to do the same thing without all the complexity of calling a DMV. However this doesn’t quite give you everything you need to solve one important problem: which columns are touched when a DAX query runs? The INFO.CALCDEPENDENCY function gives you a list of all the columns used directly in a DAX query and it also gives you a list of all the columns needed by all the measures used in a DAX query. However it only gives you a list of the relationships used and these relationships also rely on columns. In this post I’ll show you how to get a list of all the columns that are touched by a DAX query.
Click through for the script.
Comments closedI’ve used it in one form or another since I first started using SQL Server some 15 years ago, and it’s almost always been stable and reliable.
Almost.
What do you do when Database Mail suddenly stops?
Read on for that answer, as well as a puzzling situation.
Comments closedAndy Leonard continues a series on Fabric Data Factory design patterns:
In an earlier post titled Fabric Data Factory Design Pattern – Basic Parent-Child, I demonstrated one way to build a basic parent-child design pattern in Fabric Data Factory by calling one pipeline (child) from another pipeline (parent). In a later earlier post titled Fabric Data Factory Design Pattern – Parent-Child with Parameters, I modified the parent and child pipelines to demonstrate passing a parameter value from a parent pipeline when calling a child pipeline that contains a parameter.
In this post, I modify a parent pipeline to explore parameterizing which child pipeline will be called by the parent pipeline. In this post, we will:
- Copy the child pipeline id
- Clone a parent pipeline
- Add and configure a pipeline variable for the child pipeline id
- Test the dynamic pipeline id
Read on to see how.
Comments closedSteven Sanderson builds a loop:
Looping through column names in R is a crucial technique for data manipulation, especially for beginners. This article will guide you through various methods to loop through column names in R, providing practical examples and insights to enhance your data analysis skills.
Read on for examples with for loops, the dynamic duo of lapply() and sapply(), and the map() function in the purrr library.