Cannot perform alter on 'FunctionName' because it is an incompatible object type

Question

I know the same question has been asked before, but the situation is slightly different and I'd like more explanation as to why it fails in my context please, when (AFAIK) the return type is the same.

Historically in my database I've used a CLR-based table valued function to split comma-separated values into a table containing an integer column.

Because SQL2016 introduces STRING_SPLIT, I need to swap out the CLR part of this function, and replace with the native TSQL function (as I have hundreds of stored procedures that rely on this function).

If I modify this function in SSMS, I see this:

ALTER FUNCTION [dbo].[e4fn_CreateIdTable](
     @Ids [nvarchar](max), 
     @separator [nchar](1))
RETURNS TABLE (
    [IdVal] [int] NULL
) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [ValueSplitter].[MyCompany.ValueSplitter].[SplitToIntegers]

I hoped I be able to alter it like so:

ALTER FUNCTION [dbo].[e4fn_CreateIdTable] (
    @Ids [nvarchar](max), 
    @separator [nchar](1)
)
RETURNS TABLE 
AS RETURN
(
    SELECT
        CAST(NULLIF(LTRIM(RTRIM(value)), '') AS int) AS IdVal
    FROM
        STRING_SPLIT(@Ids, @separator)
)
GO

This returned the error:

Cannot perform alter on 'dbo.e4fn_CreateIdTable' because it is an incompatible object type.

I assumed that was because the table definition of the output parameter was not explicitly defined. So, I retried like this:

ALTER FUNCTION [dbo].[e4fn_CreateIdTable] (
    @Ids [nvarchar](max), 
    @separator [nchar](1)
)
RETURNS @tbl TABLE  (
    IdVal int
)
AS 
BEGIN
    INSERT INTO @tbl (
        Idval
    )
    SELECT
        CAST(NULLIF(LTRIM(RTRIM(value)), '') AS int)
    FROM
        STRING_SPLIT(@Ids, @separator);

    RETURN;
END
GO

Again, the same error appears.

1. Why does the error appear, when the return types appear to be the same?
2. If I drop/recreate the function, do all the stored procedures need to be recompiled?

Answer 1

A CLR TVF is a fundamentally different database object type than a SQL TVF so one must use 'DROP/CREATE' instead of ALTER to change the definition.

The type in sys.objects for a CLR TVF is 'FT' whereas a SQL TVF (not inline) is 'TF'.