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.
This article presents two ways to return a list of table-valued functions in a SQL Server database.
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 readingIn 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 readingIn SQL Server, you can create a scalar user-defined function using the CREATE FUNCTION statement. A scalar user-defined function, otherwise known as a scalar UDF, is a user-defined function that returns a single value.
This article contains examples of creating some basic T-SQL scalar UDFs.
Continue readingSchema 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
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 readingWhen 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.
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.
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.