Skip to main content
26 votes
Accepted

Does SQL Server cache the result of a multi-statement table-valued function?

The results of a multi-statement table-valued function (msTVF) are never cached or reused across statements (or connections), but there are a couple of ways that an msTVF result may be reused within ...
Paul White's user avatar
  • 86.4k
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
12 votes
Accepted

Multi-statement TVF vs Inline TVF Performance

Your numbers table is a heap and is potentially being fully scanned each time. Add a clustered primary key on Number and try the following with a forceseek hint to get the desired seek. As far as I ...
Martin Smith's user avatar
  • 85.2k
10 votes
Accepted

How to recompile all stored procedures and table valued functions in SQL Server database?

You can run sp_recompile on everything by using a cursor to produce ad-hoc SQL for each and run it, if you think that will help: DECLARE C CURSOR FOR (SELECT [name] FROM sys.objects WHERE [type] IN ('...
David Spillett's user avatar
9 votes

UNION ALL or other ways to return first row of result-set

If you must use a single query (as required by a single inline function), you can use one of the two options below (illustrated in my recent answer to Relating 2 tables with possible wildcards?): ...
Paul White's user avatar
  • 86.4k
9 votes
Accepted

How to use RETURNS TABLE with an existing table in PostgreSQL?

PS: sorry for my comments, I was developing a complex code and I have a small error that seems a stupid PostgreSQL restriction on a "returing table" part... I was stupid, instead of ...
9 votes

How to recompile all stored procedures and table valued functions in SQL Server database?

This solution is based upon the other answer here, but takes schemas into account and considers special characters in the name. It recompiles all procedures, functions and inline functions in all ...
SQL Police's user avatar
8 votes
Accepted

Should I add set nocount for functions?

Functions cannot return data to a client so I can't think of any use of SET NOCOUNT ON. If you do try to add it a function you'll get an error. SQL Server considers it to cause a side effect: ...
Joe Obbish's user avatar
  • 32.5k
7 votes
Accepted

Split up interval into year slices

A couple of suggestions: Use legal, lower-case, unquoted identifiers to save yourself a lot of confusion. end (and, to a lesser degree start) are reserved words. Your id column seems to be numeric ...
Erwin Brandstetter's user avatar
7 votes
Accepted

Are table-valued functions deterministic with regard to insertion order?

Display order is only guaranteed by a top-level ORDER BY. You could rewrite the function to return a position key and make it inline: CREATE OR ALTER FUNCTION dbo.AllChars ( @in nvarchar(max) ) ...
Paul White's user avatar
  • 86.4k
6 votes

Understanding Set Returning Function (SRF) in the SELECT List

Postgres treats the simple case differently. Multiple columns are treated as composite type (table row), which is only decomposed with SELECT * FROM ..., while a single column of scalar type is ...
Erwin Brandstetter's user avatar
6 votes
Accepted

Postgres - Return default value if regex match fails

While your answer solves the problem with regexp_matches(), the much better solution is to avoid the problem with the more appropriate function substring() to begin with: WITH test_data(txt) AS ( ...
Erwin Brandstetter's user avatar
6 votes
Accepted

How to return several arrays of doubles as a table in C# SQLCLR function

If you are wanting to return the 3 arrays as 3 separate result sets, that is not possible for a function, whether SQLCLR or T-SQL. You would need to create a stored procedure instead in order to ...
Solomon Rutzky's user avatar
5 votes

How to recompile all stored procedures and table valued functions in SQL Server database?

If you add memory (even if it's a Hot Added to a VM), and increase Max Server Memory to match, your plan cache will clear out. That is effectively 'recompiling' all of those things you mentioned, ...
Erik Darling's user avatar
  • 41.9k
5 votes
Accepted

Why would adding a column to a view corrupted a table-valued function?

Yes, this is expected. Functions and views that use SELECT * end up storing part of the metadata themselves relating to the underlying columns, so it is very easy for them to get confused. I talk ...
Aaron Bertrand's user avatar
5 votes
Accepted

Efficiency of Scalar UDF vs TVF

As I understand, the function is called and runs at the cross apply, meaning that it should be calculating a value even if it is not in the select, so why would it be faster not to include it in the ...
Paul White's user avatar
  • 86.4k
4 votes
Accepted

Incorrect statistics are used on index seek

The estimate for the inner (lower) input to a nested loops join is per iteration in SSMS. The 'actual' number of rows shown in SSMS is a total over all iterations. This often causes confusion, and is ...
Paul White's user avatar
  • 86.4k
4 votes
Accepted

How to (optimally) get random sample of (a.id, b.id) pairs from two tables (a, b)?

The best solution depends on the exact definition of your setup. For the example setup it's trivial: Serial integer columns without gaps. SELECT DISTINCT 1 + trunc(random() * 100)::int AS ...
Erwin Brandstetter's user avatar
4 votes
Accepted

Selecting only overlapping elements from array of ranges

This works as desired: SELECT d.sensor, r.overlapping_ranges FROM data d JOIN LATERAL ( SELECT array_agg(range) AS overlapping_ranges FROM unnest(d.ranges) range WHERE range &&...
Erwin Brandstetter's user avatar
4 votes

UNION ALL or other ways to return first row of result-set

I don't know sql-server well enough to state whether it works this way or not, but in theory there is no way you can say that one part of the union is evaluated before another. I.e. even though you ...
Lennart - Slava Ukraini's user avatar
4 votes
Accepted

How do you declare a set-returning-function to only be allowed in the FROM clause?

I am afraid that's only possible with unnest() after all. unnest() with multiple parameters is a special Postgres feature, that's internally rewritten into multiple unnest() calls that are combined ...
Erwin Brandstetter's user avatar
4 votes

T-SQL Daylight Saving lookup table - poorly performing table-valued function

If Tzid and DT_WhenSwitch define a unique row I recommend clustering the dbo.DSTLookup table by those two columns. You can make those columns the primary key if you want or you can just make them be ...
Joe Obbish's user avatar
  • 32.5k
4 votes
Accepted

Generate series 0.01 to 0.99

Is there any simple method to get a series One method SELECT a.n FROM generate_series(0.01, 0.99,0.01) as a(n); Working example 0.01 - start of series 0.99 - end of series 0.01 - increment ...
Ergest Basha's user avatar
  • 3,945
4 votes

Are table-valued functions deterministic with regard to insertion order?

No order is ever guaranteed without an order by clause. If you created an index on your table variable, you may get lucky and return data in index order depending on various optimizer choices, but it ...
Erik Darling's user avatar
  • 41.9k
3 votes
Accepted

T-SQL Daylight Saving lookup table - poorly performing table-valued function

Make your function a schema-bound table-valued-function by adding WITH SCHEMABINDING to the RETURNS TABLE clause. So: CREATE FUNCTION dbo.FN_GetLocalTime_FromUTC_BasedOnTZId (@StartDateTime ...
Hannah Vernon's user avatar
  • 70.2k
3 votes
Accepted

Function that returns table with an additional column

AIUI, your wish is to shorten the RETURNS clause of the function. Not sure if you want to establish a dependency on the row type of the table at the same time, but that would make sense here, too. ...
Erwin Brandstetter's user avatar
3 votes
Accepted

How to insert a record returned by a function into a table

Assuming current Postgres version 9.5, since it has not been defined. Best solved with a LATERAL join (Postgres 9.3+). The minimal working form: INSERT INTO table_output SELECT f.* FROM ...
Erwin Brandstetter's user avatar

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