Home » sql server »sqlserverpedia-syndication »TSQL » Currently Reading:

T-SQL for the Absentminded DBA

September 22, 2009 sql server, sqlserverpedia-syndication, TSQL 4 Comments

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:

The Absentminded DBA’s Cheat Sheet

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.EmployeeID
AND 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 myTable
OPEN 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!

Currently there are "4 comments" on this Article:

  1. [...] This post was mentioned on Twitter by Jen & Sean McCown, John. John said: Excellent, Thanks! RT @MidnightDBA: Here's our cheatsheet blog post http://bit.ly/cSPewm [...]

  2. [...] mentioned before that I’m something of an absentminded DBA – I know where to find information, even if I can’t keep it in my head all the time. One of [...]

  3. Joel Schibbelhute says:

    Most excellent. Thanks for this.

Comment on this Article:







MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Where are We?

September 13: SQL Saturday Kansas City
November 3-7: PASS Summit, Seattle, WADecember 11: Presenting "Powershell Cmdlets.." at Alaska SQL Server User Group

Blog Posts by Category

DBAs@Midnight

How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!

DBARoadmap.com

The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/