If you’re getting the following error (or similar error) while trying to execute a natively compiled stored procedure in SQL Server 2019, read on.
Continue readingQuery failed: Internal connection fatal error.
Tag: mssql
How to Create a Schema Bound Stored Procedure in SQL Server
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 readingIntroduction to User-Defined Functions in SQL Server
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.
2 Ways to List All Table-Valued Functions in a SQL Server Database
This article presents two ways to return a list of table-valued functions in a SQL Server database.
Remove SCHEMABINDING from a User-Defined Function in SQL Server
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 readingWhat is a Scalar UDF in SQL Server?
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 readingCreate a Scalar User-Defined Function in SQL Server
In 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 readingHow to Check if a T-SQL UDF is Schema Bound (Even When it’s Encrypted)
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
Create a Schema Bound UDF in SQL Server
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 readingHow to Encrypt a User-Defined Function in SQL Server
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.