Skip to content

A SQL Layout Standard

February 2, 2012

 A reminder for me which, hopefully, should help you as well when entering SQL code.

Why?! Because SQL which is EASY TO READ is much better than SQL which is not – Think about the person who has to decipher it later!

If it is easier (and it usually is) to simply “crash-touch-type-in” the code INITIALLY then that is OK … Buttt … Don’t forget to tidy-it up!

Either use a good formatting tool or follow the guidelines below.

If you “forget” to tidy-up then, hopefully, the code will be rejected at the “Code Review”.

Some general guidelines for before you get started:-

  1. if a standard already exists then follow it, even if it is different from this one
  2. if different standards exist (within the same application) then ask the Team Leader
  3. when updating existing code follow the existing standard (if one exists)

Indentation – I prefer using SPACES instead of the TAB character.

     SSMS -> Tools -> Options -> Transact-SQL -> Tabs -> TabSize=4 -> check: Insert Spaces —> TEST IT

IntelliSense – ensure it’s enabled

Then as you input the code:-

  1. All keywords e.g. SELECT should be input in uppercase
  2. Commas separating the columns should be at the START of each line, in column 4
  3. Use separate lines for each column
  4. Aliases for table column names should be spaced well apart e.g. starting in column 41 
  5. if possible, keep to within 90 columns – doing so should mean not having to scroll right too often

COMMENTS

  1. insert as many as you like providing each one is relevant
  2. insert some at the beginning (see example below)
  3. optionally, insert the Author’s name & date last modified – this helps Support staff

Finally, ask someone to do a “Code Review”.

An example:-

— This SQL analyses 3 MSDB tables to determine the longest running JOB STEPS

— Check that the WHERE clauses are not removing rows that you may actually require!

— Other tables which could be be useful:-

—    msdb.dbo.sysjobactivity (stores current execution activity)

—    msdb.dbo.sysjobstepslogs

— Author: Andrew Baker, Last Modified on: 2-Feb-2012

SELECT

    h.[server]                               AS HistoryServer

   ,s.step_name                         AS StepName

   ,h.run_date                            AS HistoryRunDate

   ,j.name                                     AS JobName

   —

   ,(  (h.run_duration/10000 *3600)

     + ((h.run_duration%10000)/100 *60)

     + ((h.run_duration%10000)%100)

    )

                                                        AS HistoryRunDurationInSeconds

   —  

   ,CONVERT(DATETIME, RTRIM(run_date)) +

    (  (run_time/10000 *3600)

     + ((run_time%10000)/100 *60)

     + ((run_time%10000)%100)

    ) / (23.999999*3600)                — seconds in a day

                                                        AS HistoryStartDateTime

   —

   ,h.run_status                        AS HistoryRunStatus

   ,s.step_id                                AS StepID

   ,s.subsystem                         AS StepSubsystem

   ,j.version_number             AS JobVersionNumber

   ,j.date_modified                  AS JobLastModifiedDate

   ,s.command                           AS StepCommand

FROM

    msdb.dbo.sysjobhistory AS h        — contains the detail for each Job Step execution

LEFT JOIN

    msdb.dbo.sysjobsteps AS s          — contains the STEPS for each Job   

    ON  h.job_id = s.job_id

    AND h.step_id = s.step_id

LEFT JOIN   

    msdb.dbo.sysjobs AS j              — contains the list of Jobs

    ON j.job_id = s.job_id

WHERE

    j.[enabled] = 1

AND h.run_status = 1

AND h.run_duration > 30 — seconds

ORDER BY

   s.step_name ASC

  ,h.run_date DESC

  ,h.run_duration DESC

Advertisements

From → Uncategorized

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: