Press "Enter" to skip to content

Curated SQL Posts

Using a Map in shiny

Steven Sanderson plots a course:

The code is used to create a Shiny app that allows the user to search for a type of amenity (such as a pharmacy) in a particular city, state, and country, and then display the results on a map. Here is a step-by-step explanation of how the code works.

Click through for notes, the code, and an example of the app in operation.

Comments closed

Slowly-Changing Dimensions in the Serverless SQL Pool

Lilliam Leme is building a serverless warehouse:

As organizations continue to collect and store large volumes of data in their data lakes, managing this data effectively becomes increasingly important. One key aspect of this is implementing Slow Change Dimension type 2, which allows organizations to track historical data by creating multiple records for a given natural key in the dimensional tables with separate surrogate keys and/or different version numbers. In this blog post we will address the following scenario: a customer wants to implement Slow Change Dimension type 2 on top of their data lake.

For this example, we will use Serverless SQL Pool to demonstrate how this can be done. Additionally, in the next post, we will explore how the same approach can be used with Spark.

This turns out to be more work than a classic SQL Server-based solution because of the fact that the serverless SQL pool is read-only, save for CETAS statements.

Comments closed

Azure SQL Updates for May 2023

Anna Hoffman gives us the latest news:

Let’s start with Azure SQL Managed Instance, which had several general availability (GA) announcements in April. First, the GA of Link feature for Azure SQL Managed Instance for SQL Server 2016 and 2019 happened. This capability allows you to set up near real-time replication between a SQL Server and SQL MI. You can use this link for scale, migration, read-only workloads, etc. To learn more, review the announcement blog. The team also announced the GA of CETAS. This stands for Create External Table As Select, which essentially means you can create an external table while in parallel exporting the results of a SELECT statement. This has been a customer ask and you can learn how to take advantage of it here.

Read on to learn more about what’s new with the rest of the Azure SQL landscape, and some things happening in the community.

Comments closed

Adding a UTC Time Zone Indicator to a Date in SQL Server

Bill Fellows fights with the language:

It seems so easy, I was building json in SQL Server and the date format for the API specified it needed to have 3 millsecond digits and the zulu timezone signifier. Easy peasy, lemon squeezey, that is ISO8601 with time zone Z format code 127

SELECT CONVERT(char(24), GETDATE(), 127) AS waitAMinute; Running that query yields something like 2023-05-02T10:47:18.850 Almost there but where’s my Z? Hmmm, maybe it’s because I need to put this into UTC? SELECT CONVERT(char(24), GETUTCDATE(), 127) AS SwingAndAMiss;

Running that query yields something like 2023-05-02T15:47:18.850 It’s in UTC but still no timezone indicator.

Read on for several attempts and what finally did the trick.

Comments closed

CETAS in SQL Server 2022

Eric Rouach shows off a nice extension to T-SQL in SQL Server 2022:

Create External Table As Select or “CETAS” has finally become available on SQL Server with the release of the 2022 version.

After a short setup, we can create various formats files containing any query’s result set. The created file/s must be kept on an Azure storage solution i.e. Azure Blob Storage.

The process also creates an external table reflecting the updated file’s content.

We’ve been able to do this in Azure Synapse Analytics dedicated and serverless SQL pools for a while, so it’s good to be able to create an external table from a SELECT query on-premises, especially considering that it’s the only way we have left to write to external sources using PolyBase.

Comments closed

Charts and Color Over-Use

Rita Fainshtein shows examples of how over-usage of color makes charts harder to read:

Both graphs convey a message of ranking and grouping into categories.

The categories are shown in both cases in a color-coded manner instead of in a hierarchical format. As graph creators, why do we tend to create graphs with color categories?

1. The fear of being boring, one color seems uninteresting, and here we have both colors and icons. This is an “excellent” attribute for a storyteller.

2. Visually representing a group with similar characteristics makes sense.

But can such graphs tell us anything about groups? Are they easy to understand?

Let’s discuss a few aspects of those cases together:

Click through for the full story, including an alternative to using color as a way to categorize data.

Comments closed

Deploying Azure SQL Edge

Kevin Chant takes us to the edge:

Azure SQL Edge is a version of the SQL database engine that is designed to be deployed on IoT (Internet of Things) devices.

It is based on SQL Server 2019. Which means that by default all new databases are created using the SQL Server 2019 compatibility level. You can lower the compatibility level all the way down to SQL Server 2008 if required.

There was some nice functionality in Azure SQL Edge, some of which (like DATE_BUCKET() and DATETRUNC() made it into SQL Server 2022).

Comments closed

Being a Better DBA with the SPIN Model

Eitan Blumin takes us to a seminar:

The SPIN sales strategy is a selling technique that was developed by Neil Rackham in the 1980s. SPIN is an acronym that stands for SituationProblemImplication, and Need-payoff. This strategy is based on the idea that asking the right questions can help you understand your customer’s needs and provide the best solution for them.

When I first heard about the SPIN sales strategy, I was attending a lecture that was delivered to us by a sales and marketing specialist during one of our company meetings several years ago. As a DBA, I initially assumed this strategy wouldn’t be relevant to my job. But as I listened to the presenter explain the SPIN model, I began to see its potential for use in my daily work:

It’s an interesting approach and I like the way Eitan ties it back to database administration. Of course, we could tie it to application development or any of a number of other fields. I, meanwhile, use the Colombo method, in which I ask a series of seemingly-dumb questions, but just before I leave, I say “Oh, just one more question,” and hit the person with the question proving I know that person committed the crime and have enough evidence to make an arrest.

Comments closed