T-SQL Tuesday #44 The Second Chance

This month’s T-SQL Tuesday is brought to us by Bradley Ball (blog | @SQLBalls). Bradley asked us to write about Second Chances.  Giving us a chance for us to talk about things we have learned from mistakes we have made.  Over the years we have all made mistakes or wished we would have done something differently.

My first experience with this was on my first programming job 15 years ago.  I was tasked with setting up an e-commerce site that linked into a SQL Server database.  I proceeded to write the ASP code that would UPDATE an existing customers address and inadvertently did not concatenate my WHERE clause the UPDATE statement and in a split second every customer had the address I was using.  This in of it self would not have been a bad thing except this was our only database.  So I had just update everyone to the same shipping address.  I learned three very simple but important lessons from this.

1. Have Backups – You should also have a backup to restore from.  Luckily as a new DBA I at least had backups and logs and was able to restore back to before my mistake relatively easily.  Also it helped we only had five people in the organization that used the system so only one person noticed and after the initial panic set aside I was able to recover the database in ten minutes.
2. Never Test on Production – Never write code and test it on a production database.  We should have test environments and where I was at the company was very small.  This was our only SQL Server in the building.  So in the future I would setup up a free version of SQL Server and test against it.
3. Stored Procedures – As I would learn later it is best practice to always use a stored procedure to access the database.  If I had written a stored procedure and sent in the correct parameters the WHERE clause could not have possibly been left off unless I was using dynamic SQL.