CODEWITHSUNDEEP
sql-serversql-viewsql-function
Haoest
Haoest

Reputation: 13916

Table-Valued Function(TVF) vs. View

What's the difference between table-valued functions and views? Is there something you can do with 1 that's hard or impossible to do with the other? Or does the difference lie in efficiency?

Upvotes: 96

Views: 48770

Answers (4)

TheMet4lGod
TheMet4lGod

Reputation: 116

To offer a different take on this, from my understanding: Views are static SELECT statements whereas TVFs are closer to Stored Procedures in both creation and execution in that you return a dynamic result set leveraging input variables. Regular (Inline) TVFs don't support flow control. If you need that then you need to use the Multi-Statement TVF. That said, you could also just as well INSERT into a table variable from a Stored Procedure which is what the TVF is doing in a more elegant way that doesn't need EXEC permissions.

From a maintainability perspective, I prefer TVFs as I don't have to write several queries to get various results or have to filter from a View (i.e., SELECT * FROM [view] WHERE [conditions]). In the TVF, I can leverage the indexes for returning smaller result sets faster. To do this in a view, you would have to have advance knowledge of what the query is going to do every time and hard specify the filtering conditions. If all you're going to do is select a subset of data from a table that was entered in the last 6 months, then a view would work fine as you can just write:

SELECT [columns]
FROM [schema].[table]
WHERE [date_col] >= CAST(DATEADD(m,-6,GETDATE()) AS DATE)

For performance you'd obviously want to index [date_col] so the view would run faster.

Upvotes: 1

Fandango68
Fandango68

Reputation: 4898

I generally have a rule of thumb when it comes to deciding whether to convert my SELECT to a VIEW or a TVF.

Does the view take longer than 2 seconds to finish and does it have more than 10,000 records? If YES, turn it into a TVF. If not, leave it alone.

Of course the rule is purely based on performance.

With a TVF, I can use a CROSS APPLY, for example to treat it as a table, but passing a specific value, such as the primary key.

WHERE ID = xxx, where 'xxx' is the value I pass in the SELECT.

Performance is way faster!

If I had a view of the TVF, I would have to allow the view to bring back over 2 million rows just to return less than 1% of that in my SELECTs.

Something to think about.

Upvotes: 9

Martin Smith
Martin Smith

Reputation: 453736

A parameterless inline TVF and a non materialized View are very similar. A few functional differences that spring to mind are below.

Views

Accepts Parameters               - No
Expanded out by Optimiser        - Yes
Can be Materialized in advance   - Yes (through indexed views)
Is Updatable                     - Yes 
Can contain Multiple Statements  - No
Can have triggers                - Yes
Can use side-effecting operator  - Yes

Inline TVFs

Accepts Parameters               - Yes
Expanded out by Optimiser        - Yes
Can be Materialized in advance   - No
Is Updatable                     - Yes
Can contain Multiple Statements  - No
Can have triggers                - No
Can use side-effecting operator  - No

MultiStatement TVFs

Accepts Parameters               - Yes
Expanded out by Optimiser        - No
Can be Materialized in advance   - No
Is Updatable                     - No
Can contain Multiple Statements  - Yes
Can have triggers                - No
Can use side-effecting operator  - No

At runtime Views and Inline TVFs are both inlined and treated similarly to derived tables or CTEs. They may well not be evaluated in their entirety (or even at all in some cases) or may be evaluated multiple times in others. Multistatement TVFs will always be evaluated and stored in the return table type (basically a table variable)

Occasionally the ability to parameterise inline TVFs directly can lead to a better execution plan than the equivalent parameterised query against a view.

Upvotes: 157

Douglas
Douglas

Reputation: 39

I have found that joins with MultiStatement TVFs perform much better than Views when a PK is specified on the function's return table.

CREATE FUNCTION [FORMREQS].[fnGetFormsStatus] ()
RETURNS

/* Create a PK using two of the columns */
@Indexed TABLE (
    [OrgID] [char](8) NOT NULL,
    [PkgID] [int] NOT NULL,
    [FormID] varchar(5) NOT NULL,
    PRIMARY KEY CLUSTERED(OrgID, PkgID) 
)
AS
BEGIN
INSERT @Indexed SELECT OrgID, PkgID, FormID FROM FormsTable

RETURN

END

Upvotes: 3

Related Questions