Deterministic way to get column type

Question

I have a computed column which uses a Scalar-valued Function to evaluate it's value. I need to make the column Persisted because of some reasons and to do it, I need to make that function deterministic. I use a query like the following to get the column type:

SELECT 
    @dataType = DATA_TYPE
FROM 
    INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE 
    TABLE_SCHEMA = 'X' AND
    TABLE_NAME = @TableName AND 
    COLUMN_NAME = @ColumnName

When I try to add WITH SCHEMABINDING clause to the function (to make it deterministic) I get the following error:

Cannot schema bind function 'X.MY_FUNCTION' because it references system object 'INFORMATION_SCHEMA.COLUMNS'.

How can I get the type of a column in a deterministic way? I looked into COLUMNPROPERTY and other Metadata functions but couldn't find a way to get the type of a column without using system objects.

Answer 1

For TSQL TVF's SQL verifies that the function is deterministic, but SQL Cannot verify that your function is deterministic if it access catalog views, because it cannot use SCHEMABINDING.

You can use a CLR TVF, which SQL doesn't completely verify, but will allow you to mark a CLR TVF as deterministic and use it in a persisted computed column.