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 ...
- 10.1k
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 ...
- 156k
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 ...
- 156k
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 ...
- 28k
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 ...
- 156k
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 ...
- 23.8k
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 (...
- 156k
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 ...
- 66.3k
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 ...
- 156k
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 ...
- 156k
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/...
- 231
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 ...
- 10.1k
Only top scored, non community-wiki answers of a minimum length are eligible