When you create a temporary table in SQL Server, you have the option of making it a local or global temporary table.
Here’s a quick outline of the main differences between local temporary tables and global temporary tables.
5 Ways to List Temporary Tables using T-SQL
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.
Create a Temporary Table Based on Another Table in SQL Server
In SQL Server you can create a temporary table based on another table by using the SELECT... INTO syntax.
You can create the table with or without data. In other words, you can copy data from the original table if you wish, or you can create the table without any data.
Difference Between sys.parameters, sys.system_parameters, & sys.all_parameters in SQL Server
If you’re looking for a catalog view to return parameter information in SQL Server, you have a choice. In particular, you can get parameter information from sys.parameters, sys.system_parameters, and sys.all_parameters.
However, you will probably only want to use one of these views, as there are differences between them.
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.
Difference Between sys.columns, sys.system_columns, & sys.all_columns in SQL Server
Three of the system catalog views in SQL Server include sys.columns, sys.system_columns, and sys.all_columns.
These three catalog views each provide metadata about columns in the database, but there’s a difference between them.
Remove SCHEMABINDING from a View in SQL Server
The purpose of schema binding a view is to ensure that the base tables referenced in the view cannot be modified in a way that would affect the view definition.
This is normally a good thing. After all, you don’t want someone coming along and dropping a table that your view depends on, do you?
But what if you need to make changes to one or more tables referenced by your view?
Continue readingCreate a Schema Bound View in SQL Server
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 reading