Joe Celko created the masterpiece entitled Ten Things I Hate About You over a decade ago, and to this day I still enjoy reading his effort. (I should have pinged Ol' Joe at SQLSaturday #35 in Dallas last Month but I was busy and he looked busy, both of us preparing for our sessions). This is a classic piece of work, and while chatting with a friend the other day on the many things that are pet peeves to us these days, I decided to create a newly-updated list of TSQL related things that we run across that really bother us in the spirit of Joe’s work. So, I came up with Ten Things I Hate About You - Revised for 2010.
Working for a large consulting company, I have the opportunity to see all kinds of garbage everywhere that I go. Bad code, good code, mostly bad code I guess. Unfortunately, refactoring all of that old code would be a major undertaking for a company, would not be cost-effect, and therefore rewriting any of it would be a non-option. So, they’re basically stuck with this crap forever. Check out the list of these ten items and see if you don’t agree with me that TSQL would be much better off without. I list them in descending order for fun.
10. Column Names with type references
Do we need to know that a street number is an integer by the name? I wonder if someone has a decimal street number, such as 100.548734540 West Main St. If it’s 1-A Main St then it needs to be a varchar anyway. And even then, do we need to know the type to be tied to the name of the entity? What if the type had to change - do we change the references to the column in all of our code? Uh, no.
DECLARE @iStreetNumber int
9. Joins with the ON clause in parenthesis
Totally not needed and if you need to change the join while debugging or only want to highlight a part of it you have to remember to remove the beginning parenthesis. PITA.
INNER JOIN Product_Dimension b
ON (a.Invoice_number = convert(varchar(8),@InvoiceNumber)
AND a.product = b.Product
AND b.Product_NDC LIKE 'M0%'
AND a.Product LIKE 'M0%')
8. Tables prefixed with tbl
I’m certainly glad that these are prefixed with tbl, so I know for sure that it’s a table. Wrong. While vw_MyView is OK for views, dbo.udf_ is fine for functions, tbl_tablename is a bit too strange for tables.
CREATE TABLE tbl_tablename (ID int)
GO
CREATE TABLE tblCustomers (iCustID int)
7. Cursors
I do admit that WHERE CURRENT OF is pretty slick. Nonetheless, I have not written a cursor for a production implementation in several years. If cursors were deprecated I probably wouldn't miss them a great deal.
6. sp_
We’re still writing stored procedures using the sp_ prefix; I see them everywhere that I go, and I’m not talking about old procs from 10 years ago. New ones as well. Apparently we haven’t learned yet that we’re not supposed to name them in this fashion.
CREATE PROCEDURE sp_MyCoolProcRulz
5. GOTO
Can you believe that this is actually in the latest version of Books Online? Wow. Very painful to read. Look, while I geek with stuff and use GOTO to load tables or do whatever, I’d never put that in any code at any client in a production setting. Never.
DECLARE @Counter int;
SET @Counter = 1;
WHILE @Counter < 10
BEGIN
SELECT @Counter
SET @Counter = @Counter + 1
IF @Counter = 4 GOTO Branch_One --Jumps to the first branch.
IF @Counter = 5 GOTO Branch_Two --This will never execute.
END
Branch_One:
SELECT 'Jumping To Branch One.'
GOTO Branch_Three; --This will prevent Branch_Two from executing.
Branch_Two:
SELECT 'Jumping To Branch Two.'
Branch_Three:
SELECT 'Jumping To Branch Three.'
4. Return -1 (or return anything for that matter)
In programming, a method will have a return statement, unless it’s a VOID method as we all know. Carrying over this syntax to a stored procedure, and using it in multiple locations is a bad idea. Causes confusion and most of the time isn’t even captured by the client or middle-layer/business logic layer.
RETURN -1
IF @VAL IS NULL
RETURN -1
ELSE
RETURN -2
3. Columns for DML written left to right (out to the side) rather than columnar.
Let me ask you a question - which of the two below would you rather read and code to? I’ll give you zero number of guesses as to which one I prefer. With an insert statement, you have to painfully count ordinal position to match these up, but in a column-like manner you can usually line them up visually. Just the other day, I was counting, trying to figure out which column was inserted where. WTH?
SELECT MARKET_CODE, COMPANY_CODE, PRODUCT, YEAR, MONTH, DISPLAY_STATUS, LAST_UPDATE, SA_QTY,
SA_USD, SA_LC, CP_QTY, CP_USD, CP_LC, PLAN_QTY, PLAN_USD, PLAN_LC, SA_MKT_CURR, CP_MKT_CURR,
Preliminary_data_flag, SA_Qty_C, CP_Qty_C, Plan_Qty_C
or
SELECT MARKET_CODE
, COMPANY_CODE
, PRODUCT
, YEAR
, MONTH
, DISPLAY_STATUS
, LAST_UPDATE
, SA_QTY
, SA_USD
, SA_LC
, CP_QTY
, CP_USD
, CP_LC
, PLAN_QTY
, PLAN_USD
, PLAN_LC
, SA_MKT_CURR
, CP_MKT_CURR
, Preliminary_data_flag
, SA_Qty_C
, CP_Qty_C
, Plan_Qty_C
2. Unnecessary BEGIN/END blocks in code
This drives me crazy, and I usually find it when debugging someone else’s code. Try commenting out in a stored procedure the CREATE PROCEDURE line, highlight a portion of the code while including the BEGIN but leaving off it’s corresponding ‘END”, and then double click on the error that is generated. It sends you nowhere near the vicinity of the error! These have got to go.
CREATE PROCEDURE dbo.ProcName
AS
BEGIN
END
GO
1. SPROC
And the number one thing in the list is "sproc". I cringe, duck, and cower when I hear someone calling a stored procedure a “sproc”.
Sally: “I didn’t see that sproc in VSS!”
Jim: “Oh let me find that sproc for you. I have all of my sprocs there”
Sally: “Cool. I like writing sprocs. One of my all-time fav things to do!”
Jim: “Yeah, me too, Sal. Me too.”
Call it a proc or a stored procedure, even an "sp", but don’t call it a sproc. Can you write… CREATE SPROC myProcedure? No, but you can compile CREATE PROC myProcedure or CREATE PROCEDURE myProcedure. I’ve often found that people who call them sprocs are usually the old vb coders that did the iStreetNumber thing as well. Nothing scientific to make this a fact-based statement, just sayin’
OK OK, obligatory caveat now. We’re having fun, right? No hate-mail, please. If you are guilty of any of one of the above, don’t worry or take offense. I am most likely guilty of at least one from this list myself at some point in time or another.
Lee Everest M.S.
------------------------
"I have not eaten enough of the tree of knowledge, though in my profession I am obligated to feed on it regularly."
References:
Books Online (2010). Microsoft. Retrieved 6/14/2010 from
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/589b6f8e-dc80-416f-9e74-48bed5337f58.htm
Celko (1997). Ten Things I Hate about you. Intelligence Enterprise Magazine. Retrieved 6/14/2010 from
http://intelligent-enterprise.informationweek.com/001205/celko1_1.jhtml?_requestid=55909