May 17, 2013 at 11:56 am
Hi guys. I am hoping for some general tips on how you get to the point of feeling comfortable running provided SQL Server scripts on my databases. Any kind of code review practices would be helpful.
When we update our production systems with SQL Server scripts provided by application developers, I always try to scan through them to understand as best as I can what they are doing so that I feel confident that nothing is going on that seems wrong.
How much time do you generally spend reviewing scripts before running them?
Sometimes the scripts are huge and complex and I don't always follow what they are doing. Is getting an estimated execution plan before running them a good way to get a feel for what will happen?
Clicking the parse button catches typos in code but does not alert me if an object exists. For example if DBName_Test was not changed to DBNAME_prod on accident and then the script fails and we have to restore from backup and start all over again.
Are there any good tools for examining and understanding SQL scripts before running them? Any suggestions for avoiding issues would be valuable.
Thanks much,
Howard
May 17, 2013 at 12:01 pm
I generally break the script down into understandable chunks to analyze it and won't post anything to production until I fully understand it. Using the execution plans and client stats is extremely valuable in evaluating performance. If you're responsible for the production database you should feel empowered to reject any script that would be detrimental if you have a better alternative.
Other than that.. study study study..
May 17, 2013 at 12:14 pm
You might read this article. While it's about solving problems, it could be used to do the same with existing code: https://www.simple-talk.com/sql/t-sql-programming/solving-complex-t-sql-problems,-step-by-step/
May 17, 2013 at 12:15 pm
Thanks Erin. I try to break them down to understand them too.
Many times the scripts are to alter some stored procedure or view so there is no execution plan that I can look at. For the most part, I just have to start at the top and scroll down through it looking for inserts/updates/deletes that seem unusual.
I guess the best is to slow down and always understand the scripts before running them. There is no magic tool to parse and break out what actions are included in the script or catch invalid object names? IntelliSense gives red squiggly lines for invalid objects but sometimes I miss them scrolling up or down and parse does not catch it.
May 17, 2013 at 1:16 pm
Steve,
Thanks for sharing that article with me. She has good insight that I can learn from.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply