Press "Enter" to skip to content

Category: Syntax

The Concepts of Data Control Language in SQL

Published 2024-11-12 by Kevin Feasel

Joe Celko talks about the lesser-known language in SQL (compared to DML and DDL):

But the truth is that the most important sub-language is the one that needs fixing. You wonder why a three-legged stool works? All three legs have to be coordinated together; the same principle holds an SQL schema.

The third sub language in SQL is the DCL (data control language). This is where you get those database privileges I just mentioned. SQL classes don’t spend a lot of time on DCL for several reasons. The first of all is that you’re a mere USER and you’re probably not allowed to pass out privileges. The original ANSI/ISO security model was pretty simple. The universe was divided into USER and USER. An important concept in that you do not create a privilege, but the ADMIN grants it to a user and it is separate from the DDL.

Click through to learn more.

Comments closed

Function Volatility in PL/pgSQL

Published 2024-11-11 by Kevin Feasel

Deepak Mahto shares some advice:

The PL/pgSQL language, available as a default extension in PostgreSQL, provides powerful tools and flexibility for application developers to build complex, enterprise-scale functionality within the database. Through PL/pgSQL’s functions and procedures, developers can choose different volatility categories—IMMUTABLE, STABLE, or VOLATILE—that offer varying performance benefits, especially in terms of result caching and data state awareness. For a deeper dive, refer to the official documentation. Choosing the appropriate volatility for a function is crucial; as they say, “With great power comes great responsibility.“

Read on to learn more. It seems like it would be quite easy to mess this up.

Comments closed

The NOT IN Operator in R

Published 2024-11-05 by Kevin Feasel

Steven Sanderson does not want these things:

In R programming, data filtering and manipulation are needed skills for any developer. One of the most useful operations you’ll frequently encounter is checking whether elements are NOT present in a given set. While R doesn’t have a built-in “NOT IN” operator like SQL, we can easily create and use this functionality. This comprehensive guide will show you how to implement and use the “NOT IN” operator effectively in R.

Read on for examples of how to use %in% and its corollary ! (...) %in%.

Comments closed

How Postgres Parses Conditional Expressions in PL/pgSQL

Published 2024-11-04 by Kevin Feasel

Deepak Mahto parses a command:

At first glance, this code block seems incomplete. Notice the IF condition: it appears to be missing an additional condition after the AND operator. Logically, this should cause an exception due to the incomplete condition following AND.

  IF i = 0 AND THEN

However, during PL/pgSQL execution, the condition is evaluated without any syntax errors or warnings. This raises a critical question:

How does PostgreSQL internally process this condition?
What allows this seemingly incomplete expression to work?

Read on for those answers.

Comments closed

The Downside of UNISTR()

Published 2024-11-01 by Kevin Feasel

Solomon Rutzky shares some thoughts:

Since the new UNISTR function doesn’t provide new functionality, only convenience (“syntactic sugar” as some would say; see comment below), I would argue that it should not only use a more standard syntax, but also not waste the opportunity and provide more substantive convenience by handling several commonly used escape sequences. I suspect that the number of times people would use “\n” is several orders of magnitude more than the number of times people would inject emojis or other non-keyboard characters. Even better would be to incorporate common escape sequences into standard string parsing.

Read on for Solomon’s comment explaining why he is not a fan of UNISTR().

Comments closed

Comparing PL/SQL to PL/pgSQL

Published 2024-10-29 by Kevin Feasel

Umair Shahid switches dialects:

To address these limitations, database systems like Oracle and PostgreSQL offer procedural extensions to SQL. Oracle’s PL/SQL and PostgreSQL’s PL/pgSQL allow developers to implement more advanced logic, including loops, conditionals, error handling, and transaction control—all within the database. These procedural languages enhance SQL’s capabilities, making it possible to write complex routines that can execute closer to the data, thus improving performance and maintainability.

As an Oracle DBA transitioning to PostgreSQL, understanding the differences between PL/SQL and PL/pgSQL is critical. This article explores the nuances between the two languages, covering syntax, features, and practical migration tips, ensuring you can leverage PL/pgSQL effectively in your PostgreSQL environment.

Read on for several differences between the two languages.

Comments closed

Troubleshooting Chains of Common Table Expressions

Published 2024-10-17 by Kevin Feasel

Jared Westover calls me out:

I started using Common Table Expressions (CTEs) in Oracle before Microsoft SQL Server and found them easier to read than a derived table. Something about reading from the top down just clicked in my brain. As the years have passed, I’m less of a fan for one reason: troubleshooting a chained CTE with several links is hard—especially when it’s someone else’s code. Even the ones I wrote years ago fill me with unease when something breaks. Is there an easier way to break down each link in the chain when things go wrong?

This is, I would argue, the biggest inconvenience around using common table expressions. The fact that SQL Server cannot materialize the contents of CTEs is likely a more prevalent problem, but challenges in how you can troubleshoot the individual parts of common table expressions are real.

Comments closed

Thoughts on Combining UPDATE Operations

Published 2024-10-16 by Kevin Feasel

Brent Ozar thinks about consolidation:

I’ve got a BEGIN TRAN in there before the updates just so I can test the same queries repeatedly, and roll them back each time. The execution plan for the updates is quite nice: SQL Server divebombs into the supporting indexes:

Relatively few rows match, so our query does less than 1,000 logical reads – way less than there are pages in the table. In this case, separate UPDATE statements make sense.

Brent then continues with, but what do we do when we need to perform multiple independent scans of the same table? Read on for Brent’s answer, but definitely check out Thomas Franz’s comments, which indicate a potential complicating factor.

Comments closed

Parameterized Queries with dbatools

Published 2024-10-11 by Kevin Feasel

Thom Andrews builds a query:

Many of us are likely aware of PowerShell, even if we don’t use it too frequently, and I suspect that if you’re reading this post you’re also familar with things like sqlcmd. Hopefully, you have also heard of DbaTools, a module for PowerShell (and if you haven’t, hopefully that’s why you’re here). Today, I wanted to discuss running parametrised queries (including table type parameters) from PowerShell, which is notorious hard/impossible with sqlcmd (or invoke-SqlCmd), using the DbaTools module.

Click through for examples building up from zero parameters up to a table of parameters.

Comments closed