Invalid use of a side-effecting operator 'change_tracking_min_valid_version' within a function

Question

I am trying to create a function wrapping change_tracking_min_valid_version(object_id), to simplify queries when using synonyms instead of table names. Something like this:

CREATE FUNCTION MY_CHANGE_TRACKING_MIN_VALID_VERSION(@synonym VARCHAR(100))
RETURNS BIGINT
BEGIN
    RETURN (SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(base_object_name))
    FROM sys.synonyms WHERE name = @synonym);
END

I have tried getting the base object name separately, to be able to drop the select part of the return statement, i.e.

set @retval = CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(@table_name))

MS SQL Server Management Studio produces this error:

Msg 443, Level 16, State 1, Procedure MY_CHANGE_TRACKING_MIN_VALID_VERSION, Line 5
Invalid use of a side-effecting operator 'change_tracking_min_valid_version' within a function.

I do understand that I can't use operations with side-effects in functions, but since I can't seem to find any information about (or understand why there would be any) side-effects of this specific function, my question is:

What are the side-effects of CHANGE_TRACKING_MIN_VALID_VERSION?

(I am using SQL Server 2012.)

Answer 1

As Aaron mentions, this logic could be done in a view rather than a function. If you do need to use a function, however, an alternative might be to use sys.change_tracking_tables rather than CHANGE_TRACKING_MIN_VALID_VERSION. It has a min_valid_version column, and as far as I can tell from the documentation it provides the same information you are looking for.

CREATE FUNCTION MY_CHANGE_TRACKING_MIN_VALID_VERSION(@synonym VARCHAR(100))
RETURNS BIGINT
BEGIN
    RETURN (SELECT c.min_valid_version
    FROM sys.synonyms s
    JOIN sys.change_tracking_tables c
        ON c.object_id = OBJECT_ID(s.base_object_name)
    WHERE s.name = @synonym);
END
GO

What are the side-effects of CHANGE_TRACKING_MIN_VALID_VERSION?

As far as I can tell from the documentation, there aren't any "side-effects" that will meaningfully impact you. As Aaron mentions, you'll get the same error if you try to use NEWID in a function. In both cases, I would suspect (but am not sure) that NEWID and CHANGE_TRACKING_MIN_VALID_VERSION are accessing and/or modifying an internal data structure (e.g., a random generator in the case of NEWID) and SQL Server is biasing towards caution in rejecting their usage in a function.

-- This generates the same error
CREATE FUNCTION testNewIdInFunction()
RETURNS UNIQUEIDENTIFIER
BEGIN
    RETURN (SELECT NEWID());
END