Sql is so twentieth century

published: Sat, 16-Apr-2005   |   updated: Thu, 27-Oct-2005

Sometimes I forget how quaint the SQL language can be. There I am merrily coding away in C# and .NET within Visual Studio, Intellisense guiding the way, the compiler saving me from the most egregious mistakes, and then I have to write a bit of SQL as a stored procedure. Suddenly it all comes to a screeching halt, not because I don't know the language, but because there is so little real-time developer support.

Consider this. I need to write a quick stored procedure that's a simple SELECT statement on a table. Maybe there's a WHERE clause or an ORDER BY or both. Perhaps a JOIN is required. I add a new stored procedure item to my database project and start:

SELECT |

What are the column names? Dunno. I'm not really sure of the table name either, to be honest. We use a pretty descriptive naming convention here at Configuresoft, but that doesn't mean I can rattle off a particular table name at the drop of a hat (there are over 600). Even if I knew the table name, I'd be still pretty stuck with regard to the column names.

Where's the Intellisense? Just not there. And it's not just Visual Studio; it's just not there in other IDEs either. There's nothing for it but to start messing around in the server tool pane, drilling through the database tree. Although this is great and helps a lot, it's so friggin' 80s. It's just ugly.

(Note: I'm fairly certain that there are tools out there that help write SQL code, but I have no experience of them.)

There is syntax highlighting for SQL code in VS (and in other editing environments), so it's not all gloom and doom on the user experience front. Woo-hoo. Unfortunately it is still pretty bad, especially to those who write SQL infrequently like me. Maybe DBAs are used to this because they don't know any better.

And then, for some bloody stupid reason, you can delete a table and the stored procedures for it are still there. You can remove a column from a table (or rename it) and nothing flags the stored procedures that use that column to say "hey, dba-dude, these won't work!". Although the database engine checks the validity of what you're doing when you add the stored procedure, it inconveniently forgets all about it after that.

And then, you have some C# code which fires off this stored procedure you've written; you know, all that code with command.Parameters.Add() all over the place. But the compiler has no idea whether the stored procedure has changed, it has no idea what the parameters to the stored procedure, none whatsoever. The whole Parameters thing, although way cool, is so weakly typed as to be a joke. It is so easy to be writing crap to some column just by accident.

Say wouldn't it be nice if SQL Server automatically exported some kind of class with a method per stored procedure? The parameters to the stored procedure would be strongly typed parameters to the method. Then Intellisense and the compiler would ensure that everything you did was valid. How about exporting a complete class model? A class per database, a class per table, with the columns as properties of the table. Uh? Oh, sorry, just day dreaming.

Really, it's enough to make you scream. In essence, database engines like SQL Server are great at storing and manipulating data. They are absolute crap at maintaining consistency within their own objects, let alone across their API boundary. Talking of their API boundary, it's bloody primitive. It's so annoying.