Category: SQL

How to Create a Schema Bound Stored Procedure in SQL Server

Posted on by Ian

If you’ve ever created a schema bound UDF, you’ll know that schema binding it is just a matter of adding WITH SCHEMABINDING to your definition. The same applies when you create a schema bound view.

Stored procedures are a bit different.

Only natively compiled stored procedures can be schema bound. In fact, natively compiled stored procedures must be schema bound. You can’t create a natively compiled stored procedure without schema binding it.

But if you try to schema bind a regular (non-natively compiled) procedure, you’ll get an error.

Continue reading →

Introduction to User-Defined Functions in SQL Server

Posted on by Ian

SQL Server ships with a collection of built-in functions that enable you to perform a variety of operations. Each built-in function serves a specific purpose, and can’t be modified. If a function serves your purpose, you can go ahead and use it.

But what if you can’t find a function that serves your purpose?

Then it’s time to write your own user-defined function.

Continue reading →

Remove SCHEMABINDING from a User-Defined Function in SQL Server

Posted on by Ian

The purpose of schema binding a user-defined function (UDF) is to ensure that the base objects referenced in the UDF cannot be modified in a way that would affect the function’s definition.

This is fine, as long as you don’t need to make any changes to the underlying objects. But what if you do need to make changes?

Continue reading →

What is a Scalar UDF in SQL Server?

Posted on by Ian

In SQL Server, a scalar UDF, or scalar user-defined function, is a user-defined function that returns a single value.

This is in contrast to a table-valued function, which returns a result set in the form of a table.

User-defined functions can be written as either T-SQL UDFs or CLR (Common Language Runtime) UDFs. CLR enables you to create UDFs in managed code using any .NET Framework programming language. These functions are then accessible to T-SQL or other managed code.

Continue reading →

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

Posted on 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 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 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 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 →