Click here to monitor SSC

Tony Davis is an Editor with Red Gate Software, based in Cambridge (UK), specializing in databases, and especially SQL Server. He edits articles and writes editorials for both the Simple-talk.com and SQLServerCentral.com websites and newsletters, with a combined audience of over 1.5 million subscribers. You can sample his short-form writing at either his Simple-Talk.com blog or his SQLServerCentral.com author page. As the editor behind most of the SQL Server books published by Red Gate, he spends much of his time helping others express what they know about SQL Server. He is also the lead author of the book, SQL Server Transaction Log Management. In his spare time, he enjoys running, football, contemporary fiction and real ale.

We have our standards, and we need them

Published 6 June 2014 9:29 am

The presenter suddenly broke off. He was midway through his section on how to apply to the relational database the Continuous Delivery techniques that allowed for rapid-fire rounds of development and refactoring, while always retaining a “production-ready” state.

He sighed deeply and then launched into an astonishing diatribe against Database Administrators, much of his frustration directed toward Oracle DBAs, in particular. In broad strokes, he painted the picture of a brave new deployment philosophy being frustratingly shackled by the relational database, and by especially by the attitudes of the guardians of these databases. DBAs, he said, shunned change and “still favored tools I’d have been embarrassed to use in the ’80′s“. DBAs, Oracle DBAs especially, were more attached to their vendor than to their employer, since the former was the primary source of their career longevity and spectacular remuneration. He contended that someone could produce the best IDE or tool in the world for Oracle DBAs and yet none of them would give a stuff, unless it happened to come from the “mother ship”.

I sat blinking in astonishment at the speaker’s vehemence, and glanced around nervously. Nobody in the audience disagreed, and a few nodded in assent.

Although the primary target of the outburst was the Oracle DBA, it made me wonder. Are we who work with SQL Server, database professionals or merely SQL Server fanbois? Do DBAs, in general, have an image problem? Is it a good career-move to be seen to be holding onto a particular product by the whites of our knuckles, to the exclusion of all else?

If we seek a broad, open-minded, knowledge of our chosen technology, the database, and are blessed with merely mortal powers of learning, then we like standards. Vendors of RDBMSs generally don’t conform to standards by instinct, but by customer demand. Microsoft has made great strides to adopt the international SQL Standards, where possible, thanks to considerable lobbying by the community. The implementation of Window functions is a great example. There is still work to do, though. SQL Server, for example, has an unusable version of the Information Schema. One cast-iron rule of any RDBMS is that we must be able to query the metadata using the same language that we use to query the data, i.e. SQL, and we do this by running queries against the INFORMATION_SCHEMA views. Developers who’ve attempted to apply a standard query that works on MySQL, or some other database, but doesn’t produce the expected results on SQL Server are advised to shun the Standards-based approach in favor of the vendor-specific one, using the catalog views.

The argument behind this is sound and well-documented, and of course we all use those catalog views, out of necessity. And yet, as database professionals, committed to supporting the best databases for the business, whatever they are now and in the future, surely our heart should sink somewhat when we advocate a vendor specific approach, to a developer struggling with something as simple as writing a guard clause. And when we read messages on the Microsoft documentation informing us that we shouldn’t rely on INFORMATION_SCHEMA to identify reliably the schema of an object, in SQL Server!

10 Responses to “We have our standards, and we need them”

  1. paschott says:

    I can definitely see both sides of this. On one hand, if your primary work is in SQL Server (or Oracle, or DBISAM, or MongoDB) you likely will stick to many vendor-specific methods and tools. As a SQL Server DB Dev, I’ll admit that I tend towards MS products for most of my work. I won’t necessarily shun other tools but unless they provide a significant advantage or simplify my work, I don’t tend to use them. I’ve tried alternate IDEs for SQL Server (such as Toad) and didn’t find enough value for me to warrant switching. I did and do find value in SQL Prompt over MS’ offering.

    As for code, I try to use ANSI compliant code where possible. Sometimes the vendor solution is just better, even if not ANSI. If that’s the case, I’ll use the vendor-specific code and know that if we were to ever switch platforms it’s something that would need to change.

    I’ve tried using the INFORMATION_SCHEMA views as well. I’ve been able to get this to work for simple queries, but more complex queries generally have me looking at the system tables. It is sad that we’re expected to use INFORMATION_SCHEMA, but it’s poorly supported. Trying to write queries with any sort of auto-complete rarely works out of the box and even then we’re not always sure where to look because we (as SQL Server devs) have gotten used to the syscolumns and sysobjects type references.

    Regarding this presenter’s rant, I’d like to think that most SQL Server people are pretty open to non-MS solutions. We’ll gladly take something that works from someone else over rolling our own or using sub-standard tools. I’m grateful that we generally have good tools out of the box, but won’t hesitate to push for something better if necessary. :)

  2. Robert Young says:

    From my reading, there are two unrelated issues.

    1) as coders, they don’t like having the RDBMS (any engine/vendor) as control of data; they prefer “doing that in the client”, which is their bloated code, of course. They elide their argument by complaining about tools, rather than hegemony, which is the true issue.

    2) whether coder, DBA, or database dev, are the vendor’s tools the most useful? I tend toward multi-engine tools: AQT on Windows and Aqua on *nix, although with DB2 somethings have to be done in its editor, alas. Depending on the engine, commands (which often are presented as ‘SQL’, but aren’t) may be a larger part of one’s work, and require use of the vendor’s editor.

  3. Dave.Poole says:

    I’ve come across this so many times. I believe it arises when organisational IT hierarchy forces separation of DBAs from developers. When the two camps work closely together mutual trust and support breaks out. Both learn from each other.
    DBAs need to understand that not all data needs to be handled by an RDBMS or even to proceed beyond the immediate application. Developers need to understand that when data does need to progress beyond the front-end app it has a value and those pesky DBA disciplines sprung up to make sure that the business value of the data can be leveraged.

    The best counter to the rant I can think of is that a developer is perfectly entitled to accept a practise when the consequence of that practise will effect only them. When their actions drop someone else in the mire then, man up princess!

  4. Hugo Kornelis says:

    I love standards. I work with SQL Server, I try to drive the product to greater adaption of the ANSI standard at every opportunity. See, for example, connect.microsoft.com/SQLServer/feedback/details/299229 (a battle I still have not been able to win, so votes are appreciated).

    The likelihood that the database I maintain will ever be ported is, oh I guess less than one percent. But I still religiously fix ANSI violations (like replacing GETDATE with CURRENT_TIMESTAMP and ISNULL with COALESCE), if only as a matter of principle.
    But I also love performance, and when I can gain a lot of performance by using a vendor-specific extension to the standard, I will. I will of course also add a comment to explain my reasoning, and sometimes even add the ANSI version of the code in the comment.

    The information schema views are a bit weird. I will admit to not using them myself. Now I don’t use the catalog views in my production code either, only in my own helper scripts (because I don’t do dynamic SQL in prod when I can avoid it). But they also have an issue.
    By definition, the information schema views can only expose features that are defined in the standard. A non-standard extension can not be included, because the schema of these views is defined in ANSI, which does not know about those extensions.
    The real problem with this is that even some very basic features are excluded from the information views because of this. The worst of them: indexes. The ANSI standard focuses on functionality, not performance – that is left to the implementation. And hence, indexes are not described in the standard. Which means that they are also not captured in the information schema views. How many scripts do you have that access metadata for tables and columns only, but not for indexes? How many do actually focus on those indexes? All the latter ones have no choice but to use the catalog views for indexes. And those are more easily joined to the catalog views for tables and columns than to the INFORMATION_SCHEMA views.
    I’d argue that the INFORMATION_SCHEMA views could just as well be removed from the ANSI standard, because, by design, they can never be fully useful.
    (Unless you want to add indexes to ANSI, and then forbid all vendors from deviating from the standard or implementing vendor-specific extras – good luck finding support for that!)

    One last remark, on this sentence from the article: “One cast-iron rule of any RDBMS is that we must be able to query the metadata using the same language that we use to query the data, i.e. SQL, and we do this by running queries against the INFORMATION_SCHEMA views”. The first part of this sentence is correct. This is rule 4 in the famous set of Codd’s 12 rules. But Codd does not mention INFORMATION_SCHEMA, nor even state anything about portability or standardization. SQL Server fully complies with this rule. The catalog views expose all the metadata, and they do so in a form that is queryable by SQL. (And I am pretty sure that all or at least most other DB platforms do the same – just using different views).

  5. Keith Rowley says:

    Part of the problem is that SQL Server, Oracle, etc are VERY complex products. A person can spend years studying one of them them and still not master every single feature they have to offer. Asking them to also study all the features provided by some third party program seems cruel.

  6. Robert Young says:

    – a developer is perfectly entitled to accept a practise when the consequence of that practise will effect only them.

    The problem is that all coders/devs following in said developer’s stead are corseted by what was originally asserted to be an isolated/personal decision. This is the number one reason that RBAR applications persist; even new ones. The standard complaint I get when suggesting DRI: “but no one else understands how to develop that way”. The Dark Ages persisted for centuries on the same basis.

  7. DougTucker says:

    How do we go about lobbying Microsoft for better SQL standards compliance? Is there someone in particular leading that charge?

  8. willliebago says:

    people in general are resistant to change – not just DBAs :)

  9. Dave.Poole says:

    Having just read the disclaimer on the use of INFORMATION_SCHEMA I have to say that I’m somewhat peeved.
    Here we have an enterprise product that has implemented a feature that they feel they need to say that it might not work. Either implement it properly or don’t implement it at all.
    The least they could do would be to explain the cases where it won’t work

  10. Timothy A Wiseman says:

    Like many of the other commentators here, I see both sides of this.

    On the one hand, the presenter was quite right. As technologists in general we should always select the best technology for a given project rather than merely defaulting to the one we are most comfortable with.

    But there are also pragmatic reasons for favoring tried and true technologies from well trusted vendors like Microsoft. More than most other technologists, DBAs in many organizations are expected to deliver reliability and maintain the verifiable integrity of the data. When placed in a position where reliability of the system is second only to ensuring no data is lost (and being able to show no data was lost) it can pay to be conservative and stay with things you know work until you have had a chance to thoroughly test and evaluate the possible new techniques and technologies.

    Of course, wanting to stay with the tried and trued helps drive a desire for standardization. But, as the article points out, standards are not always fully adhered to and can be interpreted differently. It is therefore necessary to thoroughly know the technology you are using and its quirks, even when the technology is meant to be based on a standard.

Leave a Reply