How to Check if a T-SQL UDF is Schema Bound (Even When it’s Encrypted)

Posted on February 9, 2020September 21, 2024 by Ian

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

Continue reading →

Create a Schema Bound UDF in SQL Server

Posted on February 8, 2020September 21, 2024 by Ian

In SQL Server, it’s usually a good idea to schema bind your user-defined functions (UDFs).

Schema binding your UDF will ensure that the underlying tables can’t be changed in a way that would affect your function. Without schema binding, the underlying tables or other objects could be modified or even deleted. Doing this could break the function.

Continue reading →

How to Encrypt a User-Defined Function in SQL Server

Posted on February 8, 2020February 14, 2020 by Ian

When creating a user-defined function in SQL Server, you have the option of encrypting it.

To create a user-defined function with T-SQL, you use the CREATE FUNCTION syntax. To encrypt it, you add the WITH ENCRYPTION argument.

You can also use the same argument to encrypt an existing function when using ALTER FUNCTION.

Continue reading →

Difference Between Multi-Statement Table-Valued Functions & Inline Table-Valued Functions in SQL Server

Posted on February 7, 2020February 9, 2020 by Ian

When you create a table-valued function (TVF) in SQL Server, you can either make it an inline table-valued function (ITVF) or a multi-statement table-valued function (MSTVF). There are differences between these function types, and they use a different syntax accordingly.

This article covers the difference between MSTVFs and ITVFs.

Continue reading →

Introduction to Multi-Statement Table-Valued Functions (MSTVF) in SQL Server

Posted on February 7, 2020February 9, 2020 by Ian

In SQL Server, the multi-statement table-valued function is one of two types of Transact-SQL table-valued functions (the other type being the inline table-valued function).

Table-valued functions (TVF) are a type of user-defined function that return their results as a table. They can therefore be queried just like a normal table.

Continue reading →

Introduction to Inline Table-Valued Functions (ITVF) in SQL Server

Posted on February 7, 2020February 9, 2020 by Ian

In SQL Server, the inline table-valued function is one of two types of Transact-SQL table-valued functions (the other type being the multi-statement table-valued function).

Table-valued functions (TVF) are a type of user-defined function that return their results as a table. They can therefore be queried just like a normal table.

Continue reading →

Alter a Table-Valued Function in SQL Server

Posted on February 7, 2020February 9, 2020 by Ian

You can alter a table-valued function (TVF) in SQL Server using the ALTER FUNCTION T-SQL syntax.

You simply follow ALTER FUNCTION with the new definition. Basically it’s like using CREATE FUNCTION except you replace CREATE with ALTER.

Continue reading →

Create a Table-Valued Function in SQL Server

Posted on February 6, 2020February 9, 2020 by Ian

You can create a table-valued function (TVF) in SQL Server using the CREATE FUNCTION T-SQL syntax.

The syntax is slightly different depending on whether you’re creating an inline table-valued function (ITVF) or a multi-statement table-valued function (MSTVF).

Continue reading →