Table-valued function refresh

Question

I have a table-valued function (TVF) in SQL Server that looks like this:

CREATE FUNCTION TVF_xyz(@AuditKey INT)
RETURNS TABLE
AS
    RETURN
        SELECT *
        FROM xyz 
        WHERE AUDIT_KEY = @AuditKey
GO

Now, I added a new column to the xyz table.

When I query using TVF_xyz, it doesn't show me the new column (shows all other columns except newly added).

Query:

SELECT TOP 10 * 
FROM TVF_xyz (1543)

I would like to know, how to refresh TVF to show new column.

PS: Select * used in TVF to fetch all columns.

Hi masta rhian, how do I refresh view? 'xyz' is a table and when I query this table directly, I can see new field populated. — Ash, Mar 31 '17 at 02:01
Maybe http://stackoverflow.com/questions/440308/tsql-returning-a-table-from-a-function-or-store-procedure help you — TriV, Mar 31 '17 at 02:03
Well, there are 930 fields, writing them explicitly is a nightmare. Also, there are few more tables, for which I need to add a column. I'm wondering, why it doesn't show up a new column? I thought, TVF executes function everytime it is called. — Ash, Mar 31 '17 at 02:05

Ash · Accepted Answer · 2017-04-06T01:45:52.710

6

After bit of searching, I found sp_refreshsqlmodule (Transact-SQL), its common behavior of TVF.

In order to refresh TVF, following SP needs to be executed:

EXEC sys.sp_refreshsqlmodule 'TVF_xyz'

edited Apr 06 '17 at 01:45

answered Mar 31 '17 at 02:35

Ash

1,180
3
22
36

normally you wouldn't... but as per msdn Therefore, the returned rowset structure is implicitly defined. – RoMEoMusTDiE Mar 31 '17 at 02:50
Sorry, I didn't understand your statement. As per MS Technet page (link in my answer) sp_refreshsqlmodule is for: Updates the metadata for the specified non-schema-bound stored procedure, user-defined function, view, DML trigger, database-level DDL trigger, or server-level DDL trigger in the current database. Persistent metadata for these objects, such as data types of parameters, can become outdated because of changes to their underlying objects. – Ash Mar 31 '17 at 03:11
i guess to avoid that scenario MSDN suggested to define the table structure because as a standard we dont use SELECT * – RoMEoMusTDiE Mar 31 '17 at 03:14
I'm not sure, how that is related to my question. However, I'm happy that, I could understand how TVF works and how to refresh metadata. – Ash Mar 31 '17 at 03:54
It's quite important though because we dont normally refresh medata after altering something. That's if you want to streamline your process. – RoMEoMusTDiE Mar 31 '17 at 03:56
Well, in general, using explicit column names make sense. However, in my case, I've 930 fields and I do know, I'm about to add another field and ready to cater metadata changes down the line. Anyways, thank you for your suggestions. – Ash Mar 31 '17 at 04:13

score 0 · Answer 2 · answered Mar 31 '17 at 02:04

https://msdn.microsoft.com/en-us/library/bb386954(v=vs.110).aspx

The following SQL function explicitly states that it returns a TABLE. Therefore, the returned rowset structure is implicitly defined.

sample

CREATE FUNCTION ProductsCostingMoreThan(@cost money)  
RETURNS TABLE  
AS  
RETURN  
    SELECT ProductID, UnitPrice  
    FROM Products  
    WHERE UnitPrice > @cost

Table-valued function refresh

2 Answers2

Linked