Edit: Now with working links!
I was inspired a couple of days ago by Pinal Dave on SQL Server Central; he put up a TSQL Cheat Sheet that’s lovely and convenient. But my thing is, I’m a bit past the basic datatypes and aggregate functions. Okay, I’m not past ALL of them, but still…
What I really need is a cheat sheet for all the stuff I use about all the time, but can’t seem to write without looking it up. Like CASE statements. I’ve programmed in so many languages (“What Jen, like two?” Hush up, you.) and each one has a slightly different CASE, I never remember the right format.
Tah-dah! Here’s the Absentminded DBA’s Cheat Sheet for IntermediateTSQL (PDF format)!
I fully expect I’ll come up with another one soon; there’s just too much code that’s easy to forget.
For your convenience:
Intermediate TSQL, by MidnightDBA.com
Simple CASE
CASE input_expression WHEN when_expression THEN result_expression — … ELSE else_result_expression END
Searched CASE
CASE WHEN Boolean_expression THEN result_expression — … ELSE else_result_expression END
Drop if exists
USE DBName;GOIF OBJECT_ID (N’dbo.ISOweek’, N’FN’) IS NOT NULL DROP FUNCTION dbo.ISOweek;GO
UPDATE…FROM
UPDATE Table1SET Table1.col1 = Table1.col1 + T2.col1FROM Table1 AS T2
Create Function
CREATE FUNCTION dbo.fName (@DATE datetime)RETURNS intASBEGIN RETURN 100END;
CTEs
WITH DirectReports ( ManagerID, DeptID ) AS ( SELECT e.ManagerID , edh.DepartmentID FROM HumanResources.Employee AS eINNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeIDAND edh.EndDate IS NULLWHERE ManagerID IS NULL ) SELECT ManagerID , DeptID FROM DirectReportsINNER JOIN HumanResources.Department AS dp ON DirectReports.DeptID = dp.DepartmentID WHERE dp.GroupName = N’Research and Development’
CURSOR
DECLARE @myID INT, @myName NVARCHAR(50)DECLARE myCursor CURSOR FOR
SELECT myID, [NAME] FROM myTableOPEN myCursorFETCH NEXT FROM myCursor INTO @myID, @myNameWHILE @@FETCH_STATUS = 0
BEGIN
— do stuff with @myID, @myName
FETCH NEXT FROM myCursor INTO @myID, @myName
END
CLOSE myCursor
DEALLOCATE myCursor
Edit: If you like this, you may also like my Forgotten T-SQL Cheat Sheet, which comes with a blog, the code and the 24HOP session recording!
Furthermore, I want you to know that Sean and I host a weekly webshow Fridays at 11pm CST. You should come and see the live show on http://bit.ly/DBAShow, or check out the recordings at MidnightDBA.com!
Pingback: Tweets that mention SQL Awesomesauce » Blog Archive » T-SQL for the Absentminded DBA -- Topsy.com
Pingback: SQL Awesomesauce » Blog Archive » SQL Trace Events Reference
Pingback: SQL Awesomesauce » Blog Archive » Learn T-SQL in 5 Minutes
Most excellent. Thanks for this.