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

Data Adventures

My personal journey in an intricate world of data and continuous effort to make it more structured and well understood can be found in this blog.

I live and work in Canada - see my profile on LinkedIn.

And I wonder, where that column is?

I wouldn't have decided to write this post if I didn't see my boss at one of my previous jobs being really persistent and careful in his attempts to write his TSQL code. It started when I heard him pressing space-bar at his keyboard too much, I came to his desk and saw him lining up all of his select statement columns with the equal numbers of space characters before comma delimiter and actual column names, like this:

Select Column1
     , Column2
     , Column3
       …
     , ColumnN
From TableA


I questioned him on his tedious work and he simply explained that in some text editors default tab characters are shown differently (sometimes it’s expanded to several space characters and sometimes it’s being shown as a single one). And in this case our SQL code may look like this:

Select Column1 
, Column2 
     , Column3 
       … 
 , ColumnN 
From TableA

It’s still a working code, however it will take more time to read and comprehend its scope, different elements joined by various database object, calculated or concatenated column aliases, etc. That whole example of orderly space characters from my boss had made a final impression on me about a need for a good written code which could be easily read by others.

There are many ways to make your TSQL code look more readable, several free add-ons and plugins exists for SSMS. However I would like to emphasize a big value of where you manualy place your column aliases within you SQL code.

Let's take an example of the [dbo].[vTimeSeries] view from the [AdventureWorksDW2012] SQL Server sample database, more specifically the first section of the select statement with all the columns listed:

















and now compare the same code with this:
















where I can easily identify column definition for TimeIndex, ReportingDate and other columns. It's basically all about how we like putting column alias, one way expression [AS] column_alias or another column_alias = expression.

I personally like using the second approach; even if someone may say that it mixes result set column naming with variable assignment. I just like it because it saves me time to locate element names within the code and then I can easily find the answer to the original question on where that column is :-)

Enjoy your day and happy data adventures!

Comments

Leave a comment on the original post [datanrg.blogspot.com, opens in a new window]

Loading comments...