SQL Magic and tips that are too small to warrant their own pages (read Carl doesn’t know enough about them) but are still pretty sweet and worth noting how to do!
- [https://github.com/carlpaton/SQLStatements/blob/master/mssql/General/table%20variables.sql](https://github.com/carlpaton/SQLStatements/blob/master/mssql/General/table variables.sql)
- [https://github.com/carlpaton/SQLStatements/blob/master/mssql/General/temporary%20tables.sql](https://github.com/carlpaton/SQLStatements/blob/master/mssql/General/temporary tables.sql)
- Temporary Tables
You can create local and global temporary tables. Local temporary tables are visible only in the current session, and global temporary tables are visible to all sessions.
-- Temporary table
-- Table Variables
A Pro Tip!
IF(OBJECT_ID('[tempdb].[dbo].[#settings]') IS NOT NULL)
--- add using hours
Select a random record from a large table
SELECT TOP 1 *
WAITFOR DELAY '00:00:10';
The constraint will be called
CON_some_useful_meaningful_name__my_column and is for the column
CON_ prefix can be denoted as anything useful to your organization, always follow the suggestions of your DBA or the existing naming conventions in the database.
The constraint below means
my_column has to be
When print things out for the user to see your proc doing something you will need to convert types. TIP! Set
SET NOCOUNT ON first so you dont get
(n row(s) affected) in your messages!
DECLARE @Counter int = 0;
Returns the number of rows affected by the last statement. If the number of rows is more than 2 billion, use ROWCOUNT_BIG.
SELECT * FROM dbo.foo WHERE id > 5;