THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Bit Bucket (Greg Low): IDisposable

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

UPDATE against a table-valued function when declared inline

The other day I mentioned to a friend that you could only perform an UPDATE against a table-valued function in T-SQL when the function was declared as an inline TVF. He basically didn't understand the point being made at all. That's not really suprising as the idea of performing an UPDATE against a function does my head in, in terms of everything I ever thought I knew about programming languages.I really don't think it should even be permitted but regardless, here's an example:

 

USE tempdb;

GO

 

CREATE TABLE dbo.TestTable

( TestTableID int,

  TestTableName varchar(20)

);

GO

 

INSERT INTO dbo.TestTable

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 UPDATE is being performed against the *function* not against the underlying table.

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

Published Sunday, May 22, 2011 3:01 PM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Peter said:

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.

May 22, 2011 2:56 AM
 

Rob Farley said:

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?

May 22, 2011 5:09 AM
 

Alexander Kuznetsov said:

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.

May 22, 2011 11:48 AM
 

Gorm Braarvig said:

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

May 24, 2011 5:49 AM
 

RichB said:

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!

May 24, 2011 6:03 AM
 

Mark Hions said:

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?

May 24, 2011 3:27 PM
 

John Mayo said:

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.

May 24, 2011 3:46 PM
 

Meher said:

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

May 26, 2011 11:32 AM
 

Craig Beere said:

I like this functionality.

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

June 29, 2011 11:27 PM
 

Craig Beere said:

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(...)

June 29, 2011 11:33 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement