Count Me In!

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.

Stored Procedure, Trigger, and UDF Formatting Preferences

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

Two Mistakes

I was challenged by Andy Warren to write a post about two mistakes I have made in my career since he was sure I had made many. I'm sure I have made many, but only one SQL Server mistake sticks out in my mind although I compounded it while trying to fix it.My second mistake does not relate to SQL Server, but did have a negative impact on the business I was working for.

1. In the first year of my career my colleagues and I had written an order entry system for the paper mill we worked at using SQL Server and classic asp. Being an agile shop, even before agile was really popular, we rolled it out and made upgrades/fixes on about a weekly basis. One evening I was tasked with rolling out an update to the database. We had our development/QA database up to date with the latest data and I had a dts package that I had developed to migrate the changes. Well, I started the package, and I realized I had made a mistake and stopped the package. THAT was my REAL mistake as the first step of the package DROPPED the existing tables and I stopped the package after tables had been dropped, but BEFORE they had been recreated. I had not put the package ina transaction (another mistake) so there was no rollback. Ouch! Unfortunately, I was young and not paranoid, like any good DBA would be, so I had NOT taken a full database backup before starting the process. I did have the previous night's backup and all the transaction log backups from the day so I was able to restore the database and then re-do the upgrade. Of course it took me "forever" to do the restore because first I forgot to add WITH NORECOVERY to the first restore so I had to do the full restore twice! So what should have taken me an hour took 5 hours. What did I learn? Always, always, always take a full backup BEFORE doing any maintenance or upgrades on a database or server.
2. My second memorable mistake (I am sure co-workers have others, I have blocked the rest) does not have anything to do with SQL Server, but was a pretty big one. Our mill ran on an AS400 based system that tracked, labeled, wrapped, and shipped the product. When a roll of paper (not like a toilet paper roll, but a large roll of 500+ lbs) was weighed, labeled, and wrapped the system compared the actual weight with an estimated weight to make sure the roll was the one that was expected. Humans would be a barcode on the end of the roll that was scanned as it was weighed and, believe it or not, occasionally they would put the wrong barcode tag on. Obviously, this process would take the scanned ID and read the DB2 database to get the estomated weight. Well, a younger co-worker was on call and had a problem that he could not solve and I was the only person he could get in touch with to help. I was not and am not an AS400 or DB2 expert, but I "knew" where to look for this problem. We checked for locks on the "rolls" table and, sure enough, a process had a lock on roll that it had not released. The precsribed treatment for this diagnosis is to "kill" the process that had not released the lock. This was done fairly regularly and all that happened was that user just needed to re-enter the program they were in, no big deal. Of course, I did not notice that the process I was killing was a system process, not a user process. So I killed the wrapper program and what was already the bottleneck in our productin line was SHUT DOWN! Needless to say the production folks were NOT happy and neither were the other IT guys who had to be called at 2 AM to first figure out what I had done, and then to fix it! I did learn how to determine what was a system process on an AS400 from that mistake.

I'll pass along the challenge to a couple of SQLServerCentral friends, Grant Fritchey and R. Barry Young.

Naming Conventions

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.

Props for my daughter

I know that this is normally and is intended to be a technical/professional blog, but I have to brag about my 9 year old daughter, Danielle, today. She joined the Orlando Devil Dogs Young Marine unit in September. There are 6 Saturday's of recruit training where the new recruits learn Marine and Young Marine history, customs and courtesies, physical fitness training, and drill. When you finish and pass (there are tests) each recruit becomes a Young Marine private, except for the Honor Recruit who is immediately promoted to Private First Class. Well, Danielle was the youngest and smallest recruit in her class of 7 recruits, and she was the Honor Recruit! This means she had the highest test scores, showed leadership, and desire to learn. I have to admit that I was not sure about her joining the program, but she LOVES it and is doing well at it. If she sticks with it and continues to do well, she will have the opportunity to travel and also earn scholarships. Any way, I know this is not a technical or professional post, but since it's my blog I get to be the proud dad today.