SQL: UPDATE against a table-valued function when declared inline

I had a discussion with a friend the other day who wouldn’t believe me that T-SQL allowed an UPDATE operation to be executed against a function in some situations. I must admit that when I first heard about it, it did my head in a bit as well, at least based on all I thought I knew about programming languages in general.

You can only perform an UPDATE against a table-valued function in T-SQL when the function was declared as an inline TVF. It still feels to me like it shouldn’t ever be permitted, but if you want to try it, here’s an example:

USE tempdb;
GO

CREATE TABLE dbo.TestTable
( 
    TestTableID int,
    TestTableName varchar(20)
);
GO

INSERT INTO dbo.TestTable
(
    TestTableID, TestTableName
)
VALUES (1,'Hello'), 
       (2,'Greg');
GO

CREATE FUNCTION dbo.ListGNames()
RETURNS TABLE
AS
RETURN
( 
    SELECT TestTableID, TestTableName
    FROM dbo.TestTable
    WHERE TestTableName LIKE 'G%'
);
GO

SELECT * FROM dbo.ListGNames();
GO

UPDATE dbo.ListGNames()
SET TestTableName = 'Fred'
WHERE TestTableName = 'Greg';
GO

SELECT * FROM dbo.ListGNames();
GO

SELECT * FROM dbo.TestTable;
GO

Note that the target of the UPDATE is the function not the underlying table, but of course it’s the table that is updated.

What do you think? Should this behavior even be permitted?

Learn more about Advanced T-SQL

If you really want to learn about SQL Server Advanced T-SQL, we have an online on-demand course that you can enrol in, right now. You’ll find it at SQL Server Advanced T-SQL for Developers and DBAs

2026-02-13