Wise man or Wise guy? You Decide

Just another WordPress.com weblog

Archive for the ‘Standards’ Category

An Explanation of Why to Use Stored Procedures

Posted by sqlwiseguy on May 9, 2009

I wish I was a smart as the guys I interact with on sites like SQLServerCentral and TwitterPaul Nielsen, @PaulNielsen on Twitter, has an excellent blog post today on Why Use Stored Procedures that I think is a must read for everyone involved with SQL Server.  I am a huge advocate of stored procedures and Paul does much better job explaining the why’s than I ever could. 

So thanks Paul, now I have someplace to point the nay-sayers.

Advertisements

Posted in Blogs, Development, Lessons, Programming, SQL Server, Standards | Leave a Comment »

Software Developers, Remember that People Need to Use Your Software

Posted by sqlwiseguy on May 7, 2009

As a hybrid DBA\Developer I had the message of the title reinforced through a negative experience with a web site this past week.  Please bear with me as I tell a fairly long story/rant to illustrate my point.

The web site I had an issue with is www.sunpass.com.  SunPass is Florida’s prepaid toll system and they provide a web site to manage your account.  There are 2 devices you can purchase, a portable transponder and a SunPass Mini Sticker transponder which applies to your windshield and is only good for 1 vehicle.  My wife and I purchased a SunPass Mini and I went to the web site and activated it and I set up my account so that it would auto-replenish using one of my credit cards when the balance dipped below a certain threshold. 

Well, last week we traded in the vehicle that had the Mini in it, so we bought a new Mini for our new vehicle.  This is when the trouble started.  I went to the SunPass web site to activate the new Mini.  I logged into my account and nowhere on my account home page was there a link to activate a new transponder or remove the old transponder.  I thought, “Well, the package says I can activate it online and I activated the first one online so I know it has to be here somewhere.”  So I double–checked and nope, not there.  I went back to the SunPass home page and, lo and behold, the “Existing Customers activate a New Transponder” link was on that page instead of my account home page.  Alright that’s not the most intuitive interface, but I only spent about 10 minutes trying to find it, but at least I found it.  So I clicked on the link and here is what happened:

SunPassError Wait a minute, I don’t have a commercial account, what’s going on?  So I lookup the customer agreement and here is the definition of a commercial account:

5.2 Commercial Accounts: Primarily for companies or businesses with corporate owned, leased or rented vehicles and/or trailers. A current Federal Employer Identification Number (FEIN) must be provided to open an account of this type. FEIN information must be kept current and may be requested by a SunPass® representative at any time. The minimum opening balance for a Commercial Account is dependent on the User’s estimated monthly usage, but must be a minimum of $50.00. User shall be notified of any changes to the required replenishment amount on their monthly Account Summary Statement. For Commercial accounts to qualify for tax exempt status, User must provide a valid tax exemption certificate at the time of account activation.

Nope, I didn’t provide any of this information when I setup my account so, why am I getting this message?  I found the contact us link and sent them a message asking why I was getting the message.  As expected I got a non-response response:

Please call customer service

The next day I got a letter from SunPass telling me that I had a negative balance and I needed to call them to pay up.  Now I’m not only confused, but I’m a getting a bit ticked off, I mean, I did setup my account to auto-replenish, why isn’t it happening?  Back to the web site to make sure auto-replenish is setup, yup, it is.  So I call customer service to deal with the issues.   Here are the answers I got:

  1. Can’t add transponder because I have a commercial account.
      The reality is that when they added the new Mini’s they could not treat them like regular transponders, why I don’t know, so if you open your account with a Mini your account is treated like a commercial account.  So you have to call to activate any new Mini’s on your account.  I asked “How was I supposed to know that?” and the answer is you have to call.  So I suggested that they fix the message to tell me the REAL reason the web site doesn’t do what asked.
  2. Can’t delete the old transponder.
      Again, because it is a Mini it is treated differently and they assume you will disable it by taking it off the windshield so you don’t need to ever disable the transponder.  I asked, “How was I supposed to know that?” and the answer is you have to call.
  3. Auto-replenish isn’t working.
      Apparently the system had a “glitch”, so while the web site is showing that auto-replenish is setup it really never was.  I asked, “How was I supposed to know that?” and the answer is you have to call.

So now to the point of the post. What could the developers/designers done differently to provide a better user experience.

  1. Put account related activities on the account home page.  So an add transponder link on my account home page in addition to the main home page would have made life much simpler and intuitive.
  2. Provide a message that conveys the real reason functionality is not available.  Tell me that you cannot add a Mini to an existing account, not that I have a Commercial account.  In my opinion, the real answer is to not provide the functionality at all.  If account type = Commercial do not show the Add transponder link.
  3. Catch errors and tell the user that their efforts were unsuccessful.  If something goes wrong in setting up auto-replenishment tell me!  If it happens in a batch at the end of the day, send me an email telling me it failed.  Don’t wait to send me a letter when I’m already in the hole.

Please keep the end user in mind when designing and developing your applications.  Don’t frustrate your customers, whether internal or external, with poor design.

Posted in Expectations, Lessons, Problems, Programming, Rants, Standards, Suggestions, Technology | 1 Comment »

The Myth of SQL Code Portability?

Posted by sqlwiseguy on February 25, 2009

While I have never had the “pleasure” of working on a project that required I write ANSI standard SQL so the code for portability, I have worked with products that have been written that way. Alexander Kuznetsov has an interesting blog post, Writing ANSI Standard SQL is not practical, where he addresses the issue from his experience. He notes that best practices dictate using stored procedures for data access and the big difference between SQL Server and Oracle implementation. He also links to another article he wrote on DevX, Think ANSI Standard SQL Is Fully Portable Between Databases? Think Again., where he explains some situations where SQL Server and Oracle will return different results from the same data. Sure some of these issues are somewhat contrived, but they do have real world implications. Overall I would say that I agree with his conclusions.

I would also like to add that, in my experience with applications where the SQL was written for portability, I have seen issues with performance. This is usually because the SQL does not take advantage of some of the T-SQL extensions or uses a less than optimal (for SQL Server) query syntax. It also makes security harder because it does not use stored procedures so you have to enable direct access to base tables. One product I worked with that was written with portability would not implement Primary and Foreign Keys.

I understand trying to maximize your the profitability of your code, but with performance being such a big issue now, I don’t see how you can not write platform specific code. The other issue is that these vendors often do not have platform experts, whether SQL Server or Oracle, so when you approach them with an issue they cannot offer proper help.

One last link to a post by Brad McGahee, Challenging the Tyranny of Third-Party Vendors: A DBA’s Manifesto

Posted in Performance, Portability, SQL Server, Standards | Leave a Comment »

Count Me In!

Posted by sqlwiseguy on November 25, 2008

I’m joining the Battle Against Lawless Database Design. Too often the database is seen as “just” a place to put data. This is wrong, and if handled this way then the application will not perform well. The database is like the foundation of a house, if it isn’t solid, you will be dealing with problems because of it.

Yes, SQL Server is easier to manage and maintain than many other databases, but it is an enterprise ready product that needs to be dealt with as such.

Posted in SQL Server, Standards | 1 Comment »

Stored Procedure, Trigger, and UDF Formatting Preferences

Posted by sqlwiseguy on November 18, 2008

This is part 2 of my personal standards and, when I have the power, the standards at my office. Part 1 is Naming Conventions, you may want to take a look at that before reading this post.

I have to admit to being very finicky about formatting not only in SQL, but any programming language. I am a huge proponent of white space and consistency. I also always wish I commented more thoroughly.

Some basic rules:

  1. Set NoCount On at the top
  2. Declare any variables at the top of the procedure
  3. Create any temporary tables immediately after the variable declarations
  4. Set any variable defaults after temp table creation.
  5. Capitalize first letter of Keywords. I know alot of people like all caps for keywords, but I am spoiled by the tools that colorize them.
  6. Always use Begin and End in control of flow blocks (If-Else, While, etc…)
  7. Whitespace between statements.
  8. Use “As” when aliasing coumn names. first_name + ‘ ‘ + last_name As full_name.
  9. When using “Between” I do not capitalize the “and”. amount Between 10 and 20. I do this to differentiate the “Between and” and the “And” for adding criteria. amount Between 10 and 20 And product_id = 10.
  10. Table aliases should be meaningful and in CAPS.
  11. Whitespace between operands. amount = 10.
  12. Indenting
    • Column lists – between Select and From, in Group By, in Order By, and Having
    • Tables in From
    • Criteria in On
    • Criteria in Where
    • Begin and End inside Control of Flow statements, and the inside the Begin and End
  13. Always end Procedure or Trigger with a Return.

Here is a basic example:

CREATE/ALTER PROCEDURE SCHEMA.proc_name
(
-- required paramters (no defaults - first)
@parameter datatype [= default],
-- output parameters always last in the list
@output_parameter datatype output
)
AS

/* Author Name
**
** Purpose - this procedure is used to show formatting
**
** Example
** ----------
** Declare @output_parameters datatypes, @retval Int -- if needed

Exec @retval = schema.proc_name parameter list

Select @retval As return_value, @output_parameters As paramter_names
**
** History
** ----------
** mm-dd-yyyy - Created
** mm-dd-yyyy - Initials - change made and why
**
*/

SET NOCOUNT ON

DECLARE
@variable datatype,
@variable2 datatype

CREATE TABLE #temp
(
column1 datatype,
column2 datatype
)

SET @variable = value

IF @parameter = value
BEGIN
SET
@variable2 = value
END
ELSE
BEGIN
SET
@variable2 = value2
END

INSERT INTO
#temp
(
column1,
column2
)
SELECT
column1,
column2
FROM
table1 T1 JOIN
table2 T2 ON
T1.primary_key = T2.foreign_key
WHERE
T.column1 = @variable AND
T2.column2 = @variable2

SELECT
column1,
column2
FROM
#temp

RETURN

For an example of my rules and recommendations for triggers please see my article, Introduction to DML Triggers , on SQLServerCentral

Posted in SQL Server, Standards | 3 Comments »

Naming Conventions

Posted by sqlwiseguy on November 6, 2008

The other day I read Aaron Bertrand’s blog post about his stored procedure development best practices which got me thinking about the standards I have developed over the years so I decided to do a short series on my personal standards.

Let’s start with naming conventions.

  1. Object names in all lower case with an underscore, “_”, between words. With this standard I am not affected by the case-sensitivity of the collation.
  2. Table names are plural because they represent sets of an entity, companies not company.
  3. Common column names like id, name, or description should be prefaced with the singular of the table name so our companies table would have company_id, company_name as columns. This is so that they do not need to be aliased in queries when there are multiple columns with the same name. For example, if I have a query that joins products, orders, and companies, I would do P.product_name, C.company_name instead of P.name as product_name, C.name as customer_name.
  4. Use clear and descriptive names for objects. For example, companies instead of cos or last_name instead of lname.
  5. Settle on common abbreviations. I like “desc” for description in columns like product_desc. I also prefer “no” to “num” for number. I’m not really stuck on either, but I believe you need to set the standard and be consistent.
  6. For date columns decide on either date_column or column_date. I prefer to use birth_date or start_date over date_birth or date_start. Again, I can live with either, just pick one.
  7. I like to prefix my views with “vw_”. I understand the arguments against this practice, but when I am in code I want to know when I am referencing a view and not a table, since thay can be used interchangeably and can change performance.
  8. Stored procedures are entity_action. Where action is ins, upd, del, get, list, find. I don’t see the need for any prefix as the use tells you it is a stored procedure.
  9. Function names are fn_entity_action to distinguish them from stored procedures.
  10. Cursors, in rare use, are c_descriptive name.
  11. CTE’s in 2005 and later are cte_descriptive name
  12. Triggers are trg_table_reason_on_action(s). For eaxmple for auditing purposes I would have a trigger named trg_persons_audit_ins_upd_del. I have typically only used AFTER triggers so I have not developed a standard for defining the type type of trigger. I would probably start with something like this for an INSTEAD OF trigger, trg_persons_io_ins_upd_del. I’m not sure I like that one so I’ll take suggestions.

Next up will be a post on stored procedure and trigger formating.

Posted in SQL Server, Standards | Leave a Comment »