This is an old revision of the document!

SQL Cheat Sheet

CREATE

CREATE TABLE
	Table1 (
		  Id       INT      NOT NULL
		, SomeDate datetime NULL
		CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ( Id ASC )
	)

INSERT

-- Combining an INSERT with a SELECT.
INSERT
	Table1 (
		  Col1
		, Col2
		, Col3
	)
SELECT
	  Table2.Col1
	, Table2.Col2
	, Table2.Col3
FROM
	Table2

UPDATE

-- Combining an UPDATE with a SELECT.
UPDATE
	Table1
SET
	  Table1.Col1 = Table2.Col1
	, Table1.Col2 = Table2.Col2
FROM
	Table1
INNER JOIN
	Table2
ON
	Table2.Id = Table1.Id

Most Recent

SELECT *
FROM
	Foo
INNER JOIN
	History MostRecentHistory
	ON Foo.Id = MostRecentHistory.Id
LEFT JOIN
	History AllHistory
	ON MostRecentHistory.Id = AllHistory.Id
	AND MostRecentHistory.LastUpdateOn < AllHistory.LastUpdateOn
WHERE
	AllHistory.Id IS NULL
 
-- If the Foo table may not have history and all results are still required:
SELECT *
FROM
	Foo
LEFT JOIN
	History MostRecentHistory
	ON Foo.Id = MostRecentHistory.Id
LEFT JOIN
	History AllHistory
	ON MostRecentHistory.Id = AllHistory.Id
	AND MostRecentHistory.LastUpdateOn < AllHistory.LastUpdateOn
WHERE
	AllHistory.Id IS NULL

Table Variables

DECLARE @Results TABLE (
	  BananaId   INT
	, BananaName VARCHAR(MAX)
)

Stored Procedure into Table Variable

DECLARE @Results TABLE (
	  BananaId   INT
	, BananaName VARCHAR(MAX)
)
 
INSERT INTO
	@Results
EXEC
	GetRipeBananas

Randomness

SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS RandomNumber
 
SELECT LEFT(CAST(NEWID() AS VARCHAR(100)), 6) AS RandomString

Fix Logins After DB Restore

-- Replace username with the actual username.
USE DATABASE
EXEC sp_change_users_login 'report'
EXEC sp_change_users_login 'auto_fix', 'CIPLogin' -- This definitely works.
-- Or try:
EXEC sp_change_users_login @Action = 'auto_fix', @UserNamePattern = 'User', @Password = 'Password'
EXEC sp_change_users_login 'update_one', username, username
-- Quantate
USE QuantateAdministration
EXEC sp_change_users_login 'update_one', 'QuantateSysAdmin', 'Quantate_Login'
-- CIP
USE CIP
EXEC sp_change_users_login 'update_one', 'CIPUser', 'CIPLogin'
-- General. 
EXEC sp_change_users_login 'update_one', 'DBUser', 'ServerUser'
-- Server -> Security -> Logins -> *ServerUser* -> Right click -> Properties
-- User Mapping -> Find correct database, value in User col = *DBUser*

Accessing A Linked Server

SELECT TOP 100
	*
FROM
	OpenQuery (LinkedServerName, 'SELECT * FROM Users')

Determining Foreign Key Relationships

Can do this, but it will only show which tables reference the specified table, not what tables the specified table references:

sp_fkeys TableName

Better to do this, which lists all foreign key relationships and lots of other information as well:

sp_help TableName

Joins

Type	Code Sample	Notes
Inner Join	SELECT * FROM Foo INNER JOIN Bar ON Bar.Id = Foo.BarId	The most common type of join.
Left Join	SELECT * FROM Foo LEFT JOIN Bar ON Bar.Id = Foo.BarId	The second most common type of join.
Right Join	SELECT * FROM Foo RIGHT JOIN Bar ON Bar.Id = Foo.BarId	Rare type of join.
Full Join (or Outer Join)	SELECT * FROM Foo FULL JOIN Bar ON Bar.Id = Foo.BarId FULL OUTER JOIN	Rare type of join.
Left Excluding Join	SELECT * FROM Foo LEFT JOIN Bar ON Bar.Id = Foo.BarId WHERE Bar.Id IS NULL	Uncommon type of join, but useful to avoid this: SELECT * FROM Foo WHERE Foo.Id NOT IN ( SELECT Bar.FooId FROM Bar ) which is slow.
Right Excluding Join	SELECT * FROM Foo RIGHT JOIN Bar ON Bar.Id = Foo.BarId WHERE Foo.Id IS NULL	Rare type of join.
Full Excluding Join	SELECT * FROM Foo FULL JOIN Bar ON Bar.Id = Foo.BarId WHERE Foo.Id IS NULL OR Bar.Id IS NULL	No known practical application at all.