SQL Magic And Tips

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!

Temporary Tables And Variables

Instantiated in tempdb

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.

1
2
3
4
5
6
-- Temporary table
CREATE TABLE #carl_log
(
	c1 Varchar(50), 
	c2 Varchar(50) 
);
1
2
3
4
5
6
-- Table Variables
DECLARE @carl_log table 
( 
	c1 Varchar(50), 
	c2 Varchar(50)
);

A Pro Tip!

1
2
3
4
5
IF(OBJECT_ID('[tempdb].[dbo].[#settings]') IS NOT NULL)
DROP TABLE [tempdb].[dbo].[#settings];

-- can be replaced by
DROP TABLE IF EXISTS #settings

DateAdd & Current Date Stamps

1
2
3
4
5
6
7
8
--- add using hours
SELECT DATEADD(hh, 24 * 1 + 11, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0))

--- add using minutes
SELECT DATEADD(minute, 2115, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0))

SELECT CURRENT_TIMESTAMP
SELECT GETUTCDATE()

Order By Random

Select a random record from a large table

1
2
3
SELECT TOP 1 *
FROM dbo.carl_table (NOLOCK)
ORDER BY NEWID()

Waitfor Delay

10 seconds

1
WAITFOR DELAY '00:00:10';

Constraints

The constraint will be called CON_some_useful_meaningful_name__my_column and is for the column my_column. The 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 UNIQUE.

1
2
3
4
5
6
7
USE myInstance;

SET LOCK_TIMEOUT 1000;

IF NOT EXISTS(SELECT TOP 1 1 FROM sys.key_constraints WHERE name = 'CON_some_useful_meaningful_name__my_column')
	ALTER TABLE [dbo].[my_table] ADD CONSTRAINT [CON_some_useful_meaningful_name__my_column] UNIQUE ([my_column])
GO

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!

1
2
3
4
5
6
7
DECLARE @Counter int = 0;
DECLARE @SomeUuid UNIQUEIDENTIFIER = NEWID ();

-- HACK HACK HACK

-- Inform the user
PRINT(CONVERT(varchar(1000),@Counter) + ' ' + CONVERT(varchar(36),@SomeUuid))

@@ROWCOUNT

Returns the number of rows affected by the last statement. If the number of rows is more than 2 billion, use ROWCOUNT_BIG.

1
2
3
4
5
SELECT * FROM dbo.foo WHERE id > 5;
SELECT @@ROWCOUNT

UPDATE dbo.foo SET some_field = NULL WHERE id IN (4,5,6);
SELECT @@ROWCOUNT