In SQL Server, the @@SERVICENAME configuration function returns the name of the registry key under which SQL Server is running.
No argument is required. You can simply use it in a SELECT statement to return the registry key’s name.
Note that SQL Server runs as a service named MSSQLServer. The @@SERVICENAME function returns MSSQLSERVER if the current instance is the default instance. It returns the instance name if the current instance is a named instance.
This article presents two ways to return a list of stored procedures in a SQL Server database.
In SQL Server, you can encrypt a stored procedure at the time you create it, or you can alter it later to include encryption.
To create a stored procedure with T-SQL, you use the CREATE PROCEDURE syntax. To encrypt it, you add the WITH ENCRYPTION argument.
You can also use the same argument to encrypt an existing procedure when using ALTER PROCEDURE.
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.
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 readingSQL 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 reading