syndicated – Page 818 – Voice of the DBA

Clean Code is Easier to Read – SQL Prompt

I saw a post recently that had query that looked like this:

select a.*,name, b.*
 from sys.database_principals a, sys.database_permissions b

where permission_name = 'INSERT'
and
b.grantee_principal_id = a.principal_id

Ugly to read, at least to me, and in a poorly written format. The table, table format isn’t ANSI compliant and isn’t recommended. So I did this:

A little better, and easier to read, but not great.

SELECT  a.* ,
        name ,
        b.*
FROM    sys.database_principals a ,
        sys.database_permissions b
WHERE   permission_name = 'INSERT'
        AND b.grantee_principal_id = a.principal_id

However now I can make a few quick edits. Remove the comma between tables and add “INNER JOIN” and then move the AND clause up to an ON clause to give me this:

SELECT  a.* ,
        name ,
        b.*
FROM    sys.database_principals a
  INNER JOIN sys.database_permissions b
    ON b.grantee_principal_id = a.principal_id
WHERE   permission_name = 'INSERT'

Much better, and easier to read.

May 12, 2011

Patch Week

My email account started getting notices of Windows patches yesterday, indicating it’s patch week again. If you manage Windows devices, be sure you are aware of the patches that came out. The May Bulletin on Technet shows 2 issues, one critical, one important.

I tend to apply patches late, probably a month after release, just to see if anyone reports an issue. It’s up to you, but be aware of the patches, and if you could be affected, make sure you test machines and then schedule the downtime.

May 11, 2011

Networking and Connections

This quote says it all:

It’s from an article on the 9/11 memorial and how an algorithm was used to inscribe the names in particular places.

That could easily be applied to your career as well. The connections you have, with people that know you, respect you, like your work, like you, etc. are the most important things you have.

Your skills matter, but the skills help build those connections.

Learn to network.

Cross posted to The Modern Resume

May 11, 2011

T-SQL Tuesday #18 – My CTE

It’s time for T-SQL Tuesday again, and it’s number 18. Hard to believe it’s been a year and a half since Adam Machanic (blog | @AdamMachanic) thought of the idea. I’ve participated in most and it’s something I look forward to each month. This month Bob Pusateri hosts the party with the theme of CTEs. No, it’s not thermal unit of expansion, and I hated chemistry.

My CTE

When CTEs were introduced, I thought they were a great idea. They made it much easier to write complicated queries that might need derived tables. In the past, writing something like this was hard to read.

SELECT p.Class, p.Color, p.DaysToManufacture, p.ListPrice
 FROM Production.Product p
  INNER JOIN ( SELECT ph.ProductID, ph.StandardCost, pri.Quantity
                 FROM Production.ProductCostHistory ph
                   INNER JOIN Production.ProductInventory pri
                     ON ph.ProductID = pri.ProductID
                WHERE StandardCost > 10
             ) b
    ON p.ProductID = b.ProductID
  INNER JOIN Production.ProductInventory pi ON p.ProductID = pi.ProductID
  WHERE p.Color IS NULL AND p.DiscontinuedDate IS NULL

A CTE can make this much easier to keep track of, especially in places where you don’t want to create a view instead.

WITH ProductCTE
AS ( SELECT ph.ProductID, ph.StandardCost, pri.Quantity
                 FROM Production.ProductCostHistory ph
                   INNER JOIN Production.ProductInventory pri
                     ON ph.ProductID = pri.ProductID
                WHERE StandardCost > 10
) SELECT p.Class, p.Color, p.DaysToManufacture, p.ListPrice
 FROM Production.Product p
  INNER JOIN ProductCTE b
    ON p.ProductID = b.ProductID
  INNER JOIN Production.ProductInventory pi ON p.ProductID = pi.ProductID
  WHERE p.Color IS NULL AND p.DiscontinuedDate IS NULL

I know this isn’t a great example, but by moving subqueries to a CTE structure, the end query is easier to debug and read.

Top X of a Group

Suppose you have a small result set of something like this.

CREATE TABLE Books
( BookID INT IDENTITY(1,1) , BookName VARCHAR(200) , Genre VARCHAR(50) , reads INT ) go INSERT Books SELECT 'Old Man''s War', 'Sci-Fi', 200
INSERT Books SELECT 'Ender''s Game', 'Sci-Fi', 345
INSERT Books SELECT 'Red Thunder', 'Sci-Fi', 143
INSERT Books SELECT 'Quarter Share', 'Sci-Fi', 25
INSERT books SELECT 'The Enemy', 'Thriller', 67
INSERT books SELECT 'The Hunt for Red October', 'Thriller', 678
INSERT books SELECT 'Bad Luck and Trouble', 'Thriller', 545
INSERT books SELECT 'Game of Lions', 'History', 644
INSERT books SELECT 'The Rise of Theodore Roosevelt ', 'History', 67
INSERT books SELECT 'An American Life: The Autobiography', 'History', 267

Suppose I wanted to top two books from each genre, ranked by reads. A TOP 2 won’t work because that doesn’t allow you to specify groups. However using ROW_NUMBER and an OVER clause in a CTE, this becomes an easy query.

WITH BookRanks AS ( SELECT b.BookID
      , b.BookName
      , b.Genre
      , b.reads
      , ROW_NUMBER() OVER (PARTITION BY b.genre ORDER BY reads DESC) AS Counter FROM Books b
 ) SELECT bookID
      , Genre
      , reads
      , bookname
  from BookRanks
   WHERE counter <= 2

That gives me an easy to read result set:

bookID Genre reads bookname

——- ——– —– ———————————————————-

8 History 644 Game of Lions

10 History 267 An American Life: The Autobiography

2 Sci-Fi 345 Ender’s Game

1 Sci-Fi 200 Old Man’s War

6 Thriller 678 The Hunt for Red October

7 Thriller 545 Bad Luck and Trouble

Older T-SQL Tuesday Topics

Just a quick list of the past topics and the roundups.

May 10, 2011