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.

SQLSaturday #8 - Orlando Recap

I attended/spoke at/volunteered at SQLSaturday #8 - Orlando today and had a very good time. There was close to 300 attendees and things went well. The sponsors did a great job and provided some great prizes (3 Xboxes, an IHome, an IPod Touch, and a full class at End2End Training). As usual Andy Warren and crew (I had a small part) did a good job organizing and keeping everything moving. I don't envy Andy as being the lead for this event is a full-time job for at least 2 weeks. I did not have the opportunity to attend any sessions as I was busy helping out where needed, prepping for my Profiler session, and meeting people. It was great to meet in person some folks I'd met in forums, particularly on SQLServerCentral. There was Steve Jones, Brandie Tarvin, Brian Kelley, and Fraggle (Nathan, last name not remembered). I also met Rachel from RedGate, who doesn't think Hershey's chocolate is any good, and prefers Cadbury. There were many others as well, and since I'm horrible at remembering names, especially when I spoke with at least 50 people I'll stop naming new names. Then there are the old friends, Andy Warren, Chris Rock, Rodd Harris (also from NTM, where I work), David (who's last name I can never remember), and Dolores Toefel from the OPASS group.

My Profiler session went well and seemed to be well-received. There were no empty seats, nor was there anyone standing. I forgot to look at the session evaluations as I was quickly moving on to let the next speaker get setup and to get to my next task. I think I could have done a little more prep, especially for the trace I used for my replay definition. The next step would be to do an advanced presentation that spends more time on server-side tracing and querying and interpreting trace information.

I also went to the after party which was a lot of fun. It's great to sit and relax with other geeks, talk shop, sports, politics, and just goof around a bit. It looked like about 30-35 people went to the after party which was a decent turnout.

The next SQLSaturday is SQLSaturday #10 - Tampa 2009 January 24, 2009. I won't be able to make it as I'll be freezing in New Hampshire for a few months, but I bet it will be a good event to attend.

Featured Video on JumpstartTV

Cool, one of my videos was the featured video on JumpstartTV today. Here's the link to the video:
http://www.jumpstarttv.com/integrating-performance-monitors-with-sql-server-profiler_451.aspx

Always cool when you see your name out there.

JumpstartTV is putting out a ton of material right now particularly for SQL Server and, not just because I am on there, I would recommend checking it out for some quick How To videos. Most of the videos are under 5 minutes so it's not a huge time sink to try some out. Be sure to rank and comment on any videos you watch and if you want to send any comments to me I'll be sure to pass them along to the owners\developers.

Introduction to DML Triggers published on SQLServerCentral

My third article, Introduction to DML Triggers, was published on SQLServerCentral today. The article was born out of forum questions about triggers and the errors and/or bad practices I have seen in the posted triggers. The goal of the article is to help people understand how DML triggers work in SQL Server so they can avoid the common mistakes.

You can also follow any discussion of the article here.

JumpstartTV Videos Published

A couple of months ago I spent a day with the guys at JumpstartTV creating some videos on using SQL Server 2005 Profiler. Those videos have now been published. Here's a link to the first one:
Creating New Trace With Sql Server 2005 Profiler. Please comment and let me know what you think.

JumpstartTV, in this incarnation, is being filled with 1-5 minute "How to" videos on a variety of topics with a current focus on technology, specifically SQL Server and .NET. I think the nicest feature is the ability to suggest video topics. So if you don't know how to use configuration files in SSIS and there is not a video there already, you can ask for that topicand it will be available to authors, like myself, can "take ownership" and create the video.

The question that still needs to be answered is, "Is this format better than a step by step article with screen shots?". I happen to think the it will be better for some people as everyone learns differently. I definitely like the short format. I certainly hope that it takes off as I consider the guys behind it friends and want to see them succeed. Also, in a more selfish manner, I wan tmy videos to do well so they'll ask me for more.