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

Nested SELECT Statement

SELECT
	BestEmployee.Name
FROM (
	SELECT TOP 100
		*
	FROM
		Employee
	ORDER BY
		Employee.Sales DESC
) AS BestEmployee
ORDER BY
	BestEmployee.Name

Common Table Expression (CTE)

-- Untested code.
WITH BestEmployee_CTE
AS
(
	SELECT TOP 100
		*
	FROM
		Employee
	ORDER BY
		Employee.Sales DESC
)
SELECT
	BestEmployee.Name
FROM
	BestEmployee_CTE

Alter a Table

Add a column

ALTER TABLE TABLE_NAME
	ADD Column_Name INT NULL;

Add a column if it doesn't exist

IF COL_LENGTH('Table_Name','Column_Name') IS NULL
BEGIN
	ALTER TABLE TABLE_NAME
		ADD Column_Name INT NULL;
END

Rename a column

EXEC sp_rename 'Categories.Active', 'Active_Old', 'COLUMN'

Create or alter a stored procedure

If you want to preserve permissions:

IF OBJECT_ID('StoredProcedure_Name') IS NULL
	EXEC ('CREATE PROCEDURE StoredProcedure_Name AS SELECT 1')
GO
ALTER PROCEDURE StoredProcedure_Name
(
...

If you are happy to blow away permissions:

IF OBJECT_ID('StoredProcedure_Name', 'P') IS NOT NULL
	DROP PROC StoredProcedure_Name
 
GO
CREATE PROCEDURE StoredProcedure_Name
(
...

Duplicate (copy) a Database

BACKUP DATABASE SourceDatabase
TO DISK = 'E:\Backups\20150609 SourceDatabase.bak'
WITH COPY_ONLY
 
CREATE DATABASE NewDatabase
 
-- Use the LogicalName column to populate the MOVE clause of the RESTORE DATABASE command.
RESTORE FILELISTONLY FROM DISK = 'E:\Backups\20150609 SourceDatabase.bak' WITH FILE = 1
 
-- Use the physical_name column to populate the TO clause of the RESTORE DATABASE command.
SELECT
	master_files.physical_name
FROM
	master.sys.databases
JOIN
	master.sys.master_files
ON
	master_files.database_id = DATABASES.database_id
WHERE
	DATABASES.name = 'NewDatabase'
 
RESTORE DATABASE NewDatabase
FROM DISK = 'E:\Backups\20150609 SourceDatabase.bak'
WITH REPLACE,
MOVE 'SourceDatabase' TO N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\NewDatabase.mdf',
MOVE 'SourceDatabase_log' TO N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\NewDatabase_log.ldf'

Most Recent

SELECT *
FROM
	Foo
INNER JOIN
	History MostRecentHistory
	ON MostRecentHistory.FooId = Foo.Id
LEFT JOIN
	History AllHistory
	ON AllHistory.FooId = MostRecentHistory.FooId
	AND AllHistory.LastUpdateOn > MostRecentHistory.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 MostRecentHistory.FooId = Foo.Id
LEFT JOIN
	History AllHistory
	ON AllHistory.FooId = MostRecentHistory.FooId
	AND AllHistory.LastUpdateOn > MostRecentHistory.LastUpdateOn
WHERE
	AllHistory.Id IS NULL

Table Variables

Use a Nested SELECT Statement or a Common Table Expression instead!

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

Stored Procedure into Table Variable

Use a Nested SELECT Statement or a Common Table Expression instead!

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

List All Foreign Keys

SELECT
	  ReferentialConstraint.CONSTRAINT_NAME        ForeignKey_Name
	, ForeignKey.TABLE_SCHEMA                      ForeignKey_Schema
	, ForeignKey.TABLE_NAME                        ForeignKey_Table
	, ForeignKey.COLUMN_NAME                       ForeignKey_Column
	, ReferentialConstraint.UNIQUE_CONSTRAINT_NAME PrimaryKey_Name
	, PrimaryKey.TABLE_SCHEMA                      PrimaryKey_Schema
	, PrimaryKey.TABLE_NAME                        PrimaryKey_Table
	, PrimaryKey.COLUMN_NAME                       PrimaryKey_Column
	, ReferentialConstraint.MATCH_OPTION           MatchOption
	, ReferentialConstraint.UPDATE_RULE            UpdateRule
	, ReferentialConstraint.DELETE_RULE            DeleteRule
FROM
	INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ReferentialConstraint
JOIN
	INFORMATION_SCHEMA.KEY_COLUMN_USAGE ForeignKey
	ON ForeignKey.CONSTRAINT_NAME = ReferentialConstraint.CONSTRAINT_NAME
JOIN
	INFORMATION_SCHEMA.KEY_COLUMN_USAGE PrimaryKey
	ON PrimaryKey.CONSTRAINT_NAME = ReferentialConstraint.UNIQUE_CONSTRAINT_NAME

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'
-- NGA Provisioning
USE NextGenProvDB
EXEC sp_change_users_login 'update_one', 'NextGenUser', 'NextGenLogin'
-- 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

Creating a Login or User

This is particularly useful when the database is on Azure.

-- Create the Login.
-- The CHECK_EXPIRATION and CHECK_POLICY options relate to the password. Switch them off for a service or application type login.
CREATE LOGIN [OrchardUser] WITH PASSWORD=N'XXXXXXX', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
 
-- Create the User.
CREATE USER [OrchardUser] FOR LOGIN [OrchardUser] WITH DEFAULT_SCHEMA=[dbo]
GO
 
-- Grant a role.
EXEC sp_addrolemember N'db_owner', N'OrchardUser'

Nuke the Database

Generates SQL commands to drop everything in the database. Be careful you nuke the correct database…

USE [DATABASE]
 
SELECT 'DROP PROCEDURE ' + [Schema].name + '.' + StoredProcedure.name AS SqlCommand
       , 10                                                           AS SortOrder
FROM   sys.objects StoredProcedure
JOIN   sys.schemas [Schema]
ON     [Schema].schema_id = StoredProcedure.schema_id
WHERE  StoredProcedure.TYPE = 'P'
UNION
SELECT 'DROP VIEW ' + sys.objects.name
       , 20
FROM   sys.objects
WHERE  sys.objects.TYPE = 'V'
UNION
SELECT 'ALTER TABLE ' + [ParentTable].Name + ' DROP CONSTRAINT [' + [ForeignKey].name + ']'
       , 30
FROM   sys.objects [ForeignKey]
JOIN   sys.objects [ParentTable]
ON     [ParentTable].object_id = [ForeignKey].parent_object_id
WHERE  [ForeignKey].TYPE = 'F'
UNION
SELECT 'DROP TABLE ' + sys.objects.name
       , 40
FROM   sys.objects
WHERE  sys.objects.TYPE = 'U'
ORDER BY SortOrder

Calling a Stored Procedure with ADO.NET

If only one result set is returned:

using (SqlConnection connection = new SqlConnection("Data Source=...")) {
	connection.Open();
 
	using (SqlCommand command = new SqlCommand("StoredProcedureName", connection)) {
		command.CommandType = CommandType.StoredProcedure;
		command.CommandTimeout = 300;
 
		using (SqlDataReader reader = command.ExecuteReader()) {
			while (reader.Read()) {
				for (int i = 0; i < reader.FieldCount; i++) {
					string fieldName  = reader.GetName(i);
					// Note that values must be accessed by the column index and can not be accessed directly by field name.
					string fieldValue = reader.GetValue(i);
					Console.WriteLine("{0}: {1}", fieldName, fieldValue);
				}
			}
 
			reader.Close();
		}
	}
}

If multiple result sets are returned:

using (SqlConnection connection = new SqlConnection("Data Source=...")) {
	connection.Open();
 
	using (SqlCommand command = new SqlCommand("StoredProcedureName", connection)) {
		command.CommandType = CommandType.StoredProcedure;
		command.CommandTimeout = 300;
 
		using (SqlDataReader reader = command.ExecuteReader()) {
			do {
				while (reader.Read()) {
					for (int i = 0; i < reader.FieldCount; i++) {
						string fieldName  = reader.GetName(i);
						// Note that values must be accessed by the column index and can not be accessed directly by field name.
						string fieldValue = reader.GetValue(i);
						Console.WriteLine("{0}: {1}", fieldName, fieldValue);
					}
				}
 
			// Move to the next set of results.
			} while (reader.NextResult());
 
			reader.Close();
		}
	}
}

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.