2

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

  • 1
    Not at a computer but isn't this the same error that is returned if you try to use NEWID() or GETDATE() within a function? The problem is not that there are actual side effects, more that they will not return the same results each time they are called. The typical workaround is to either not use a function in the first place, or to put those calls in a view and cal the view from the function. – Aaron Bertrand Oct 12 '15 at 14:11
  • @JulienVavasseur If CHANGE_TRACKING_MIN_VALID_VERSION actually changes the database, I would really like to know what changes it makes. – popq Oct 12 '15 at 14:37
  • @AaronBertrand That might be it. My db experience is mainly with one of the other big players, so I could very well be asking the wrong question. Thanks. – popq Oct 12 '15 at 14:40
  • @JulienVavasseur truncating the table is similar to deleting all rows, but without logging individual row deletions. The only sane thing to do when it comes to change tracking is to remove all ct data for the table that is truncated. This will of course affect the return value of CHANGE_TRACKING_MIN_VALID_VERSION (but is in no way a side effect of that function). – popq Oct 13 '15 at 7:56
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.