Press "Enter" to skip to content

Curated SQL Posts

Replacing Common Table Expressions in ADF Dataflows

Jeet Kainth needs an alternative:

At the time of writing, it is not possible to write a query using a CTE in the source of a dataflow. However, there are a few options to deal with this limitation:

– re-write the query using subqueries instead of CTEs

– use a stored procedure that contains the query and reference the stored proc in the source of the dataflow

– write the query as a view and reference the view in the source of the dataflow (this is my preferred method and the one I will demo here)

Jeet focuses on the third alternative. I’d lean toward the second or the third alternative, myself. Probably the second one (stored procedures) but both allow me to create an interface between ADF and the database. That way, underlying table changes will be less likely to require me to make code changes in ADF.

Comments closed

Reviewing a SQL Server Backup File

Andy Yun digs into a SQL Server backup file:

This first came up during my onboarding with Pure Storage. Anthony Nocentino (b|t) taught me that a SQL Server backup file is a byte-for-byte copy of your data, as materialized in SQL Server MDF files (assuming no backup compression or backup encryption). And that would make sense – how else would SQL Server store a copy of your data in a backup file? It does not make sense for SQL Server to alter your data when it writes it down to a backup file (again, with NO backup compression/encryption) – that’s a waste of compute and effort.

Well, I had a conversation with someone who was unclear about that assertion. I tried some Google-fu to present some supporting materials, but could not actually find any documentation, official or otherwise, to back it up. So here we are.

Click through to dive into a backup file with Andy.

Comments closed

Reviewing the Gartner BI Magic Quadrant

Matt Allington takes a look:

I update this blog article regularly to keep track of how Microsoft is progressing on its strategic journey in building a world class BI platform.  Gartner released the 2022 magic quadrant for Business Intelligence in March 2022.  As expected (by me at least), Microsoft is continuing its trail blazing and has maintained a clear lead over the other contenders in both ability to execute and completeness of vision.  I first wrote this blog article in 2019 to show the trend over time. Since then, I have been collecting the new Gartner magic quadrant images and I have combined them all (2015 – 2022) into an animated gif file (see below).   The visualisation was built using Power BI (what else) so it is very easy to see the changing nature of the competitive environment.  Note that Microsoft started behind back in 2015, and arguably hit the lead in 2018.

Also check out Matt’s video on how to translate spatial values to numbers so Power BI can plot them.

Comments closed

Importing Data into R

Sebastian Sauer shows off several ways of loading data into R:

Importing data into R can cause headaches for newbies. For some, the concept of relative and absolute paths is new. That’s why I compiled here some recommendations on how to important data into R and on how to ditch the “what’s my path” problem.

Click through for some notes. This post focused on files rather than databases, though that’s a very common way of loading data as well.

Comments closed

Negative Identity Reseeding

Andrea Allred hits identity integer absolute zero:

Recently we had a system go down because we ran out of integers. (Mental note to create an alert when we are getting near to running out of integers.) If we upped the column to a bigint we were going to have to drop all the indexes. This server doesn’t have the capacity needed to do an operation of that size and it was estimated that it would be down for 8 hours while we dropped indexes, upped the column type and added back the indexes. This was way too long as it was early in the work day (had it been evening, it would have been fine).

There was also a concern about how many stored procedures were expecting an int but would need to be modified to a bigint, along with any code. That was a big undertaking and we were in an emergency down.

This reseeding typically works well, though it’s important to know if the code will fail upon getting negative numbers.

Comments closed

Office Hours Text Version

Brent Ozar does some Q&A:

Q: WhatsUpDocs?: Hi Brent, have you ever needed to look at business documentation (check business rules/logic) when consulting or as an employee, but it was severely lacking? Recently joined a different team in work and trying to find simple answers to questions is an uphill struggle…

The vast, vast majority of companies don’t document their technology. The tech is in a constant state of flux, and it’s a miracle if the tech even works, let alone is documented accurately. If you’re the kind of person who needs accurate, up-to-date documentation on the tools you use, you’ll be happier working for very large, slow-moving companies with compliance needs. Think giant global financial corporations.

Click through for the full list.

Comments closed

Generating a Report for Known Issues in Power BI

Gilbert Quevauvilliers builds a report with daily alerts:

I was actually working on my own version of the Known Issues Power BI report, when the Power BI team released their blog post New Power BI Known Issue page | Microsoft Power BI Blog | Microsoft Power BI

In the steps below I am going to show you the report I created, and then how I configured it to alert me when there are updates to new known issues.

I also subscribed to this report on a daily basis, this allows me to quickly and easily be aware of any known issues in Power BI.

Read on to see how Gilbert did this and click through for a copy of the report for your own purposes.

Comments closed

Azure Shared Disk with Zone-Redundant Storage

Dave Bermingham runs some tests:

What makes this interesting is that you can now build shared storage based failover cluster instances that span Availability Zones (AZ).  With cluster nodes residing in different AZs, users can now qualify for the 99.99% availability SLA. Prior to support for ZRS, Azure Shared Disks only supported Locally Redundant Storage (LRS), limiting cluster deployments to a single AZ, leaving users susceptible to outages should an AZ go offline.

There are however a few limitations to be aware of when deploying an Azure Shared Disk with ZRS.

Dave also checks to see how their performance compares to locally-redundant storage.

Comments closed