Press "Enter" to skip to content

Category: Syntax

Grouping Sets and Groupings

Published 2022-03-30 by Kevin Feasel

Kevin Wilkie has fun with grouping sets:

Let’s look at our dbo.Person1 table that we worked with earlier. Today, I want to find a count of all of the persons in each of the following categories: ZipCode, Gender, and Email Domains. And just for fun, let’s add in there where each of those categories cross – for example, Zipcode and Gender, ZipCode and Email Domain, etc…

Most people would think all kinds of awful thoughts at this point about all of the GROUP BY statements you’ll have to write. For anyone wondering – this is one way to do it. Notice all kinds of UNION statements and I’m sure someone is wondering if that’s truly all of the combinations. And we don’t want to go into the maintenance on this if things do happen to change…

And don’t forget about the GROUPING() function:

Let’s say our business partner asks us to determine which fields are aggregated together. Since we only have 2 fields and a grand total of 15 rows, we could determine this by eye. But, like all good developers, we want to do this programmatically.

Here’s where our friend – the GROUPING() function – comes into play.

GROUPING SETS is an extremely useful operator in the ANSI SQL standard. Definitely worth learning how to use.

Comments closed

Views in MySQL

Published 2022-03-17 by Kevin Feasel

Robert Sheldon continues a series on getting started with MySQL:

Like other database management systems, MySQL lets you create views that enable users and applications to retrieve data without providing them direct access to the underlying tables. You can think of a view as a predefined query that MySQL runs when the view is invoked. MySQL stores the view definition as a database object, similar to a table object.

Read on for plenty of detail around views. Even if you know how views work in another RDBMS, there are nuances to each of them you’ll want to understand.

Comments closed

The Power of QUOTENAME

Published 2022-03-16 by Kevin Feasel

Kevin Wilkie unlocks the power of QUOTENAME():

When I first heard about QUOTENAME, I was like “This is rather useless. It just puts brackets around whatever. I can do it just as easily hard-coding the strings.”

Truly, I’m not completely wrong, but it’s a heck of a lot more fun to knock things out with the QUOTENAME function!

But there’s more that you can do with this function, as Kevin notes.

Comments closed

The Power of PIVOT and GROUPING SETS

Published 2022-03-08 by Kevin Feasel

Aaron Bertrand builds a report:

Without comprehensive reporting tools (or Excel), it can be cumbersome and frustrating to produce perfect report output from SQL Server SELECT statement or stored procedures. In modern versions, we have access to T-SQL functionality that far exceeds old-school ROLLUP and CUBE, like PIVOT, UNPIVOT, and GROUPING SETS. Let’s look at how to produce output we can easily plug into a simple front end and produce great-looking reports.

GROUPING SETS is one of my favorite under-utilized operators.

Comments closed

TRANSLATE() and REPLACE() in SQL Server

Published 2022-02-23 by Kevin Feasel

Kevin Wilkie compares a couple of functions:

There is another function within SQL Server that many people think does the same thing, but with a slight nuance.

Sometimes, you just need to change out one character with another. For example, you need to make a “(” into a “[” to make everything consistent.

I’ve probably used REPLACE() two (or three?) orders of magnitude more often than TRANSLATE() but that’s mostly because I keep forgetting what the latter does.

Comments closed

Filtered Indexes and Functions

Published 2022-02-16 by Kevin Feasel

Eitan Blumin looks at filtered indexes:

In fact, absolutely no functions of any kind can be used within the WHERE clause of a filtered index. Not even schema-bound user-defined scalar functions.

Unfortunately, as stated in the Microsoft Docs page about Filtered Indexes, the WHERE clause of a filtered index can only support simple comparison operators.

Well, it’s not entirely true, as you CAN actually use some functions, but on two conditions:

Read the whole thing. Eitan lays out one limitation of filtered indexes and provides a couple of potential workarounds.

Comments closed

Dynamic SQL No-Go

Published 2022-02-11 by Kevin Feasel

Kenneth Fisher can’t go in dynamic SQL and neither can you:

This is one of those things that when I look back on it seems really obvious. Note: If at the end of this it isn’t overly obvious to you that’s ok too. I do a lot of dynamic SQL and GO is one of my favorite commands.

Read on to understand why. I was going to “One minor clarification…” Kenneth about it being an SSMS command (implying that it’s not available elsewhere) but he successfully parried the attack en passant.

1 Comment

Working with Views in PostgreSQL

Published 2022-02-07 by Kevin Feasel

Gauri Mahajan tries out different types of views in Azure Database for PostgreSQL:

A view can be considered as a dataset that has a pre-determined schema, the data is derived based on a specific criterion and is the source from one or more underlying tables. When a view is queried for data, it, in turn, queries the underlying tables and presents the intended dataset. At times, when the volume of data is very large, a view may start impacting query performance. In those cases, data from the underlying tables with the intended logic that makes up the view is physically stored in another table. This specific construct is called a materialized view. As the data from the source table gets updated, this materialized view needs to be refreshed from time to time to keep the data update in the materialized view, unlike a regular view. Azure’s offering of PostgreSQL database is Azure Database for PostgreSQL and it offers the same features as well.

Click through for more information on creating and working with views, materialized or not. Note that SQL Server’s indexed views are not the same as materialized views here.

Comments closed

Syntax for Scripting Calculation Groups

Published 2022-02-01 by Kevin Feasel

Marco Russo and Alberto Ferrari are linguists:

When calculation groups were introduced in 2019, we did not have a way to describe them in a textual form. A calculation group was represented as a table with one visible column and one or more rows, one for each calculated item. Each calculation item could have one or two DAX expressions associated with it – one for the calculation item itself and an optional one for the format string. Describing a calculation group in an article often required the writer to include screenshots of the Tabular Editor user interface, plus comments in the sample code to explain where each DAX expression should be placed in the user interface.

From the start we proposed a syntax to describe an entire calculation group in a textual form. However, there was no tool able to convert that syntax into the actual object in the Tabular model. For this reason, in the initial version of the articles about calculation groups we used a “pseudo-syntax” and we included comments that made the code more verbose and not necessarily easier to read. However, Tabular Editor 3 introduced the full DAX script syntax for calculation groups that we had hoped would be available in 2019. We decided to adopt that syntax in our content. We use this article as a guide to introduce and explain the DAX Script syntax for calculation groups.

Go check it out.

Comments closed

Script Parsing with ScriptDOM

Published 2022-01-31 by Kevin Feasel

Mala Mahadevan continues a series on ScriptDOM:

In the last post I wrote about what ScriptDOM is and why it is useful. From this post, I will explain how it can be put to use. What it does when you pass a script to it is to parse it, check if it is free of syntax errors, and build what is called an ‘Abstract Syntax Tree’, which is a programmatic representation of the script, with nodes and branches for each code element. The rest of the usage/functionality is built around the Abstract Syntax Tree. So in this post let us look into how this is accomplished.

Read on to see what you need to do.

Comments closed