Features I REALLY want in TSQL, Part #2

This next feature isn’t exactly a TSQL feature, and it is not highly visionary, but after spending literally the past eight (8) hours (I kid you not) using SQL tools to manipulate data and evolve some table structures, I remembered it is something I really would like to see.

What I’d like to see added are Default Aliases for tables and fields.

To illustrate the value this feature would provide, let’s use a simple query. The following query is one take verbatim from SQL Enterprise Manager after clicking on tables and fields:

SELECT     dbo.tblVendor.Name, dbo.tblProduct.Name AS Expr1, dbo.tblItem.Name AS Expr2

FROM         dbo.tblVendor INNER JOIN

                      dbo.tblProduct ON dbo.tblVendor.ID = dbo.tblProduct.VendorID INNER JOIN

                      dbo.tblItem ON dbo.tblProduct.ID = dbo.tblItem.ProductID

Before using the query I always have to edit it. I start by giving each table a simple alias to clean up the query:

SELECT     v.Name, p.Name AS Expr1, i.Name AS Expr2

FROM         dbo.tblVendor v INNER JOIN

                      dbo.tblProduct p ON v.ID = p.VendorID INNER JOIN

                      dbo.tblItem i ON p.ID = i.ProductID

Next I give the fields meaningful aliases instead of those annoying “ExprN” aliases:

SELECT     v.Name AS VendorName, p.Name AS ProductName, i.Name AS ItemName

FROM         dbo.tblVendor v INNER JOIN

                      dbo.tblProduct p ON v.ID = p.VendorID INNER JOIN

                      dbo.tblItem i ON p.ID = i.ProductID

Lastly I typically copy to SQL Query Analyzer and reformat so I can actually read and understand the query, and I also prefix the tables with database names for readability and maintainability assuming I plan to save the query in a stored proc, view, or function:

SELECT

   v.Name AS VendorName,

   p.Name AS ProductName,

   i.Name AS ItemName

FROM

   Products..tblVendor v

   INNER JOIN Products..tblProduct p ON v.ID = p.VendorID

   INNER JOIN Products..dbo.tblItem i ON p.ID = i.ProductID

Whew! That’s an awful lot of time spent just to get a clean workable query. Yes I know, some people say I am too anal, but my brain can’t process much at any one time so I always have to simplify and clean things up like this. Anyway, if SQL Server had default aliases for fields and tables and a few configuration options, a future SQL IDE could automagically add all my table and field aliases and database prefixes.

Now I know the first comment I’ll get will be someone saying: “But your suggestions makes no sense because you can’t be sure not to have potential conflicts among your tables and fields such as when you have a tblItem and tblInvoice and you set each to use “i” as the default alias.” To which my reply will be “So what? I’m proposing default aliases for fields and tables as “hints” for SQL IDEs, not as something people would depend on for robustness! “

I would envision a future SQL IDE would have a set of configuration options something like this (my configuration options imply multiple default aliases for tables and fields that could be used in case of conflict with the primary default alias):

• Always alias tables with their default alias:
  
  
  
  • [  ] Yes
    
  • [  ] No
  
  
• How to handle table alias conflicts
  
  
  
  • [  ] Append a numeral: i.e. “i”, “i1”, “i2”, etc.
    
  • [  ] Use secondary default: i.e. try “i” then try “inv”, etc.
    
  • [  ] Ask Me
  
  
• Default field aliases
  
  
  
  • [  ] Always alias fields
    
  • [  ] Only alias fields when field conflicts with another field
  
  
• How to handle field alias conflicts
  
  
  
  • [  ] Append a numeral: i.e. “i”, “i1”, “i2”, etc.
    
  • [  ] Use secondary default: i.e. try “ItemName” then try “AlternateItemName”, etc.
    
  • [  ] Ask Me
  
  
• Always prefix tables with database names
  
  
  
  • [  ] Yes
    
  • [  ] No

A really saavy Microsoft usability tester looking over my shoulder as I worked with SQL day in and day out would come up with the set of features I describe here because it would really reduce the amount of time I have to spend doing rote tasks in SQL.  Beyond that, I’d love to see configuration options that specify how to format SQL statements, but that’s beyond the scope of anything I’d like to post today.