UPDATE against a table-valued function when declared inline

If you compare with a OO programming language, this behaviour could be seen as that the function returns a reference to the table, hence it is updateable.

On the other hand, these functions are named table valued functions, hence they should return a value and not a reference.

Yup, absolutely. No different to being able to run an update against a view, CTE or table-subquery.

After all, an inline TVF is just a parameterised view, right?

I don't see any problem with this - Linq works like this all the time, and so do other set based languages such as Mathematica.

This is completely intuitive to me.

I like this, I actually often create a view on top of a TVF to hide complexity of eg historical tables. The problem, of course is that the hiding of the complexity makes the end soution harder to govern, so this trade off makes it a rare case to enable update on these objects, but I see no reason why the tool should prevent it.

/g

Rationally, not a problem.

However... I'm pretty sure a lot of people like to encapsulate stuff in functions partly as a way to shelter their data from accidental updates.  This shatters that concept all the way to the ground, and the knowledge needs to be propogated further!

Good post, thanks for teaching me something new today.

Incidentally, like views it seems you can only update one table at a time... and I've not tried a merge yet, but hey, the day is young!

Rob's comment about this being analogous to the way a view works made this very clear for me.  It's quite close to a view defined without the check option.

I hoped that the TVF would behave the same way for INSERT and DELETE statements, and a test shows that they also work just fine.  Of course, you can delete only the rows that the TVF exposes, but it's perfectly possible to insert any valid row, even one that doesn't agree with the function's filter.

    insert into dbo.ListGNames()

    values (3, 'Nobby')

    insert into dbo.ListGNames()

    values (4, 'Glenda')

Both of those work without issue.

    delete from dbo.ListGNames()

    where TestTableName = 'Glenda'

No problem, but

    delete from dbo.ListGNames()

    where TestTableName = 'Nobby'

deletes no rows.

I expect by now your friend understands this very well.  The comments here are running in favour of "Yes, this makes sense". However, is it something we want to promote in development, or include in courseware?

I tweaked you example slightly so I could run it on both SQL Server 2005 and SQL Server 2008. I added another SELECT statement and added a step column so I could keep track of which result set went with which query. I also added another name that started with G so I could better see what was happening. I also made the script drop objects if they already existed so the script could be easily re-run.

-------------------------------------------------

SET NOCOUNT ON;

USE tempdb;

GO

IF OBJECT_ID('dbo.TestTable') IS NOT NULL

DROP TABLE dbo.TestTable

CREATE TABLE dbo.TestTable

( TestTableID int,

 TestTableName varchar(20)

);

GO

INSERT INTO dbo.TestTable

VALUES (1,'Hello');

INSERT INTO dbo.TestTable

VALUES (2,'Greg');

INSERT INTO dbo.TestTable

VALUES (3,'Grant');

GO

SELECT 1 AS Step,* FROM dbo.TestTable

GO

IF OBJECT_ID('dbo.ListGNames') IS NOT NULL

DROP FUNCTION dbo.ListGNames

GO

CREATE FUNCTION dbo.ListGNames()

RETURNS TABLE

AS

RETURN

( SELECT TestTableID, TestTableName

 FROM dbo.TestTable

 WHERE TestTableName LIKE 'G%'

);

GO

SELECT 2 AS Step,* FROM dbo.ListGNames();

GO

UPDATE dbo.ListGNames()

SET TestTableName = 'Fred'

WHERE TestTableName = 'Greg';

GO

SELECT 3 AS Step,* FROM dbo.ListGNames();

GO

SELECT 4 AS Step,* FROM dbo.TestTable;

GO

-------------------------------------------------

The results I get on both SQL Server 2005 and SQL Server 2008 are the following:

Step        TestTableID TestTableName

----------- ----------- --------------------

1           1           Hello

1           2           Greg

1           3           Grant

Step        TestTableID TestTableName

----------- ----------- --------------------

2           2           Greg

2           3           Grant

Step        TestTableID TestTableName

----------- ----------- --------------------

3           3           Grant

Step        TestTableID TestTableName

----------- ----------- --------------------

4           1           Hello

4           2           Fred

4           3           Grant

Note that the table data was changed after the function was updated resulting in the second row of the final result set having Fred instead of Greg. This seems to be different results than you got. What did I do wrong?

And, no, I don't think this sort of thing should be allowed. Regardless of what is or isn't changed, it just seems like dangerous functionality.

I also got the Same results as John Mayo. When I ran the script on SQL Server 2008 R2 with no modifications I found that the base table data was updated. Here are my results:

TestTableID TestTableName

1 Hello

2 Fred

I like this functionality.

I want to see it extended - I want WITH CHECK OPTION to be enabled for these functions.

While waiting for my previous comment to appear, I read John Mayo's comment about "dangerous functionality".

I don't think this is any more dangerous that updateable views. Same warnings and considerations apply.

It seems to violate the "functions cannot change database state" statement but only because that statement is wrong. It implies that there is a single class of object called a "function:. There isn't - there are three different classes of objects; scalar functions, inline table valued functions and multiline table valued functions. These three types of objects have different requirements and rules.

Maybe we need three different create statements?

CREATE PARAMETERIZED VIEW dbo.ListGNNames(...)

CREATE SCALAR FUNCTION dbo.GST(...)

CREATE TABLE FUNCTION dbo.SlaesByRegion(...)