A Fine Slice Of SQL Server
Gilbert Quevauvilliers shows off the Power BI Embedded Playground:
One of the great things about Power BI is how they make things a lot easier and better to use.
I have been answering some questions in the Power BI Community and other people have been looking into using Power BI Embedded.
This led me to find out that there is a quick and easy way to test out Power BI Embedded. The best part is that I can use my own reports and do a drag and drop test!
Click through to see how.
Comments closedNick Edwards plays match-maker:
Have you ever been faced with the SSIS error “Row yielded no match during lookup”? If so, this blog is for you!
A customer of ours recently faced the same issue in one of their SSIS packages and asked us to investigate this for them. Initial investigations on their side highlighted that when they replicated the lookup component using a standard join in T-SQL (similar to the image below) it returned the expected results.
So why was SSIS reporting an error and ultimately causing the package to fail?
Read on to learn why. For bragging rights (and a demonstration of how much SSIS pain I’ve suffered through the years), I got it in one.
Comments closedKevin Chant deploys some Synapse pipelines:
In this post I want to cover how you can automate a pipeline migration to a Synapse workspace using Azure DevOps. As a follow up to a previous post I did about one way to copy an Azure Data Factory pipeline to Synapse Studio.
Because even though the post is good it deserves a follow up showing an automated way of doing it. I wanted to show that it can be done more gracefully.
And we all want to be graceful, right?
Comments closedChad Callihan puts on the lab coat and safety goggles:
Causality is defined as the relationship between cause and effect so it’s safe to say that “causality tracking” is an appropriate name.
When you have causality tracking turned on for an Extended Events session, you can follow along with how one event in SQL can lead to various other events. By following the initial attach_activity_id.guid that gets assigned, you can follow statements, waits, statistic changes, and more. It’s a great way to filter the flury of events occuring on your server and narrow your focus.
Read on to see what causality tracking does and how you can benefit from it.
Comments closedBrent Ozar explains that sometimes you’ll need to slap a DESC on your index columns:
The short answer is that if your query orders columns by a mix of ascending and descending order, back to back, then the index usually needs to match that same alternating order.
Now, for the long answer.
Do read on for the long answer, however.
Comments closedWill Girten, et al, announce Delta Sharing on Azure:
Included in this release is a new and improved API for listing all the tables under all schemas in a share. The new API supports pagination similar to other APIs.
For example, to list all the tables in the Delta share
my_share, you can simply send a GET request to the/shares/{share_name}/all-tablesendpoint on the sharing server.
Prior to that, you might want to read up on Delta Sharing.
Comments closedI have a post combining Synapse Link for Cosmos DB and the Spark to Synapse SQL Connector:
In this post, we saw how to enable Cosmos DB’s Analytical store, access data using Synapse Link for Cosmos DB, and use the Spark to Synapse SQL Connector to move that data into a dedicated SQL pool. We saw how to do this in a workspace using a managed virtual network with data exfiltration protection enabled, meaning this is the largest number of steps necessary.
Click through for product descriptions and step-by-step instructions.
Comments closedGrant Fritchey searches for included columns:
A common issue in performance is the lookup, key or RID, but the question frequently asked is, which columns are looked up? You need to know in order to try to address the issue. The answer is easy enough to find. Let’s take a quick look.
Read on for the answer, which you can find in SQL Server Management Studio. Or by shredding a bunch of XML if that’s the kind of thing you’re into.
Comments closedFirst of all, I want to thank everyone who took some time to participate in this month’s T-SQL Tuesday: Upending Preconceived Notions. There’s a fantastic line-up of contributions! I was tickled that many of the topics chosen were things I once held one viewpoint on, later learned more, and evolved that viewpoint.
Click through for the list and Andy’s thoughts on each.
Comments closedMala Mahadevan gives us an idea of what ScriptDOM is:
I’ve been meaning to get a series of blog posts started on this topic. A twitter conversation from yesterday finally pushed me to it. Last year, I was tasked with finding a linting tool for the large t-sql code base we have at work. I looked into several tools – tsqllint, Sonarqube and several others. We ran into similar issues on all of them. Someone else defining rules for us didn’t work.
One tool called it wrong to use more than 3 tables in a query – we had several with 6-7, maybe even more. Another started to point out lack of indexes on temp tables as a problem (the rule was designed for table scripts but worked the same on code). Getting new rules that we wanted – such as not having unnaming primary keys on temp tables (Querystore doesn’t like them) or having our naming standards enforced meant extra work on someone else’s code. Our rules were custom to our environment. There were generic best practices for sure, such as finding the SELECT * or NOLOCK hints, but those were a small subset of what we needed. I then started looking for a tool with which I could make a custom linter. That’s when I discovered ScriptDOM, which has been around for a really long time with few people knowing or using it. It took me some time to understand how to put this to use. But after I figured it out it was really easy. Now I have a fairly robust, custom linter in place written in PowerShell and integrated well into our Azure DevOps Build process. It is easy to use and it is owned by us.
Read on to see what ScriptDOM can do and stay tuned to learn more.
Comments closed