Free Stuff Friday Eve

I actually have enough free stuff, I thought I'd share a day early.

Free advice: Here's a blog from MSSQLTips.com called Best Practice – Increase the Number of Error Logs.  I like it.  You're not wowed?

Well, how about “Who's Free DMV Monitoring Tool is Better?” by Kevin Kline? Read the blog – it's a short one – and check out the SQL DMV Stats and SQLStats2005 tools on CodePlex.com.

From the wonderful world of Twitter, we have a free book recommendation (hey, it's my blog, and I say advice and recommendations are free resources).  @jxlarrea says "I'm reading CODE by Charles Petzold (Microsoft Press). I just reached chapter 4 and the book is already extremely engaging!"  Full title is Code: The Hidden Language of Computer Hardware and Software, and it does sound interesting…

Twitter also bestows on us a blog by Brad M. McGehee (@bradmcgehee), with links to a Free SQL Server Dashboard. Check out the blog, and see SQLServerExamples.com for the dashboard.

Free ebooks? Oh yeah, we got free ebooks:

Do you have a free resource? Email me (jen@jennifermccown.com) or post in the comments. Your DBA brothers and sisters thank you.

-Jen McCown, http://www.MidnightDBA.com

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:

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.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!

JOINs, and the problem with being a MidnightDBA…

The real problem with being a MidnightDBA is midnight blogging.  It's a syndrome in which you find yourself with this idea, this great and very clever idea, that you want to share with all of blogdom. So you – let's be honest here – *I* start to write up my idea, and it turns out to be something blindingly, astoundingly ordinary and obvious.

 I lie. The REAL real problem with being a MidnightDBA is getting all of that, and then blogging about it anyway.

So recently I was coding for an unfamiliar system, and discovered this setup: there's a master configuration table that holds settings for other units in the database. Any time a new unit is inserted, you also have to insert settings to the config table, using the master config table as a template. Eh? An example, you say? Certainly!

Master configs:

  • Setting 1, blue
  • Setting 2, medium
  • Setting 3, round

Insert Unit 1 into the database. Use master configs as a template to insert Unit 1 local configs:

  • Unit 1, Setting 1, blue
  • Unit 1, Setting 2, medium
  • Unit 1, Setting 3, round

 You know, only slightly more complicated.  So if we have a set of units we need to insert, and a set of configs for each unit, we'll need a statement like this:

SELECT DISTINCT UnitID
INTO #tempUnits
FROM Unit

 

 

 

SELECT
C.OrgID
,C.ConfigID
,C.DefaultValue StartValue
,U.UnitID ID
,C.TypeID
,GETDATE() ModifiedDate
FROM Config C
JOIN #tempUnits U ON 1=1

The part I thought was super clever was that whole inner join on nothing thing.  As it turns out, that is kinda nifty, but it's also something very simple: a CROSS JOIN.  It makes sense: a cross join gives you a set of rows from Table2 for each row in Table1.  An INNER JOIN gives you all the rows from Table1 that match Table2, on the join condition. Since there's no join condition here, it just ..well let's not get repetitious…it just gives you a CROSS JOIN. 

Wasn't that fun? I gotta get some sleep…