Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

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:

formatsql

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.


Filed under: Blog Tagged: Red Gate, SQL Prompt, syndicated, T-SQL

Comments

Posted by Jess on 12 May 2011

I love SQL Prompt, but it's a bit too expensive for me. I've had to settle for www.devart.com/.../sqlcomplete .

Posted by jcrawf02 on 12 May 2011

Jess, try www.ssmstoolspack.com, free, and top notch.

Posted by Steve Jones on 12 May 2011

There are a few other tools out there. prompt is a little expensive by itself, but as part of the tools in one of the RG packs, it's worth it for your employer to get and save lots of time.

Hadn't seen SQLComplete before. How does it compare to native intellisense?

Posted by sheldonhull on 2 April 2012

*** WARNING: ***

I had js/agent.trojan notifications on  www DEVART DOT COM

I wouldn't go there.

Leave a Comment

Please register or log in to leave a comment.