Skip to main content
added 804 characters in body
Source Link
Geoff Patterson
  • 8.4k
  • 2
  • 28
  • 53

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

If you do need to use a function, 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

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
Source Link
Geoff Patterson
  • 8.4k
  • 2
  • 28
  • 53

If you do need to use a function, 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