Press "Enter" to skip to content

Category: Syntax

Approximate Percentiles in Azure SQL DB and MI

Published 2023-03-02 by Kevin Feasel

Balmukund Lakhani announces a feature has gone generally available:

Today, we are announcing General Availability (GA) of native implementation of APPROX_PERCENTILE in Azure SQL Database and Azure SQL Managed Instance. We announced preview of these functions in October 2022. Since then, many customers have adopted these for the applications where response time of percentile calculation was more important than the accuracy of the result.

I have and will continue to extol the virtues of these two functions wherever I go. They’re considerably better than the originals once you start getting into the hundreds of thousands or millions of rows. They’re also available in SQL Server 2022.

Comments closed

Speeding Up Queries via IF EXISTS

Published 2023-03-01 by Kevin Feasel

Chad Callihan doesn’t need to wait for the query’s end credits sequence:

When checking for the existence of a value or values in a table, typically, the whole table does not need to be read. The goal is to obtain more of a true or false answer, whether a criteria is met or not. If the criteria is met with the first few records then there’s no need to read on. Nonetheless, you may come across scripts written to unnecessarily retrieve the complete count of a value in a table. Let’s compare the performance difference between using COUNT(*) and using “IF EXISTS” when checking a table for values.

One’s going to give you a full scan and the other will give you a semi-join. Read on to see what the practical effect of this is.

Comments closed

Pivoting in Postgres with CROSSTAB

Published 2023-02-17 by Kevin Feasel

Rajendra Gupta pivots abruptly:

A pivot table is a popular tool in Microsoft Excel that shows summarized data and helps you analyze it in various ways. Pivot tables collect and organize data from different rows, columns, and tables. Pivot tables are a great way to summarise data, and a handy tool for analyzing sales revenue, products sold, sales performance, etc.

Relational database tables store data in multiple rows and columns. You can calculate data using various functions such as count, sum, and average. SQL Server provides the PIVOT and UNPIVOT functions for working with pivot tables. How do we create the pivot tables in PostgreSQL? Let’s find it out.

Read on for a demonstration.

Comments closed

The Power of Date Truncation

Published 2023-02-14 by Kevin Feasel

Magda Bronowska rounds to the nearest minute:

From MS Learn:

DATETRUNC() function returns an input date truncated to a specified datepart.

On the surface the work similarly to DATEPART(), however that function returns integer values, opposed to the dates returned by DATETRUNC() (we will see that better in the example below).

Read on for plenty of examples of this, as well as two more syntax updates in SQL Server 2022.

Comments closed

The Benefit of IS DISTINCT FROM

Published 2023-02-14 by Kevin Feasel

Rob Farley enjoys the syntax:

This month, Deepthi Goguri (@dbanuggets) asks us about our favourite new feature in SQL Server 2022 or Azure. And while there are always a few, I’m going to write about why I have a particular fondness of “IS [NOT] DISTINCT FROM“, despite the fact that it’s overly wordy and the functionality isn’t actually new at all.

People understand my point that it’s a little wordy. Typing “IS NOT DISTINCT FROM” instead of “=” doesn’t sound fun to anyone, and I think “==” or “IS” ought to be fine. The fact that the functionality isn’t new… well that statement seems to raise a few eyebrows.

Read on for Rob’s take on what IS DISTINCT FROM (and its negative cousin) actually do and what performance-killing alternative people used prior to that.

Comments closed

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