Shop Talk: 2020-04-27

The Recording

The Panelists

  • Kevin Feasel
  • Mala Mahadevan
  • Tom Norman

Notes: Questions and Topics

Upgrades

Mala had a two-part question for us. When do you upgrade? And why do you upgrade?

Answer: Each of us has different opinions.

Mala would regularly wait for SP1 of a product before upgrading. With Microsoft eliminating regular service packs for SQL Server, she’s not quite sure what rule of thumb to follow.

Tom likes to push the envelope, preferring to upgrade quickly. Though he doesn’t hit each version—he tends to skip a version, e.g., 2016 to 2019. He does want to see compelling items in a version before upgrading.

Kevin likes to upgrade for upgrading’s sake. Or something like that… I have enjoyed being part of the Early Access Program for SQL Server and getting a chance to try out products under development. I pushed back a bit against the “Wait for SP1” argument, but one thing I failed to say during it is that if everybody waits for SP1, SP1 will still have a bunch of bugs. I am thankful for the people whose philosophy is “Someone’s got to find the bugs, and it might as well be me” and everybody who waits to upgrade should as well.

From there, I derailed things onto my refusal to work for a company stuck on old version of SQL Server, with no plan to upgrade (or a plan but no real desire to upgrade). Tom and Mala make me walk it back a bit.

ANSI SQL

Mike Lisanke wanted to know why we call the language SQL for SQL Server, Oracle, DB/2, Postgres, etc., and yet they’re all different languages.

We covered a lot in here, but the gist is that ANSI releases versions of the standard which companies subsequently adopt in part (and extend in part). I mentioned that there isn’t “an” ANSI SQL standard and Wikipedia has a nice table (about 2/5 of the way down) showing the different versions of ANSI SQL. I had guessed about the pre-89 versions and wasn’t quite right—there was only one pre-89 version, there wasn’t a 1997 version, and 2000 was 1999. Other than that the answer was fine! But there have been 10 iterations of the ANSI SQL standard.

We also talked about the origin stories of a few platforms, including Sybase/SQL Server, Oracle/Postgres, and MySQL/MariaDB. We also talked about coding for ANSI compliance. Tom likes that idea (or just using PolyBase—which I recommend!). I don’t care much for coding for ANSI compliance for most places because you lose chances to improve performance for a chimerical gain. The exception here is if you must write software which is cross-platform; then you’re stuck.

I brought up lateral joins. Here’s a sample implementation in Apache Drill. I still prefer the name CROSS APPLY.

Hierarchies and Relational Database Design

Tom mentions making use of hierarchyid in SQL Server. Then we started name-dropping books.

First up, I recommend Adam Machanic, et al’s, Expert SQL Server 2005 Development. I haven’t read it in a while and obviously the development surface area has changed in 15 years, but there is an excellent chapter on trees and hierarchies.

Mala and I both recommend Louis Davidson and Jessica Moss’s Pro SQL Server Relational Database Design and Implementation. I have an older edition, but I mentioned that it has the best explanation of 4th normal form that I’ve ever read.

I then pulled out my copy of Candace Fleming and Barbara von Halle’s Handbook of Relational Database Design. I consider it the best explanation of normalization I’ve ever seen in print (and thanks to Grant Fritchey for the recommendation!). Just don’t read the second half of the book unless you want a story of how implement on ancient systems.

Events of Note

We wrapped up with one event of note:

2 Replies to “Shop Talk: 2020-04-27”

Leave a Reply

Your email address will not be published. Required fields are marked *