1

We have a lot of multi-statement TVFs and we sometimes want to convert these to be inline TVFs (usually for performance reasons).

We can't do a simple ALTER FUNCTION to make this switch because SQL Server tells us "Cannot perform alter on 'db.FOO' because it is an incompatible object type." so we have to drop the function first and then recreate it.

This has worked well for us, but we aren't sure why SQL Server doesn't just do the drop/create automatically. Is there some particular issue that it's trying to protect us from that we're blindly walking in to?

Some Google searching reveals people getting the error message and being told to just drop it and recreate it, but I haven't been able to figure out the why of the error message in the first place.

Any insight into the internals would be appreciated.

James Williams
  • 1,861
  • 1
  • 15
  • 21

1 Answers1

3

They are 2 different kinds of objects in sys.objects thus are not compatible.

Effectively, it is the same as

CREATE TABLE dbo.foo (...)

then

ALTER VIEW dbo.foo ...

A view and a table can be used the same way but they are not the same. Same for TVFs.

There should be different flavours of CREATE FUNCTION for the 3 types...

gbn
  • 422,506
  • 82
  • 585
  • 676
  • But as far as "not being compatible", how are they not compatible? Are there cases where converting one to another will cause incorrect results (assuming the conversion itself is bug free, of course)? Will it merely invalidate existing plans so those have to be recompiled? Something else? – James Williams Aug 01 '11 at 15:31
  • @James Williams: there are not "converted". An inline one is a macro that expands into the outer query that has can have statistics etc. A multi statement is a black box and a series of steps and is self contained plan wise. So an inline can never has it's own plan because it's a macro. The multi-statement one has it's own plan that is not considered part of the outer query. Don't get hung up on it: they are different things. – gbn Aug 01 '11 at 15:39
  • So blindly dropping one of a certain kind and and then recreating something with the same name of the other kind doesn't have any issues. It just has to be a manual process instead of an automatic one, but everything will continue to work. Ok. – James Williams Aug 01 '11 at 15:42
  • See http://stackoverflow.com/questions/2435587/profiling-statements-inside-a-user-defined-function/2435631#2435631 and http://stackoverflow.com/questions/1432343/table-valued-function-where-did-my-query-plan-go/1432413#1432413 – gbn Aug 01 '11 at 15:42
  • @James Williams: yes: the difference will be performance as long the same data comes back for given inputs – gbn Aug 01 '11 at 15:43