A conversation with a data modeler and a comment left on another blog led to this week’s commentary.
The former revolved around how a PK should be defined on a child table. The data model called for Parent_ID to be constrained as a FK in the child, and Child_ID to be the PK of the child table. I explained that from the POV of the SQL server optimizer, the child PK ought to be a compound of Parent_ID and Child_ID, in that order. This would permit the optimizer to choose a merge join for any query involving both tables, a join type that is fast and efficient (for the most part – there are exceptions). The data modeler agreed this was a reasonable idea, but we then wandered off into a discussion about this and that “best practice”, and whether or not such “best practices” were in fact practical at all, or whether they were based purely on philosophical principles that might or might not have any close relation to reality.
That same day, I came across a comment on a forum left by a well-known database developer, author of many books on the subject, quite the stickler for rules and standards, so much so that I privately refer to this individual as “the ISO Nazi”, because of the constant reference to the ISO SQL standards in this individual’s works and communications, and because this individual is in the habit of berating and mocking those who do not adhere religiously to the ISO party line.
There is, no doubt, a place in the world for SQL standards. It’s good to know that there is a set of keywords and concepts which appear in every flavor of SQL. At the same time, I think it crucial to acknowledge that, with some exceptions, most of us work with the same RDBMS day in and day out, and we have learned the idiosyncrasies of our tools, often to our advantage. Writing code that complies strictly with what the ISO considers as the way to do things may or may not produce the best results, and given that many of us spend our entire careers working with one vendor’s product, portability is usually not a driving consideration.
My interest in the ISO standard is, frankly, purely academic. When it comes to writing my code, and designing my objects, I consider that first and foremost I am an SQL Server developer, and I do my best to think like the optimizer. Doing so more often than not assures that I am coding to get the best performance out of the RDBMS, even if I am violating international standards and “best practice” monographs. It is performance and reliability that I owe to my users, not conformity to an ideal.