Rewind to 1997-ish. Yours truly is sent on the typical “go find out about” junket, training and eventual Microsoft certification. At the time, several things really stood out to me:
- SQL Server supports 16 tables per query*
- When charging $1,800 a head a day, a training institution can do better than Maxwell House-grade coffee, and
- There is no SQL statement to say “I want to create this object; if it’s not there yet, just create it; if it is, try and alter it, and if not, just drop it and create it”.
To be more specific for those in the field, and to elaborate a bit for those who are not… In SQL Server, there is (and has been for a long time) the concept of a stored procedure. Which is exactly what the name suggests: a stored bunch of “do this, do that…”
To create a stored procedure, you tell SQL Server
Create Procedure NameOfYourProcedure
As
Do This…
Do That…
But what if the procedure already exists? Well, you could dispose of any existing version first, internally, and then create the new version. You know, as you probably intended in the first place. SQL Server did no such thing. Pah! In such a situation, what should your application do? Well, why not do the least helpful thing possible! You return an error that the object already exists, and… that’s it. That’s what you do. I am not kidding. For those of you somewhat newer to computers, this was somewhat of a convention when resources (memory, storage, speed of the processor) were so tight that you could not be friendly about failure, even if you wanted to. This culture carried forward, and is still present today.
Ever got something along the lines of “Operation Failed! Error Code: 0×8000FED0″? That code means something. The problem is that it only means something to the programmers of the particular application — an issue of perspective, nay, a failure of it that too rarely came home to roost (oh, the things Microsoft found out when they actually made Word collect information about how people use it). Regardless, these were the late ’90s, and there was no reason for this behavior other than corporate laziness. “Object already exists”? That’s exactly the kind of cop-out message you return when you are writing feature specifications on the SQL server team yet are far more interested in getting home for your family’s barbeque… the one you invited your boss to. I’m sorry, but it truly smacks of complete unawareness of what a DBA does day-to-day, an utter lack of care for same, or both.
So… how does the thing get created? Well, you simply tell every single soul that wants to create a stored procedure to do
If Exists(Select 1 From SysObjects Where Name = ‘NameOfYourProcedure’)
Drop Proc NameOfYourProcedure
Go
Create Procedure NameOfYourProcedure
…
Actually, since you could have objects of different types with the same name, e.g. a table and stored procedure, or you could have two objects with the same name but with different owners** (personally, I think that if you do, you deserve every heaping helping of misery humanly and technically available, but that is another post altogether).
Aside from being distinctly non-standard (only SQL Server and certain magical versions of SyBase — the people Microsoft bought their initial 4.21 version of SQL Server from — know about SysObjects), this is just asinine. For the record, SysObjects at the time was a magical, non-standard table which contained every object in the database. But never we mind… change is coming. We now have the spiffy new Alter syntax!
Alter Procedure NameOfYourProcedure
…
Excellent, isn’t it? Yes, it is. Except for one thing: if you issue an Alter statement for an object that does not exist, you get an error. I kid you not. So, facing the utterly common, daily task of creating a script to create or update an existing object, DBAs the world ’round are stuck with two fully implemented and supported statements:
- Create, which bombs if the object already exists;
- Alter; which bombs if the object does not.
As hard as it is to believe, there is no syntax to do what a developer or DBA would like to do to an object around, oh, 100% of the time: create it if it does not exist, and alter it if it does. At times, it makes me want to hop around on one leg and yell “boggle boggle boggle” at the top of my voice. If anyone would listen, I still might. It’s an easy feature to add. You know it, I know it, dogs know it, but in essence, we’re still doing the same dumb unnecessary jumping through hoops of doodoo we were forced to back a decade or two ago.
[ side-rant ]
Why is it that while SQL Server for two major versions now has supported perfectly serviceable ANSI standard views for anything one might need to check for the existence of just about anything in the SQL Server omniverse, none of its tools, when asked to generate a script to generate an object, use them? I am talking, of course, about the Information_Schema views. It means that instead of:
If Exists(Select * From SysObjects Where Name = ‘Something’ And ObjectProperty(ObjectID(‘Something), ‘IsProcedure’) = 1)
…
You can and (even according to Microsoft’s documentation) should do
If Exists(Select * From Information_Schema.Routines Where Name = ‘Something’)
…
To incorporate somewhat from the side-rant into the main narrative: yes, Microsoft’s own tools do the same dumb, redundant, inelegant thing that is forced upon us DBA types. It seems it is accepted as a status quo within the SQL Server group that:
- There is no need for a natural syntax for something every DBA does around 7,395 times a day, and that
- There is no need for anyone to use perfectly usable, readable and portable syntax if you can just pretend to be hard-core by using back-water retrograde 6.5 era coding styles. Microsoft does. Trip over a SQL Server service pack in mid-installation and check out the SQL scripts that are being run to update the system stored procedure set. It must make someone, somewhere feel good to use retarded syntax.
Now, I do not know the source code for the SQL Server parser. I do not know how its grammar is defined. Still, I double-dog dare Microsoft to hire me to add this feature. If I cannot do it within a week, I will go home and take up gardening.
[ /side-rant ]
Depending on the parser’s implementation of context-sensitive keywords (which, especially from 2005-added features, seems quite rich), I would pick one of the following implementations:
Version 1:
CreateOrAlter Something
As
…
Version 2 (to be more in line with Oracle converts):
Create Or Replace Something
As
…
Or, something I suspect would be the least invasive on the core grammar (and non-standard, and silly, but hey, I will take something like this any way I can get it),
Version 3:
Create [Something] [NameOfSomething]
With Replace
As
…
But that’s not the thing that made me write this overly long, whiny blog entry. You know what actually turned out the the straw that broke the camel’s back and made it tip over the bucket?
With SQL Server 2005, a new scripting, metadata definition language was introduced for Analysis Services objects (that’s Microsoftese for OLAP and Data Warehousing for IT people outside the Microsoft bubble). It is called XMLA, and it is nothing but a set of XML schemas that describe any and all objects within the Analysis Services universe: databases, cubes, objects, dimensions, attributes, measure groups, roles, the kitchen sink and yo mama’s britches.
Having started from scratch, one would imagine the clean slate would allow for a orthogonal, clean paradigm where description and implementation were concepts allowed to contact each other, but only by fax and only on every other Sunday between 4 and 5PM. To put it another way, concepts kept separate as much as humanly possible. Philosophically, XML is a descriptive language***: it should describe what, not how.
Somewhere deep in the bowels of Microsoft, a team of undoubtedly fairly intelligent people lost sight of this in a truly epic way… and came up with XMLA. They came up with a scripting language in XML form, uniting the worst of two worlds and managing to combine the limitations of both in ways heretofore unknown.
XMLA describes such objects as commands, as an XML definition of what the server is supposed to do. Which is spectacularly ironic, counter-intuitive and in my opinion somewhat a slap in the face of the spirit (such as it is, for an entity so soulless) of XML. Rather than describing, XMLA prescribes.
Okay, this is getting far too abstract. First, let me give you a sample of what XMLA would have looked like if I would have any say on the matter — this probably would involve me having to carry a Glock throughout the development cycle, but still:
<database name=”Something”>
<cube name=”Something Else”>
…
This is what XMLA actually looks like:
<Create …>
<Database …>
<Cube …>
What do you think happens when you issue a Create XMLA script to a database to a server that already has the database you are scripting? For that matter, when you attempt to create any object that already exists? Exactly, you get an unceremonious error, and you are on your own. Now, what do you think happens when you issue an Alter on any object that does not exist yet?
Sigh.
In the end, as of now, the development world at large (including yours truly) is left with a scripting language that is unusable for scripting, describing objects or anything that developers would want to do.
Imagine, with all I have said before, being in a situation where you want to distribute an application where:
- The cubes are canned
- The data source is dynamic
- You want to allow users to add or remove cubes
Business Intelligence Studio is a joke, and I think a prime example of letting developers determine usability. It makes me think long and hard about what Microsoft dog-foods. What do they use? There is no way in hell that Analysis Server, as is, is being used internally.
Please, do not get me wrong. I flame about Query Optimizer regressions, things that should have been implemented ages ago, the elephantine toolset (albeit positively svelte compared to Oracle’s “I’ma gonna need 359,000,000 bytes of RAM to start up my management application — before you even connect to a server” way of life). Warts and all, I love the entire Microsoft database stack. I see where development focus goes, version to version. I have beta-tested 12 of Microsoft’s major development/database products so far. I see 2005 was very much about improving VLDB support, correct SCHEMA support (while preserving 99.99% compatibility — anyone in on those FSpecs deserves a farking Medal Of Honor), .NET (CLR) integration (whoa), and from the looks of it, re-doing Analysis Services. I am asking for little, trivial things here. I am begging for polish. Heck… give me a call, and I’ll do it for you.
* In any version of SQL Server before 2005, calling it a schema is a joke.
** Almost a decade later, as a programmer-turned-DBA with an empty afternoon, I wrote a primitive SQL parser to chug down all stored procedure scripts in my company to determine Maximum Number Of Joins In A Single Query (I still hold that record at 82) and Maximum Number Of Distinct Tables In A Single Query (sadly, that record belongs to someone else at 54). I shudder to think about writing those queries on 6.5. And in case you were wondering, no, views would not have helped — our data model was that intricate.
*** XML is descriptive. XPath is descriptive. XSLT is a joke, and a subject I intend to revisit at a later time.
