0

I am aware that derived table and Common table expression (CTE) do not persist. They live in memory til the end of the outer query. Every call is a repeated execution.

Do functions such as inline table-valued functions persist, meaning they are only calculated once ? Can we index an inline table-valued function?

Kenny
  • 1,902
  • 6
  • 32
  • 61

3 Answers3

0

Do functions such as inline table-valued functions persist

NO, if you see syntax of table valued function it returns result of a select statement in essence and so it doesn't store the fetched data anywhere (same as in view). So, NO there is no question of creating index on it since the data doesn't gets stored.

Unless you are storing that fetched data in another table like below and then you can create a index/other stuff on that test table;

SELECT * FROM yourInlineTableValuedFunction(parameter)
INTO TestTable;
Rahul
  • 76,197
  • 13
  • 71
  • 125
0

Inline function is basically the same thing as a view or a CTE, except that it has parameters. If you look at the query plan you'll see that the logic from the function will be included in the query using it -- so no, you can't index it and SQL Server doesn't cache it's results as such, but of course the pages will be in buffer pool for future use.

I wouldn't say that each call to CTE is a repeated execution either, since SQL server can freely decide how to run the query, as long as the results are correct.

For multi statement UDF each of the calls (at least in versions up to 2014) are separate executions, as far as I know, every time, and not cached in the sense I assume you mean.

James Z
  • 12,209
  • 10
  • 24
  • 44
  • I did mean the cache and persistence for efficient reuse. Then what is the catch of inline table-valued functions over CTE or view, except the fact that for CTE, we need to repeat the code everytime, and for functions, we don't need to ? I see that Views and Functions are stored as objects, so only their code definitions is stored, not data ? – Kenny Jul 24 '15 at 12:58
  • @Kenny My guess is that internally they are probably easier to optimize than views because you have parameters instead of joins from the query into the view which can lead to data types mismatch etc. Even Microsoft says "Inline functions can be used to achieve the functionality of parameterized views." in the 2008 documentation: https://technet.microsoft.com/en-us/library/ms189294%28v=sql.105%29.aspx when comparing to a CTE, then functions provide possibility for code re-use, CTE is just for that one statement. – James Z Jul 24 '15 at 13:12
0

Can we index an inline table-valued function?

No, but if you make the table into a temp table, sure you can index and speed up. The overhead of creation of a temp table will more than pay off in improved indexed access, caching, and, based on your use case, repeated use of the same temp table in a multi-user scenario.

Emacs User
  • 1,457
  • 1
  • 12
  • 19