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.

Palocz

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. – Palocz

Maybe #440808 help you – Folklore

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. – Palocz

Answer 1

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'

Palocz

normally you wouldn't... but as per msdn Therefore, the returned rowset structure is implicitly defined. – Ideality

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. – Palocz

i guess to avoid that scenario MSDN suggested to define the table structure because as a standard we dont use SELECT * – Ideality

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. – Palocz

It's quite important though because we dont normally refresh medata after altering something. That's if you want to streamline your process. – Ideality

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. – Palocz

Answer 2

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  

Ideality