Press "Enter" to skip to content

Author: Kevin Feasel

Wildcards and Data Type Precedence

Daniel Hutmacher has fun with implicit conversion:

Implicit conversions in SQL Server follow a specific, predictable order, called data type precedence. This means that if you compare or add/concatenate two values, a and b, with different data types, you can predict which one will be implicitly converted to the data type of the other one in order to be able to complete the operation.

I stumbled on an interesting exception to this rule the other day.

Click through for an example of date searching with LIKE.

Comments closed

Calculating the Adaptive Join Threshold

Paul White breaks out the math books:

One thing I want you to bear in mind throughout this piece is an adaptive join always starts executing as a batch mode hash join. This is true even if the execution plan indicates the adaptive join expects to run as a row mode apply.

Like any hash join, an adaptive join reads all rows available on its build input and copies the required data into a hash table. The batch mode flavour of hash join stores these rows in an optimized format, and partitions them using one or more hash functions. Once the build input has been consumed, the hash table is fully populated and partitioned, ready for the hash join to start checking probe-side rows for matches.

This is the point where an adaptive join makes the decision to proceed with the batch mode hash join or to transition to a row mode apply. If the number of rows in the hash table is less than the threshold value, the join switches to an apply; otherwise, the join continues as a hash join by starting to read rows from the probe input.

Read the whole thing and learn more about the cutoffs for adaptive joins.

Comments closed

Ignite Announcements

James Serra has a round-up of Ignite 2022 announcements:

 Azure Managed Instance for Apache Cassandra: Is now GA. Cassandra is an open source, column family store NoSQL database. The Azure Cassandra service includes an automatic synchronization feature that can sync data between with customers’ own Cassandra instances, on-premises and elsewhere. More info

Wolfgang Strasser has some thoughts as well on what Ignite has shown us so far:

As you might have noticed, Azure Purview is one of my newest friends in Azure Data town. During Ignite, the support for Amazon RDS (Relational Database Service), the Data Lake Data Asset Access Governance, and Microsoft Defender for Cloud Integration with Azure Purview was announced.

What I really look forward to test is the Data Asset Access Governance for Data Lake storages. Imagine a world that allows you to define permission on a central place and those permissions are brought to a storage account / system of your choice..

Read both of them for two different perspectives.

Comments closed

Combining Hazelcast and Kibana

Nicolas Fraenkel shows off data from Hazelcast in Kibana:

Hazelcast data pipelines work by regularly polling the source. With an HTTP endpoint, that’s straightforward, but with SSE, not so much as SSE relies on subscription. Hence, we need to implement a custom Source and design it around an internal queue to store the changes as they arrive, while polling will dequeue and send them further down the pipeline.

Read on for code and explanation.

Comments closed

Starting a Synapse Proof of Concept

Hope Foley shares a secret with us:

I love my job!  One of the things I do for a living is to help customers get started with new services in Azure to finagle their data.  Many times we’ll start with a small POC to just start to understand the parts and pieces, and I teach them along the way.  I work with a lot of customers so being quick and nimble helps.  Lately I’ve been using PowerShell to setup the pieces needed for a full Synapse Analytics environment, including an example set of 4 pipelines (2 to extract to ADLS, 2 to upload to dedicated SQL pool).  Pulling data out of large relational databases into the data lake became a request I heard over and over so I automated it.  I’ve added and tweaked this over the years into a project I called “Synapse Load” and put a version out in my github. 

Click through to see what this includes and how you can use it.

Comments closed

Azure Synapse Analytics Announcements

Kaiser Larsen has some Azure Synapse Analytics announcements for us:

As businesses worldwide navigate a new normal, data teams find themselves pressured to deliver transformative insights quicker than ever. Customer interactions are increasingly digital and multi-channel, supply chains are constantly adapting to changing demand, and operations are being reconfigured to accommodate remote and hybrid work. Business agility has never been more critical. And data teams are being asked to create new solutions, accelerate project deployments, and deliver real-time insights to power that agility.

For Ignite 2021, we’ve focused on delivering new features that enable data teams to deliver insights to the business faster than ever. Here is the summary of the latest innovations on Azure Synapse.

Read on to see some of what they’ve just dropped in.

Comments closed

Backing Up a Power BI Premium Database

Gilbert Quevauvilliers wants you to back that thing up:

Continuing with my series of using Power BI Premium Per User (PPU), today I am going to show you how to back up your PPU database.

As far as I am aware all the options below will work for Power BI Premium as well.

To me this is critical when my dataset size grows. Especially when it takes multiple days to process all the data into the required partitions.

Not only is having a backup best practice, if something must go wrong with a deployment (let’s say I wipe out the partitions by mistake) it will be quick and easy to restore from a backup.

Read the whole thing.

Comments closed

SQL Assessment for SQL Server on VMs

Ebru Ersan announces a new preview:

Wouldn’t it be great if there was a way to learn if your SQL Server on Azure Virtual Machines was configured optimally? Do you have the right options set? Do you have your tempdb on the right disk? Can your queries perform better? All these and more can be answered using the new Azure portal experience on the SQL virtual machine resource page. SQL Assessment feature, once enabled, will evaluate your SQL Server on Azure VM against configuration best practices to determine if your system is healthy and setup for success. This feature is currently in preview. We would love to hear your feedback.

Click through to see it in action.

Comments closed

SQL Server 2022 Preview Announced

Peter Carlin ends our long national nightmare:

SQL Server 2022 integrates with Azure Synapse Link and Azure Purview to enable customers to drive deeper insights, predictions, and governance from their data at scale. Cloud integration is enhanced with disaster recovery (DR) to Azure SQL Managed Instance, along with no-ETL (extract, transform, and load) connections to cloud analytics, which allow database administrators to manage their data estates with greater flexibility and minimal impact to the end-user. Performance and scalability are automatically enhanced via built-in query intelligence. There is choice and flexibility across languages and platforms, including Linux, Windows, and Kubernetes.

Click through for a quick overview of what’s making its way into the product.

2 Comments