Background

As a Database Administrator get so see a bit of SQL Statements.

Avenues

The SQL gets to the DBA through various avenues, inclusive :-

1. Developers
   • Submitted to review and execute against Production Database
2. Monitoring Tools

In quite a few cases, the first thing one has to do is space things out a bit.

SQL

Original

Here is our original SQL.

SELECT            dbo.lms_emp_aff_x.aff_netid,            dbo.lms_emp_aff_x.aff_loc,            dbo.lms_emp_aff_x.aff_emp_unique_loc_id,            'A',            @currdate,            ' '        FROM dbo.lms_emp_aff_x        WHERE dbo.lms_emp_aff_x.aff_loc = @loc_passed AND dbo.lms_emp_aff_x.aff_emp_unique_loc_id NOT IN            (              SELECT location_uid.lu_unique_loc_id              FROM location_uid              WHERE location_uid.lu_loc = @loc_passed AND location_uid.lu_record_type = 'A'           )

SQL Beautifiers

Outline

1. EverSQL
   • EverSQL Query Formatter
     Link
2. Free Formatter
   • SQL
     Link
3. Poor SQL
   • Poor SQL
     Link
4. Devart
   • SQL Formatter for SQL Server
     Link
5. Dan’s Tools
   • sql beautifier
     Link

Choices

EverSQL

Revised

Image

Code

SELECT
  dbo.lms_emp_aff_x.aff_netid,
  dbo.lms_emp_aff_x.aff_loc,
  dbo.lms_emp_aff_x.aff_emp_unique_loc_id,
  'A',
  @currdate,
  ' '
FROM
  dbo.lms_emp_aff_x
WHERE
  dbo.lms_emp_aff_x.aff_loc = @loc_passed
  AND dbo.lms_emp_aff_x.aff_emp_unique_loc_id NOT IN (
    SELECT
      location_uid.lu_unique_loc_id
    FROM
      location_uid
    WHERE
      location_uid.lu_loc = @loc_passed
      AND location_uid.lu_record_type = 'A'
  )

Free Formatter

Revised

Image

Code

SELECT
   dbo.lms_emp_aff_x.aff_netid,
   dbo.lms_emp_aff_x.aff_loc,
   dbo.lms_emp_aff_x.aff_emp_unique_loc_id,
   'A',
   @currdate,
   ' '
FROM
   dbo.lms_emp_aff_x
WHERE
   dbo.lms_emp_aff_x.aff_loc = @loc_passed
   AND dbo.lms_emp_aff_x.aff_emp_unique_loc_id NOT IN
   (
      SELECT
         location_uid.lu_unique_loc_id
      FROM
         location_uid
      WHERE
         location_uid.lu_loc = @loc_passed
         AND location_uid.lu_record_type = 'A'
   )

Poor SQL

Revised

Image

Code

SELECT dbo.lms_emp_aff_x.aff_netid
	,dbo.lms_emp_aff_x.aff_loc
	,dbo.lms_emp_aff_x.aff_emp_unique_loc_id
	,'A'
	,@currdate
	,' '
FROM dbo.lms_emp_aff_x
WHERE dbo.lms_emp_aff_x.aff_loc = @loc_passed
	AND dbo.lms_emp_aff_x.aff_emp_unique_loc_id NOT IN (
		SELECT location_uid.lu_unique_loc_id
		FROM location_uid
		WHERE location_uid.lu_loc = @loc_passed
			AND location_uid.lu_record_type = 'A'
		)

Devart / SQL Formatter for SQL Server

Revised

Image

Code

SELECT
  dbo.lms_emp_aff_x.aff_netid,
  dbo.lms_emp_aff_x.aff_loc,
  dbo.lms_emp_aff_x.aff_emp_unique_loc_id,
  'A',
  @currdate,
  ' '
FROM dbo.lms_emp_aff_x
WHERE dbo.lms_emp_aff_x.aff_loc = @loc_passed
AND dbo.lms_emp_aff_x.aff_emp_unique_loc_id NOT IN (SELECT
  location_uid.lu_unique_loc_id
FROM location_uid
WHERE location_uid.lu_loc = @loc_passed
AND location_uid.lu_record_type = 'A')

Dan’s Tools – cleancss

Revised

Image

Code

SELECT dbo.lms_emp_aff_x.aff_netid, dbo.lms_emp_aff_x.aff_loc, dbo.lms_emp_aff_x.aff_emp_unique_loc_id, 'A', @currdate, ' '
FROM dbo.lms_emp_aff_x WHERE dbo.lms_emp_aff_x.aff_loc = @loc_passed AND dbo.lms_emp_aff_x.aff_emp_unique_loc_id NOT IN(SELECT location_uid.lu_unique_loc_id FROM location_uid WHERE location_uid.lu_loc = @loc_passed AND location_uid.lu_record_type = 'A')

Summary

In our current short list, here are losers and winners.

Losers :-

1. Dan’s Tool
   • Clean CSS
     • Not SQL focus

Winners :-

1. EverSQL
   • EverSQL Query Formatter
2. Free Formatter
   • SQL
3. Poor SQL