Press "Enter" to skip to content

Curated SQL Posts

Running Jobs in Azure SQL Database

Etienne Lopes gets a job:

In SQL Server, many automated tasks are implemented via SQL Server Agent jobs. If you’re used to it, you may get a bit sad when you learn that SQL Server Agent is not present in Azure SQL Database. However if those automated tasks rely on T-SQL then they can still be easily implemented in Azure SQL Databases using elastic jobs that provide the ability to run T-SQL scripts on a schedule or on-demand

Read on to see how they work.

Comments closed

MySQL Backups via mysqldump

Lukas Vileikis begins a series on MySQL backups with the classic option:

mysqldump is one of the most popular database backup tools in the MySQL world.

The tool is prevalent partly because it’s very basic and quite powerful – mysqldump database backup tool is command line-based, very simple and very straightforward to use. As far as MySQL or its flavors (MariaDB and Percona Server) are concerned, this command line-based tool is one of the top choices for junior and senior database engineers across the world alike.

Click through to see how it works.

Comments closed

Date Ranges and Merge Interval

Daniel Hutmacher notes a performance killer:

In my last post, I found that DATEDIFF, DATEADD and the other date functions in SQL Server are not as datatype agnostic as the documentation would have you believe. Those functions would perform an implicit datatype conversion to either datetimeoffset or datetime (!), which would noticeably affect the CPU time of a query.

Well, today I was building a query on an indexed date range, and the execution plan contained a Merge Interval operator. Turns out, this operator brings a few unexpected surprises to your query performance. The good news is, it’s a relatively simple fix.

Click through for an example and some information on a fix. Hugo Kornelis also adds some good insights in the comments.

Comments closed

Code is for Humans

Shane O’Neill covers an important topic:

There are a myriad number of uses for Dynamic SQL – I’ve already read some of the published posts and I’m impressed with the range. (Yeah, I’m writing this late; I’m aware).

I’m aiming for something different. I want to talk about the things I believe Dynamic SQL should have. You can disagree with me if you’d like; I’d welcome it (as long as you can justify it) but here are my thoughts on writing Dynamic SQL.

Improving the readability of code is probably the most important important things we could do at the margin. There are certainly trade-offs here: some patterns of code can be significantly more efficient at the cost of a minor readability loss and, if you need that to reach some expected performance threshold, fine. But ceteris paribus, the more human-readable code is the better code.

Comments closed

Monitoring Azure SQL Backup History

Mustafa Ashour wants you to check your backups:

Database backups are an essential part of any business continuity and disaster recovery strategy, because they help protect your data from corruption or deletion. These backups enable database restore to a point in time within the configured retention period. By default, Azure SQL Database & Azure SQL Managed Instance stores data in geo-redundant storage blobs that are replicated to a paired region. Geo-redundancy helps protect against outages that affect backup storage in the primary region. It also allows you to restore your databases/instance to a different region in the event of a regional outage/disaster.

Read on to learn more about how Azure SQL DB and Azure SQL Managed Instance perform backups, their cadence, and how you can find information on backup history.

Comments closed

Another Problem with Nullable Columns

Erik Darling tells the unpleasant truth about NULL:

Table definitions have a similar effect on developers. In today’s post, I’m going to use temp tables as an example, but the same thing can happen with regular tables, too.

The issue isn’t with NULL values themselves, of course. The table definition  we’re going to use will allow NULLs, but no NULLs will be present in the data.

The issue is with how you query NULLable columns, even when no NULLs are present.

As a card-carrying member of the League of No-Null Workers, I am happy to tell you all about how much I hate NULL in databases. And this isn’t even the big reason.

Comments closed

Dataset Changes while Deploying in Power BI

Marc Lelijveld investigates a what-if scenario:

One of the topics discussed during the session, is the effect of deployments on datasets by using native deployment pipelines in the Power BI service. Deployment Pipelines only deploy meta data from the data model, however specific changes might have an unwanted effect on the data in the dataset in the target stage.

In this blog post, I will further elaborate on several specific use cases and the effect on your dataset in the target stage.

Read on for the results of three separate tests.

Comments closed

Finding DAX Dependencies

Chris Webb tries out a DMV:

If you’re monitoring activity in Power BI, for example using Log Analytics, you’ll know that you can capture the DAX queries generated by your published Power BI reports. How do you make sense of them though? You may want to know which tables, columns or measures are being used by a query – maybe so you can work out which ones aren’t being used and can be deleted. I always thought the only way to do this would be to parse the DAX query, which would be incredibly difficult to do. Yesterday, though, Igor Cotruta pointed out on Twitter that there’s a really easy way to do this that I didn’t know about, using the DISCOVER_CALC_DEPENDENCY DMV. I’ve used this DMV before and blogged about it here, but what was new to me is that you can pass a DAX query into the QUERY restriction (it is all documented here). To see how it works let’s look at a simple example.

This looks pretty cool.

Comments closed