Category Archives: Tips

Compare column names for case sensitivity

I’m reviewing the code for the upcoming Minion CheckDB, and one of the things we’re checking for is case consistency in column names. For example, if Table1 has a column named Col1, and Table2 has COL1, well that’s no good.

But, how do we easily find those mismatches on a system that’s not case sensitive? Easy: collations.

This query compares all columns with the same name (speaking case insensitively) for case equality:

SELECT OBJECT_NAME(c1.object_id) AS TableName1
, OBJECT_NAME(c.object_id) AS TableName2
, c1.name AS ColName1
, c.name AS ColName2
FROM sys.columns AS c1
INNER JOIN sys.columns AS c ON c1.object_id > c.object_id
WHERE UPPER(c1.name) = UPPER(c.name)
AND c1.name COLLATE Latin1_General_CS_AS <> c.name COLLATE Latin1_General_CS_AS
ORDER BY ColName1
, TableName1
, ColName2;

Notice that we’re joining on c1’s object_id GREATER THAN c’s object_id. If we did <> (not equals), then we’d double our results (we’d see T1 C1 | T2 c1, and another row for T2 c1 | T1 C1).

We also have, in the where clause, UPPER(c1.name) = UPPER(c.name). We want column names that match, except for case.

And the “except for case” part comes with collating that last AND with a case sensitive collation: Latin1_General_CS_AS.

Easy. Done. Off you go.

-Jen

An MCM teaches COMMENTS at SQL Saturday OKC

Comments

Edit: SQL Sat OKC has come and gone, but the new session on comments is recorded and up on the MidnightDBA Events page! (Direct link to WMV.)

Tomorrow (as of the blog’s publish date) at8:30am, I will be teaching “T-SQL’s Hidden Support Feature” at SQL Saturday Oklahoma City (totally free, did you know? Free IT training, y’all.)

I said in a recent blog about comments that this session started in my head years ago, and launched a few weeks ago when I was double-dog-dared to write it by Oklahoma City user group members.

I wrote the session in about an hour. It was that fully formed already.

WHY ON EARTH am I talking about comments, of all things? How too, too common, dahling.  Simply this: Comments give you the biggest possible ROI for code support.  Come by tomorrow (or see the recording afterward), and I’ll tell you why and how. 

See me, and maybe a couple of dozen other speakers, teaching all the things SQL Saturday OKC tomorrow.

-Jen

 

Easy SQL mistakes: Accidental Correlated Subquery

Just a fun note, because it was a moment of pure puzzlement for me. I’m writing a new SP for Minion Backup (you knew it had to be Minion related, didn’t you?) and I just got the oddest error. Here’s how it went.
unsplash_spiral

First, I got a list of backups from the log:

SELECT ExecutionDateTime
    , Status
    , PctComplete
    , DBName
    , DBType
    , BackupType
    , BackupStartDateTime
    , BackupEndDateTime
 FROM Minion.BackupLogDetails;

But I wanted to limit the resulting list based on an existing temporary table, so I joined them, adding simple table aliases as I went (“D” and “B”):

SELECT D.ExecutionDateTime
    , D.Status
    , D.PctComplete
    , D.DBName
    , D.DBType
    , D.BackupType
    , D.BackupStartDateTime
    , D.BackupEndDateTime
 FROM Minion.BackupLogDetails as D
 JOIN #Backups as B ON D.DBName = B.DBName;

But the important thing was, I wanted this new result set to be of backups PRIOR TO the most recent backup set. That’s easy, I’ll just get the max date from BackupLogDetails, and pull a less than:

SELECT D.ExecutionDateTime
    , D.Status
    , D.PctComplete
    , D.DBName
    , D.DBType
    , D.BackupType
    , D.BackupStartDateTime
    , D.BackupEndDateTime
 FROM Minion.BackupLogDetails as D
 JOIN #Backups as B ON D.DBName = B.DBName
 WHERE D.ExecutionDateTime < ( SELECT   MAX(D.ExecutionDateTime)
    FROM Minion.BackupLogDetails
 );

Aaaaand then I get the weird error:

Msg 147, Level 15, State 1, Line 50
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

What the what?? I literally JUST ran a query exactly like this, but without the join. I haven’t mixed aggregate and non-aggregate columns in the query without a GROUP BY…the only aggregate is in the subquery, and it’s all by its little lonesome!

Well, you likely read the blog title, so it’s no mystery to you: by adding that table alias, “D.”, to the subquery, I accidentally made this a correlated subquery…the thing was now trying to pull MAX(ExecutionDateTime) from that outer table, instead of the inner table.  That’s not at ALL what I wanted to do!

The fix here is easy – just take out that table alias from the subquery:

  SELECT D.ExecutionDateTime
    , D.STATUS
    , D.PctComplete
    , D.DBName
    , D.DBType
    , D.BackupType
    , D.BackupStartDateTime
    , D.BackupEndDateTime
 FROM Minion.BackupLogDetails as D
 JOIN #Backups as B
 ON D.DBName = B.DBName
 WHERE D.ExecutionDateTime < ( SELECT MAX(ExecutionDateTime)
      FROM Minion.BackupLogDetails );

If you wanted to be really really pedantic, you could of course add a new table alias to the subquery table – say, “sub” – and then select MAX(sub.ExecutionDateTime), etc etc. It’s all up to you. Just, you know, don’t accidentally correlate when you didn’t meant to!

Happy days,
Jen McCown
http://www.midnightdba.com/Jen/