Press "Enter" to skip to content

Curated SQL Posts

Double-Join Syntax in T-SQL

Greg Dodd unleashes a monster:

We now have all of the data, but the question is, how do we stop the Person’s name coming back if the HireDate is after 1 Jan 2000?

We can’t simply put it in a where clause, because then the Course Title won’t come back. We could put it as part of the join condition to Instructor, but that will only limit the hire date coming back. We could use that to build a case statement around the FirstName and LastName fields, but what other options are there?

What we want to do is limit the join to Person to only return if Instructor returns, and then we could put the join condition on Instructor.

I’ll admit that whenever I see this syntax, I tend to remove it and replace with a subquery or APPLY operation. I don’t like multi-joins at all for the reasons Greg mentions at the end: it’s uncommon and difficult for a T-SQL developer to parse mentally.

Comments closed

Live Extended Events Data with Azure SQL Database

Grant Fritchey is doing it live:

Once you’ve created an Extended Events Session that is output to Azure Storage, you’ve done most of the work. The trick is really simple. Get the Azure Storage account set up with a Container. Create a Shared Access Signature (SAS) with the right permissions (rwl, read, write, list). Get the token from the SAS (it’s a long string). Use it, along with the path to the container to create a Database Scoped Credential. Create the session using the same path and container that you defined in the Credential. Done. You’ve got an Azure Extended Events session gathering data for you and outputting to a file in Azure Storage.

Now, what I’d like to tell you is that you can open up the Live Data window from SSMS. You can’t.

Grant does give us a workaround which kind of does the trick, but this is an obvious place where some additional developer care would be valuable.

Comments closed

Writing Check Constraints in SQL Sever Data Tools

Chris Johnson has a how-to guide:

That all looks ok, and everything published fine, and kept on publishing fine until I happened to check the generated script for some of the publishes (I was in the early stages of development at this point so deployments were just being done through Visual Studio direct to my machine).

When I did I noticed that every deployment I was getting code like this:

That’s dropping all my constraints, recreating them using WITH NOCHECK, and then using WITH CHECK to check the existing data. Obviously all the data will pass those checks, but that’s going to add some time to my deployments, and as the size of the data and the number of checks both increase, these checks will take more and more time.

Read on to understand what’s happening. I’d call this a fairly silly limitation on the part of SSDT.

Comments closed

HDFS Data Encryption at Rest

Arun Kumar Natva takes us through the process of encrypting data at rest in Cloudera Data Platform:

HDFS Encryption delivers transparent end-to-end encryption of data at rest and is an integral part of HDFS. End to end encryption means that the data is only encrypted and decrypted by the client. In other words, data remains encrypted until it reaches the HDFS client.

Each HDFS file is encrypted using an encryption key. To prevent the management of these keys (which can run in the millions) from becoming a performance bottleneck, the encryption key itself is stored in the file metadata. To add another layer of security, the file encryption key is stored in encrypted form, using another “encryption zone key”.

Read on to learn more and to see how it all fits together.

Comments closed

Grafana Changing License

Alex Woodie has some bad news for us:

Grafana is switching licensing of its core products from Apache License 2.0 to the more restrictive Affero General Public License (GPL) v3. The company made the change in an attempt to balance the value of open source with Grafana’s monetization strategy, CEO Raj Dutt announced yesterday.

Grafana has been considering a license change for some time, Dutt wrote in a blog post on April 20. This week, the company finally felt the time was right to move.

“Oof” was my first response. I know that a pretty large percentage of companies won’t touch AGPL. I don’t know if we’ll see these companies adopt the commercial version of Grafana, see the companies switch over to something else, or see developers fork Grafana and come up with some other product. AGPL is not quite as scary for companies when a product is at the end of the chain, as visualization and dashboarding products tend to be, but for many companies, that doesn’t matter.

Comments closed

Read-Ahead Reads

Chad Callihan provides some helpful tips around read-ahead:

What are read-ahead reads and how do they impact SQL Server performance? Read-ahead reads allow SQL Server to think ahead to pull pages into the buffer cache before they are actually requested for a query. Up to 64 contiguous pages from a file can be read and the ability to read-ahead can be used for both data pages and index pages. Once data is in the buffer cache, it will not need to be pulled in for future queries unless it has been pushed out by other SQL Server tasks.

Click through to see what they are and how you could disable them if you really need to.

Comments closed

Service Endpoints in Azure SQL Database

Mike Wood takes us through service endpoints in Azure:

In previous installments of my “Securing Azure SQL Database” series, I covered Azure SQL Database firewall rules and private endpoints—the first of which is a way to help reduce the public exposure of your database endpoint and the second being a means to remove all public access if necessary. Each option has unique benefits, and some scenarios might call for a mix of the two options.

In this blog post, I’ll cover a third option for securing Azure SQL Database—service endpoints. This option is similar to private endpoints in that you restrict public access and only grant access to the database through your Virtual Network (VNet).

Read on to learn more.

Comments closed

T-SQL Tuesday 137 Round-Up

Steve Jones wraps up the latest T-SQL Tuesday:

I hosted the blog party this month, with the invite to write about notebooks. These are a neat technology, and I’ve written about them at SQLServerCentral.

This post is a wrap-up of the various responses to my invitation. First, quite a few people give credit to either Aaron Nelson or Rob Sewell for their writings and work with notebooks, so check out their blogs.

Click through for the list of respondents.

Comments closed

Using Calendar Tables

Aaron Bertrand has a post up on using a calendar table:

A while back, I wrote an article called Creating a date dimension or calendar table in SQL Server. I have used this pattern repeatedly and, based on the questions I get from the community, many of you are using it, too. Some of the questions I get are along the lines of “how do I actually use this table in my queries?” and “what are the performance characteristics compared to other approaches?” So, I thought I would put together a collection of use cases and analysis, starting with business day problems.

I’m a big fan of calendar tables as well. They’re quite useful for a variety of business problems and make date math problems really easy, especially when dealing with non-standard calendars (e.g., work weeks, fiscal years, figuring out what day Easter is).

Comments closed

Getting Good Feedback

Cole Nussbaumer Knaflic explains how to get feedback:

We recently kicked off a new 10-week course, which has been really fun to develop, because it’s both longer than our typical workshops and spread out over a greater amount time. Combining these aspects means that we get to cover more topics related to data storytelling and go into greater depth on each. We kicked things off with a focus on feedback, due to the important role this will play throughout the course, and the critical role it plays in our skill development and efforts to communicate effectively with data in general.

There’s some good advice in here.

Comments closed