98 votes
Accepted

Is there any benefit to SCHEMABINDING a function beyond Halloween Protection?

Yes. Failing to specify WITH SCHEMABINDING means SQL Server skips the detailed checks it normally makes on the function body. It simply marks the function as accessing data (as mentioned in the link ...
Paul White's user avatar
  • 84.1k
60 votes

Test if a string is a palindrome using T-SQL

If you are using SQL Server you can use the REVERSE() function to check? SELECT CASE WHEN @string = REVERSE(@String) THEN 1 ELSE 0 END AS Palindrome; Including Martin Smith's comment, if you are on ...
Shaneis's user avatar
  • 1,803
45 votes
Accepted

In PostgreSQL, is there a type-safe first() aggregate function?

DISTINCT ON() Just as a side note, this is precisely what DISTINCT ON() does (not to be confused with DISTINCT) SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of ...
Evan Carroll's user avatar
  • 63.2k
41 votes
Accepted

SQL Server 2019 executes unreachable code

This is a bug with the inlining of Scalar UDFs (or perhaps a bug with the query optimiser that is being exposed more by scalar UDF inlining). You can use WITH INLINE = OFF to turn off inlining for ...
Martin Smith's user avatar
  • 84.7k
36 votes
Accepted

Is there a way to prevent Scalar UDFs in computed columns from inhibiting parallelism?

Yes if you: are running SQL Server 2014 or later; and are able to run the query with trace flag 176 active; and the computed column is PERSISTED Specifically, at least the following versions are ...
Paul White's user avatar
  • 84.1k
31 votes
Accepted

Self referencing scalar function nesting level exceeded when adding a select

This is a bug in project normalization, exposed by using a subquery inside a case expression with a non-deterministic function. To explain, we need to note two things up front: SQL Server cannot ...
Paul White's user avatar
  • 84.1k
26 votes
Accepted

What is the difference between an after update and a before update in PostgreSQL

There is one primary difference in a trigger that is AFTER from one that is BEFORE .. but both get executed regardless. In a BEFORE trigger, the trigger is executed BEFORE the DML statement is ...
Joishi Bodio's user avatar
  • 3,388
22 votes
Accepted

What does BEGIN ATOMIC mean in a PostgreSQL SQL function / procedure?

It's the new (in Postgres 14) syntax variant for SQL-standard functions. The release notes: Allow SQL-language functions and procedures to use SQL-standard function bodies (Peter Eisentraut) ...
Erwin Brandstetter's user avatar
21 votes
Accepted

PostgreSQL: Pass table as argument in function

The parameter _source in the MWE (minimal working example) is not referenced anywhere. The identifier source in the function body has no leading underscore and is interpreted as constant table name ...
Erwin Brandstetter's user avatar
20 votes
Accepted

Postgres get function owner

There is a simple and a complicated way. The complicated way is to join with pg_roles and get the user name from there. But since PostgreSQL hackers don't want to type more than necessary, they came ...
Laurenz Albe's user avatar
  • 51.7k
18 votes

Are there standard SQL functions with side effects?

You have a few different questions in here. Q: What are ANSI standard SQL functions? ANSI standard functions are things like AVG, COUNT, MIN, MAX. They're covered in the 1992 ANSI standard, but that'...
Brent Ozar's user avatar
18 votes
Accepted

How can I force a scalar UDF to be evaluated just once in a query?

Ultimately, it is not possible to force SQL Server to evaluate a scalar UDF just once in a query. However, there are some steps which can be taken to encourage it. With testing I believe that you can ...
Joe Obbish's user avatar
  • 32.2k
17 votes
Accepted

Test if a string is a palindrome using T-SQL

Since there are a fair number of solutions I'm going to go with the "critique" part of your question. A couple of notes: I've fixed some typos and noted where I did. If I'm wrong about them being a ...
Kenneth Fisher's user avatar
17 votes
Accepted

How can I eliminate this scalar function or make it faster?

Rewrite the scalar function as an in-line table-valued function, or upgrade to SQL Server 2019, which will automatically perform that translation for you. Machine Translation The following is the ...
Paul White's user avatar
  • 84.1k
17 votes

Why won't this UDF work in this check constraint?

The error is because the expression in a CHECK constraint must result in a boolean value (though there is no BOOLEAN datatype in SQL Server). Yours returns a bit value. You can technically solve this ...
ypercubeᵀᴹ's user avatar
17 votes
Accepted

Are views logically redundant?

Both views and inline TVFs are updatable, and can be used interchangeably almost everywhere. Specifically for SQL Server, the following features are supported by views that are not available for ...
Charlieface's user avatar
  • 12.9k
16 votes

In PostgreSQL, is there a type-safe first() aggregate function?

Yay, I've found out an easy way with your case by using some features in PostgreSQL 9.4+ Let's see this example: select (array_agg(val ORDER BY i))[1] as first_value_orderby_i, (array_agg(val ...
Mabu Kloesen's user avatar
16 votes
Accepted

PostgreSQL 9.6 column dropping and side-effects on SQL functions with CTEs

Probable bug on 9.6 and 9.6.1 This completely looks like a bug to me... I don't know why it happens, but I can confirm that it happens. This is the simplest found setup that reproduces the problem (...
joanolo's user avatar
  • 13.4k
16 votes
Accepted

In PostgreSQL, what is the difference between a "Stored Procedure" and other types of functions?

Since Postgres functions (CREATE FUNCTION) only run in a (single) transaction context, several important commands cannot be executed inside a function body. Like CREATE DATABASE or CREATE INDEX ...
Erwin Brandstetter's user avatar
15 votes

Test if a string is a palindrome using T-SQL

You could also use a Numbers table approach. If you don't already have an auxiliary numbers table you can create one as follows. This is populated with a million rows and so will be good for string ...
Martin Smith's user avatar
  • 84.7k
15 votes
Accepted

Generic end date of quarter, PostgreSQL

I think the shortest and most elegant way to solve this issue is to use date_trunc('quarter',d) (which will retrieve the start of the quarter) + 3 months - 1 day, and use the expression to create a ...
joanolo's user avatar
  • 13.4k
15 votes
Accepted

How to use variables in procedure (not function)?

SQL functions or procedures (LANGUAGE sql) do not have a DECLARE section - nor BEGIN / END. These are keywords for the block structure of the procedural language PL/pgSQL - in functions and procedures ...
Erwin Brandstetter's user avatar
14 votes

How to Convert Data to Proper Case In SQL Server

The challenge you'll run into with these approaches is that you've lost information. Explain it to the business users that they've taken a blurry, out of focus picture and despite what they see on t.v....
billinkc's user avatar
  • 15.9k
14 votes

Are 'Functional Indexes' Supported in MS SQL Server?

While there isn't a feature of Functional Indexes in SQL Server per se, one can mimic a similar implementation via indexing on top of a Computed Column. Computed columns allow functions to be utilized....
J.D.'s user avatar
  • 37.5k
14 votes
Accepted

Why Does SQL Server Track Time Accurately For Some Multi-Statement Table-Valued Function Query Plans, And Not Others?

This is a consequence of using interleaved TVF execution. Interleaved execution changes the unidirectional boundary between the optimization and execution phases for a single-query execution and ...
Paul White's user avatar
  • 84.1k
13 votes

Test if a string is a palindrome using T-SQL

The REVERSE() method "improved", i.e. reversing only half of the string: SELECT CASE WHEN RIGHT(@string, LEN(@string)/2) = REVERSE(LEFT(@string, LEN(@string)/2)) THEN 1 ...
13 votes
Accepted

How to use an array as argument to a VARIADIC function in PostgreSQL?

Pass the array as is using the VARIADIC key word in the call: CREATE OR REPLACE FUNCTION json_extract_path_text(string citext, VARIADIC params text[]) RETURNS text LANGUAGE sql IMMUTABLE PARALLEL ...
Erwin Brandstetter's user avatar
13 votes

Is there a way to prevent Scalar UDFs in computed columns from inhibiting parallelism?

In addition to @Paul's excellent Yes #1, there is actually a Yes #2 that: works as far back as SQL Server 2005, does not require setting a trace flag, does not require that the computed column be ...
Solomon Rutzky's user avatar
13 votes
Accepted

Why does a Set Returning Function (SRF) run slower in a FROM clause?

Let's start by comparing execution plans: tinker=> EXPLAIN ANALYZE SELECT * FROM generate_series(1,1e7); QUERY PLAN ...
willglynn's user avatar
  • 401
13 votes
Accepted

Emulate user-defined scalar function in a way which doesn't prevent parallelism

You cannot really safely achieve exactly what you want in SQL Server today, i.e. in a single statement and with parallel execution, within the restrictions laid out in the question (as I perceive them)...
Paul White's user avatar
  • 84.1k

Only top scored, non community-wiki answers of a minimum length are eligible