Stephen's Wiki

User Tools

Site Tools

Trace: • sql_cheat_sheet
sql_cheat_sheet

This is an old revision of the document!

Table of Contents

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 @ProductTotals TABLE
(
  ProductID INT,
  Revenue money
)

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', username
EXEC sp_change_users_login 'update_one', username, username

Accessing A Linked Server

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

Joins

Type Diagram Code Sample Notes
Inner Join Inner Join 
SELECT
    *
FROM
    Foo
INNER JOIN
    Bar
    ON Bar.Id = Foo.BarId
 The most common type of join.
Left Join Left Join 
SELECT
    *
FROM
    Foo
LEFT JOIN
    Bar
    ON Bar.Id = Foo.BarId
 The second most common type of join.
Right Join Right Join 
SELECT
    *
FROM
    Foo
RIGHT JOIN
    Bar
    ON Bar.Id = Foo.BarId
 Rare type of join.
Full Join (or Outer Join) Full Join 
SELECT
    *
FROM
    Foo
FULL JOIN
    Bar
    ON Bar.Id = Foo.BarId
FULL OUTER JOIN
 Rare type of join.
Left Excluding 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 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 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.
sql_cheat_sheet.1345943907.txt.gz · Last modified: 2017/01/01 19:52 (external edit)
Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki