Schema binding an object such as a user-defined function (UDF) is considered good practice, as it prevents changes being done to any objects that it references that could inadvertently break the function.
You can schema bind a user-defined function at the time you create it, or you can alter later on.
Normally, you can check if a UDF is schema bound in SQL Server by viewing its definition. You can usually do this via the GUI by selecting “Script as Create” or similar.
You can also do it using T-SQL by selecting the definition
column of the sys.sql_modules
system catalog view.
But this will only work if the UDF isn’t encrypted.
However, there is another column in the sys.sql_modules
view that serves our purpose whether the UDF is encrypted or not: is_schema_bound
Example 1 – Encrypted UDF
Here’s an example of finding out whether an encrypted user-defined function called udf_CatsByName_ITVF
is schema bound or not.
SELECT
definition,
is_schema_bound
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('udf_CatsByName_ITVF');
Result:
+--------------+-------------------+
| definition | is_schema_bound |
|--------------+-------------------|
| NULL | 1 |
+--------------+-------------------+
In this case, the UDF is schema bound.
Also notice that the definition
column returns NULL because the function has had encryption applied.
Example 2 – UDF Without Encryption
If encryption hadn’t been applied, we could have seen the full definition in that column, and we would have seen the argument WITH SCHEMABINDING
in the definition.
Here’s the query again when the function is not encrypted.
SELECT
definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('udf_CatsByName_ITVF');
Result:
+--------------+
| definition |
|--------------|
|
CREATE FUNCTION dbo.udf_CatsByName_ITVF( @CatName varchar(70) )
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN (
SELECT
CatId,
CatName,
Phone
FROM dbo.Cats
WHERE CatName = @CatName
);
|
+--------------+
I’ve removed the is_schema_bound
column from the query to make it easier to read.
Either way, the is_schema_bound
column can be used whether the UDF is encrypted or not.