Press "Enter" to skip to content

Category: Syntax

FILTER vs CASE in Postgres

Published 2023-02-08 by Kevin Feasel

Lukas Eder hits on an interesting data point:

I’ve found an interesting question on Twitter, recently. Is there any performance impact of using FILTER in SQL (PostgreSQL, specifically), or is it just syntax sugar for a CASE expression in an aggregate function?

Click through for the answer and your daily reminder that SQL variants aren’t pure fourth generation languages—if they were, the optimizer would take all possible constructs of a given desired operation and generate the same execution plan for all of them.

Comments closed

Splitting Strings to a Table in Snowflake

Published 2023-02-08 by Kevin Feasel

Kevin Wilkie puts on the flannel and grabs his database lumberjack axe to split some strings:

But, sometimes, you want a small list of data inherent to a query in SnowFlake. And that’s what I want to talk about today.

In SQL Server, you would create a temp table and then insert the data into it. But in Snowflake, there may be a better / easier way.

Let’s use the function SPLIT_TO_TABLE. Shockingly, it does what’s on the label – it split data and puts it into a table.

Click through for an example. Also check out the Snowflake documentation, where they make use of the lateral operator (the ANSI version of APPLY()) to generate results for multiple strings and make use of the SEQ column.

Comments closed

Simplified Syntax for Scala 3

Published 2023-02-01 by Kevin Feasel

Anshika Agrawal shows a few examples of how Scala 3 is a bit easier to pick up than Scala 2:

Scala 3 is a remodel/refit for the scala language. It attracts developers due to its improved features such as simpler syntax, better type inference, improved error messages, and enhanced support for functional programming. In this article, we will compare the syntactical enhancement of Scala 2 & Scala 3. How indentation will help developers to write code efficiently and effortlessly.

Click through for some examples. On the whole, these are small but welcome changes in eliminating unnecessary code cruft.

Comments closed

SOUNDEX in Snowflake

Published 2023-02-01 by Kevin Feasel

Kevin Wilkie makes a noise:

In Snowflake, there is another function that is SOUNDEX-like that can give slightly different results – SOUNDEX_P123.

For those cases where the first and second letters of the string have the same SOUNDEX number, the SOUNDEX_P123 function will keep the number for the second letter. This variant is used in a few other database systems, for example, Teradata.

Click through for a demonstration of the two SOUNDEX() variants and how results can differ.

Comments closed

Sessions and Execution of Dynamic SQL

Published 2023-02-01 by Kevin Feasel

Deborah Melkin riddles us this on dynamic SQL:

I admit it – I do waaayyyy too much with dynamic SQL. But I just keep running into situations that require it. That being said, I ran into an interesting problem that had me puzzled. I found a bunch of different blog posts that pointed to me to the right direction but required a little extra work to find the solution.

There are several concepts that are at play here, so I’ll try to break this out so we can put the pieces together. The first once is centered around dynamic SQL. There are two parts of this I want to make sure we understand first – how it fits into sessions and how it gets executed.

Read the whole thing.

Comments closed

Max and Min Functions in KQL

Published 2023-01-31 by Kevin Feasel

Robert Cain goes extreme:

The max and min aggregation functions are common to almost every language, and the Kusto Query Language is no exception. As you would think, when you pipe in a dataset max returns the maximum value for the column name you pass in. Likewise min returns the lowest value.

In addition, there are variants for each, maxif and minif. We’ll see examples for all of these in this post.

Click through for a few functions you can call via the summarize operator.

Comments closed

External Tables and the Serverless SQL Pool

Published 2023-01-27 by Kevin Feasel

Ryan Adams continues a series on querying the serverless SQL pool in Azure Synapse Analytics:

There are two ways to read data inside Data Lake using the Synapse Serverless engine.  In this article, we’ll look at the second method which uses an external table to query a path within the lake.

Synapse is a collection of tools with four different analytical engines (Dedicated Pool, Spark Pool, Serverless Pool, Data Explorer Pool).  This gives you a lot of options for ingesting, transforming, storing, and querying your data.  Here you will use the Synapse Serverless Pool to query the data in your ADLS account.   

Read on for a demonstration.

Comments closed

Emulating Window Functions in MySQL 5.7

Published 2023-01-20 by Kevin Feasel

Lukas Eder says, we have window functions at home:

One of MySQL 8’s biggest improvements is the support of window functions. As I always said in conferences, there’s SQL before window functions and SQL after window functions. Once you start using them, you’ll use them everywhere.

Some of you poor souls are unfortunate enough to be stuck on MySQL 5.7, either of your own choosing, or because you’re using a clone / fork that is still 5.7 compatible. While for most people, this blog post is just for your amusement, or nostalgia, for some of you this post will be quite useful.

If you are in a windowless world, read on to see how you can make life a little more manageable.

Comments closed

Performance Comparing DISTINCT to GROUP BY

Published 2023-01-20 by Kevin Feasel

Reitse Eskens does a performance comparison:

A few days ago, I heard someone stating that Group By was much quicker than Distinct. Less disk impact, less memory etc.
So, I thought I’d find out if it’s true or not because I found it interesting. I always thought there was no difference. I tested a single small table and found no difference in speed, reads or execution plan. But that’s no real world example. Usually the tables contain a lot of data and are joined to other tables.

Click through for the results of Reitse’s analysis.

Comments closed