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.
Tag: how to
Return All Disabled Constraints in SQL Server (T-SQL Example)
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.
How to make RAND() Deterministic in SQL Server
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 readingHow to Encrypt a View in SQL Server
In 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.
Create a Temporary Table in SQL Server
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:
- Local temporary tables are prefixed with a single number sign (
#) - Global temporary tables are prefixed with a double number sign (
##)
How to Encrypt a Stored Procedure in SQL Server
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.
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 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
How 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.
How to Tell if a Computed Column is Deterministic in SQL Server
When you create a computed column in SQL Server, the expression you use for the column will either be deterministic or nondeterministic. This can have implications, such as whether or not you can use it in an index or flag it as “persisted”.
A deterministic column is one that will return the same value for a specific set of input values and given the same state of the database. A nondeterministic column can return a different value even when given the same input even if the database state remains the same. For example, a function that returns the current date is nondeterministic, because it will return a different value each day.
You can use the COLUMNPROPERTY() function with the IsDeterministic argument to find out whether or not a computed column is deterministic.