Archives

My Way of Coding T-SQL Consistently

As I mentioned in the last article, you need to develop or adopt a standardized way of writing T-SQL code. In this article, I’m going to share the habits I’ve developed, “my way” if you will.

Vizzini: Finish him. Finish him, your way.
Fezzik: Oh good, my way. Thank you Vizzini… what’s my way?
Vizzini: Pick up one of those rocks, get behind a boulder, in a few minutes the man in black will come running around the bend, the minute his head is in view, hit it with the rock.
Fezzik: My way’s not very sportsman-like.
- The Princess Bride

Just like Fezzik, you may be asking, “Well, which way is my way?” The best way for me to help you with that is to share the method(s) I’ve developed and reasons for doing so. Keep in mind that many people will disagree with my way, and that’s alright. This article however, is all about me, and since these methods work for me, I think they’ll work for you too. Adoption of a 3rd party query tool helps to keep your code consistent.

Capitalization Usage

I write all of the T-SQL functions in ALL CAPS and typically CamelCase field and database names. More on this later.

Field Order

I list the fields in an order which makes sense to me. Consider the script I shared with you in the last article:

SELECT  fcompany, fcustno, fcustpono, forderdate, fsono, fsorev FROM somast

While the computer doesn’t care, I list my fields in roughly most general to most specific order. Therefore, I would order those in the following manner:

SELECT  fcustno, fcompany, fsono, fsorev, fcustpono, forderdate  FROM somast

This order just “feels” right to me. Customers “are larger” or less specific than Sales Orders and the fields are listed in this order. This will become a lot more important when we discuss joining tables.

I use a lot of vertical space in my queries. The query above becomes this:

SELECT fcustno,
       fcompany,
       fsono,
       fsorev,
       fcustpono,
       forderdate
  FROM somast

Once again, the computer couldn’t care less, it just makes everything easier for me to read.

Leading vs. Trailing Commas

I prefer to use leading commas. The query now looks like this with leading commas:

SELECT fcustno
 ,fcompany
 ,fsono
 ,fsorev
 ,fcustpono
 ,forderdate
FROM
  somast

It may take you awhile to get used to seeing the query like that, but there’s a good reason for doing so. If you are editing a long query with trailing commas, and you need to remove the last entry in the list, you will invariably forget to remove the final comma and receive an error.

SELECT fcustno,
       fcompany,
       fsono,
       fsorev,
       fcustpono,
  --     forderdate  **** Using Double dashes comments out this line. This code now ends with a comma and will error out. 
  FROM somast

Leading comma queries are easier to edit in my opinion.

Aliasing

Butch: Would you hand me a dry towel, miss beautiful tulip?
Fabienne: Oh, I like that. I like being called a tulip. Tulip is much better than mongoloid.
-Pulp Fiction

Aliasing is used in two places in a SQL Statement, the Select clause and the From clause. The From clause alias renames the database object and I typically use 3 or 4 character alias names. I also include the database owner or schema as well. Alias names can be assigned following the AS keyword, but it is optional. For example, the leading comma query becomes this:

SELECT SOM.fcustno
 ,SOM.fcompany
 ,SOM.fsono
 ,SOM.fsorev
 ,SOM.fcustpono
 ,SOM.forderdate
FROM
  dbo.somast AS SOM  -- Or dbo.somast SOM

I know that seems like extra work, but its absolutely necessary when you write multiple table queries. Some people alias their tables with meaningless single letter values like A, B, and C. I can’t stand this because when I read a SQL statement aliased in this way, I have to constantly look at the from clause to remind myself what each alias represents.

Once again, consistency is key. I always use the following alias names:

somast = SOM
soitem = SOI
sorels = SOR
slcdpm = SLC
etc.

Since I am consistent, I always know that a field aliased with ARI represents Aritem; and I can cut and paste between queries as well. Incidentally, FastForms adds _EXT to the name of it’s extension tables.. Therefore, I add a lowercase e to any extension table alias. Soitem_EXT becomes SOIe. Keep it simple.

The second alias type applies to the field name. What the heck does fsono mean to a user? Therefore, I alias my queries like this:

SELECT SOM.fcustno CustNo
 ,SOM.fcompany CustName
 ,SOM.fsono SONo
 ,SOM.fsorev SORev
 ,SOM.fcustpono CustPONo
 ,SOM.forderdate OrderDate
FROM
  dbo.somast SOM

Much Easier to Read


Also, because I’ve aliased my table names in CamelCase, many query tools such as SSRS will automatically format them as two words. “OrderDate” becomes “Order Date”. You can include spaces in your alias names, but you must put brackets around them. [Order Date] will render properly in almost any reporting tool, but I dislike all the extra typing.

Tomorrow, I’ll post my first video which will be a short demonstration of how I follow these “rules” with Toad for SQL Server.

Related posts:

6 comments to T-SQL Basics – My Way of Coding Consistently

  • Great post. Very similar to the approaches I try to follow. It makes a huge difference in readability for everyone involved if you have a consistent approach to this.

  • [...] This post was mentioned on Twitter by Grant Fritchey and Stray__Cat, David Stein. David Stein said: Blog: My Way of Coding T-SQL Consistently As I mentioned in the last art http://bit.ly/edwflp [...]

  • Good advice, David. I used to dislike the sight of leading commas until trailing commas made commenting a pain, like you said. Short aliases are great too. I was looking at a RS book that aliased the AW Person.Address as “BillToAddress.” and then wrote that alias repeatedly for each column heading. Can’t say I’d want to do it that way when “bta.” is much simpler.

  • Thanks guys. Leading commas do take a bit to get used to, but the cut and paste bonus can’t be denied.

  • Wonderful article David! I have always used trailing commas and have run into the same issue of editing where I forget to remove that last comma. I never thought to use leading commas. It looks funny to me, but I am going to have to give it a whirl!

  • LWatters

    A simple modifcation to the stored procedure sp_help in the “master” database can display all the fields of a table with a prefixed comma – again great for cut & paste.

    The SQA command is: EXEC sp_helpc
    Example: EXEC sp_help SORELS
    returns:

    ,fenumber
    ,finumber
    ,fpartno
    ,fpartrev
    ,frelease
    ,fshptoaddr
    ,fsono
    .etc.

    Create a New Stored Procedure in Master and paste this in:

    CREATE proc sp_helpc
    @objname nvarchar(776) = NULL – object name we’re after
    as
    – PRELIMINARY
    set nocount on
    declare @dbname sysname

    – OBTAIN DISPLAY STRINGS FROM spt_values UP FRONT –
    declare @no varchar(35), @yes varchar(35), @none varchar(35)
    select @no = name from master.dbo.spt_values where type = ‘B’ and number = 0
    select @yes = name from master.dbo.spt_values where type = ‘B’ and number = 1
    select @none = name from master.dbo.spt_values where type = ‘B’ and number = 2

    – Make sure the @objname is local to the current database.
    select @dbname = parsename(@objname,3)

    if @dbname is not null and @dbname db_name()
    begin
    raiserror(15250,-1,-1)
    return(1)
    end

    – @objname must be either sysobjects or systypes: first look in sysobjects
    declare @objid int
    declare @sysobj_type char(2)
    select @objid = id, @sysobj_type = xtype from sysobjects where id = object_id(@objname)

    print ‘ ‘

    – DISPLAY COLUMN IF TABLE / VIEW
    if @sysobj_type in (‘S ‘,’U ‘,’V ‘,’TF’,’IF’)
    begin

    – SET UP NUMERIC TYPES: THESE WILL HAVE NON-BLANK PREC/SCALE
    declare @numtypes nvarchar(80)
    select @numtypes = N’tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney’

    – INFO FOR EACH COLUMN
    print ‘ ‘
    select
    ‘Column_name’ =’,’+ name,
    ‘Type’ = type_name(xusertype),
    ‘Length’ = convert(int, length),
    ‘Prec’ = case when charindex(type_name(xtype), @numtypes) > 0
    then convert(char(5),ColumnProperty(id, name, ‘precision’))
    else ‘ ‘ end,
    ‘Scale’ = case when charindex(type_name(xtype), @numtypes) > 0
    then convert(char(5),OdbcScale(xtype,xscale))
    else ‘ ‘ end
    from syscolumns where id = @objid and number = 0 order by colid

    end

    return (0) — sp_helpc
    GO

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>