Detangling Nested Queries

As a one-time "accidental DBA", I both witnessed and committed a number of bad or inadvisable practices on my SQL Server.  We were a small shop, and early on the furthest thing from my mind was performance or efficiency.  There was one egregious infraction that's worth a mention here...

Views are definitely handy.  They can also promote and exacerbate developer laziness.  We had a view set up that would join order line and order header records, for a massive "SELECT *" style sales history output, with just about all columns.  Then someone came along and wrote a view using that view as a source table.  Then, someone else came along and wrote an SSRS report based on that second view.  So here we had a highly focused end query returning small amounts of data from a view pulling data from another huge view, pulling data from the source tables.  I've done a bit of testing and I'm not convinced that it creates as much of a performance problem as it would seem; it appears that SQL Server does the logical heavy-lifting of making it efficient even though the code is not.  I'd be interested to know otherwise though!

But the main problem is one of manageability.  With all these unnecessary dependencies we end up with a needlessly complex amount of code...two view definitions and a query, where only a single query was necessary.  Any changes to the view definition breaks the views and queries down the chain, in what could be a transparent manner.  So, one day when the office was quiet enough, I set about detangling these messes and rewriting the report queries to go straight to the tables, only pull the information they needed (removing the "SELECT *"...a bad habit that afflicted me in my early days, I confess).  Now when someone examined the report query, they could understand exactly what data it was pulling, without having to confusingly parse through the subsequent view definitions from which it was retrieving data.  This is especially useful when the end user questions data quality; in my case, I wasn't the report author, so I didn't have a full understanding of what the query was gathering.  If we write report queries as simply as possible and avoid the temptation to just tack it onto an existing view with just some additional filtering, it will help the next guy in the chain, believe me.

"The simplest thing that works", a good mantra for query writing.  Happy query detangling.