It’s usually a good idea to schema bind your views in SQL Server.
Schema binding your view will ensure that the underlying tables can’t be changed in a way that would affect the view. Without schema binding, the underlying tables or other objects could be modified or even deleted. If that happens, the view will no longer work as expected.
Continue readingIn SQL Server, you can use the sp_helptrigger stored procedure to return the type or types of DML triggers defined on the specified table for the current database.
This stored procedure only works on DML triggers (not DDL triggers).
If you’ve used T-SQL for a while, it’s likely you’ve encountered the sys.objects system catalog view, which returns user-defined, schema-scoped objects in the database.
But there are two other similar views that you may or may not be aware of; sys.system_objects and sys.all_objects.
It’s amazing how quickly some features can become deprecated in the world of software.
This article presents two methods to see whether deprecated features are being used in a SQL Server instance.
In SQL Server, temporary tables are created using the same CREATE TABLE syntax as regular tables. The difference is that temporary tables’ names are prefixed with either one or two number signs (#), depending on whether it’s a local temporary table or global temporary table:
#)##)In SQL Server, a temporary table is a certain kind of table that exists until goes out of scope (unless it’s explicitly dropped).
This is different to a regular (persistent) table, where the regular table exists permanently in your database until you explicitly drop it.
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.