116 votes
Accepted

How to join a table with a table valued function?

You need CROSS APPLY not join. The definition of table expressions involved in joins must be stable. I.e. They can't be correlated such that the table expression means something different dependant ...
  • 78.6k
92 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 ...
  • 73.8k
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 ...
  • 1,793
42 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 ...
  • 78.6k
41 votes
Accepted

How to grant permissions on a table-valued function

Scalar functions require EXECUTE permissions, however when you've converted to a Table Valued Function the permissions required change to SELECT. You must now GRANT SELECT ON functionName TO ...
35 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 ...
  • 57.2k
35 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 ...
  • 73.8k
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 ...
  • 73.8k
29 votes

EXPLAIN ANALYZE shows no details for queries inside a plpgsql function

The correct syntax for the EXPLAIN call needs a SELECT. You can't just write the bare function name in SQL: EXPLAIN ANALYZE SELECT f1(); Optimization PL/pgSQL functions are black boxes to the query ...
20 votes
Accepted

Why does LEN() function badly underestimate cardinality in SQL Server 2014?

For the legacy CE, I see the estimate is for 3.16228 % of the rows – and that is a "magic number" heuristic used for column = literal predicates (there are other heuristics based on ...
  • 3,491
18 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 ...
18 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 ...
  • 3,078
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'...
  • 41.5k
17 votes
Accepted

Is support for Parallel Scalar UDF a reasonable feature request?

It is fairly well documented that UDFs force an overall serial plan. I'm not certain it is all that well documented. A scalar T-SQL function prevents parallelism anywhere in the plan. A scalar CLR ...
  • 73.8k
17 votes
Accepted

Why "SET LOCAL statement_timeout" does not work as expected with PostgreSQL functions?

The way statement_timeout works, the time starts counting when the server receives a new command from the client. Queries launches inside server-side functions are not commands from a client, they ...
17 votes
Accepted

When to use stored procedure / user-defined function?

Postgres 11 or newer SQL procedures (not functions) can COMMIT and start new transactions: The manual for CREATE PROCEDURE. 2ndquadrant blog entry introducing the feature. However, commands that ...
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 ...
17 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 ...
  • 30.9k
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 ...
  • 73.8k
16 votes
Accepted

Assign to NEW by key in a Postgres trigger

First of all, there is no "trigger body" (unlike Oracle). In Postgres you have a trigger function (also, misleadingly, called "procedure") with a function body and 0-n triggers (...
16 votes
Accepted

Why scalar valued functions need execute permission rather than select?

Most likely the primary reason is that Table-Valued Functions return a Result Set, just like Tables and Views. This means that they can be used in the FROM clause (including JOINs and APPLYs, etc) of ...
16 votes
Accepted

How to drop all of my functions in PostgreSQL?

To drop all functions (including aggregates) in a given schema (be careful with this!): Postgres 11 or later Procedures have been added. The system catalog pg_proc slightly changed: prokind replaces ...
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 (...
  • 12.6k
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 ...
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 ...
  • 78.6k
15 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 ...
  • 35.2k
14 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 ...
  • 12.6k
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 ...
  • 73.8k
13 votes

Create index if it does not exist

It will be available in 9.5. Here is actual git commit https://github.com/postgres/postgres/commit/08309aaf74ee879699165ec8a2d53e56f2d2e947 Discussion on pg hackers http://postgresql.nabble.com/...
13 votes

Calculating Average Excluding Outliers

A very basic example would be to get the AVG and STDEV of the range of numbers and then exclude any that were more than 1 Standard Deviation from that average. You then take the average of the new ...

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