T-SQL for the Absentminded DBA

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

CASE input_expression  WHEN when_expression THEN result_expression – … ELSE else_result_expression END  

CASE WHEN Boolean_expression THEN result_expression – … ELSE else_result_expression END

USE DBName;GOIF OBJECT_ID (N’dbo.ISOweek’, N’FN’) IS NOT NULL DROP FUNCTION dbo.ISOweek;GO

UPDATE Table1SET Table1.col1 = Table1.col1 + T2.col1FROM Table1 AS T2

CREATE FUNCTION dbo.fName (@DATE datetime)RETURNS intASBEGIN RETURN 100END;

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’

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!