In SQL Server, you can use GOTO to alter the flow of execution. You can use it to “jump” to another part in the T-SQL code.
The way it works is, you create a label, then you can use GOTO to jump to that label. Any code between GOTO and the label are skipped, and processing continues at the label.
GOTO statements and labels can be used anywhere within a procedure, batch, or statement block. They can also be nested.
In SQL Server, you can use any of the following five ways to return a list of temporary tables using Transact-SQL.
These return both local and global temporary tables.
Here’s some T-SQL code you can use to get a list of all CHECK and foreign key constraints in a SQL Server database.
The RAND() function in SQL Server returns a pseudo-random float value from 0 through 1, exclusive.
This function can be deterministic or nondeterministic, depending on how it’s invoked.
Continue readingIn SQL Server, you can encrypt a view at the time you first create it, or you can alter it later to include encryption.
To create a view with T-SQL, you use the CREATE VIEW syntax. To encrypt it, you add the WITH ENCRYPTION argument.
You can also use the same argument to encrypt an existing view when using ALTER VIEW.
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, 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’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 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
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.