SQL wherefore art thou SQL?

Posted: 2008-06-19 in Sql
Tags:

SQL wherefore art thou SQL?
I often question what has happened to my friend TSQL? With every pending rumor of a new MS SQL Server release I excitedly gidder like a school boy as I ponder at the new goodies that are bound to advance. How can I expand myself with some cooler newer more buzz word language, and every release the answer is the same. No advance in the language other than a few more functions, and most of these aggregate functions. Yes I know about CLR, yes I spend most of my current day programming in C# but no, I want, no make that I DEMAND TSQL is brought to the modern light. LINQ has opened the eyes of beholders, why can’t TSQL follow suit?

Why SQL why do you keep doing this to me? Why don’t I have an ever expanding library of goodies?
Why don’t I have functions more simple like regular expression, string commands, debugging, try catch fail, private little sub functions in the same query page (think OO or even C) and many other goodies to boot.

I have no problem with the SQL engine itself, and not the driver (as that would be me) but instead the controls and instruments provided to connect the dots Mr TSQL, a dinosaur from the past.

Unless you have been under a rock for the past 15 years you might be forgiven in seeing the obvious, TSQL is still there, good drivers are becoming rare these days with the average decent SQL programmer being also post OO age. Those young guns that do step to the plate are usually all talk and all SQL-92 standards, something reminiscent of bad teachings from university classes. Code written is the obvious many temp variables, cursors and temp tables… all joined in at the end to try resemble the results desired. Fine to those around them as the results are correct but SQL is an art and the bad art is like a car tire being tied to a marathon runner. Eventually the server will grind to a halt, not something you want during peak load times.

I then guarantee two stages of rapid fix will result.
Stage one add indexes too everything and see if that help, add some more ram and reboot the server.
Stage two (smoking server). Throw more money at the server and get more CPU Cores or a heap more HDD and memory.
Stage three, investigate what this query analyzer thing Microsoft has sent me is, and time to invstigate which queries run the slowest (SQL 2005 has some good way to find the top 10 worse SP’s, or called SP’s) and start fixing the queries in a most critical to least critical order. The only problem is that by now if the DBA or SQL guy knows how to do this then there is a fair call that they person wouldn’t be in this situation.

So final stage, the late last option; hire someone that knows a little about execution paths, good SQL code, query hints and effective SQL code. Being in this position more than once, I have found that in these situations a re-factor is not merely enough but instead an architecture change. SQL does offer a nice advantage here, you can write easily write often complete replacement systems in parallel to a live system. This gives a powerful choice of swapping out small parts at a time or a complete major swap or anything in between all unbeknown to the end users.

.

But all this talk of bad programmers has me a little side tracked, what and why do we have so many of these guys? Why are so many people trying to avoid SQL like the plague?
There are a lots of good C# programmers, there are a lots of good C++, java and even web programmers out there. So why are people so reluctant to reject TSQL? Why are there so many that try and fail so miserably? Where are all the DBAs (that’s another story)?

Enter the world of code evolution, or in TSQL case not. Code evolution takes place every day, books are written, ideas are formed, arguments are raised groups split and new groups form A become B B becomes C, C becomes C++, C++ become D, on the way spawns children, like C#, and you are left with a complete family tree of code. A family tree spanning from the birth (punch cards, Plankalkul and FORTRAN) to the present (C++, .NET, Java, etc). in the world of languages nothing has ever evolved as quickly as the computer language, and probably nothing ever will.

Evolution of language also brings with it massive changes and abilities in compilers, compile time and run time exception handling, code completion, syntax highlighting, developer auto code (think GUI development and Database linking), developer tools, debugging tools, and code analysis tools.
Debugging in TSQL is as painful as the days of print statements.

All this time TSQL has been stuck in one massive rut, the greatest evolutions seems to have happened in 92, pre that 89 (its birth is the 70s but ANSI got it in 86). Over the entire generation of TSQL less has happened than in the brief period of .NET. It is time SQL gurus of the world to come forth and slay the chains of suppression. It is time to release an update and not a SQL:2006 type update…

What I am talking about is a C to C++ update, a full blown time to get a move on and learn from the past type of update. Yep really something that big.

We could start with:

Reformat of the syntax, LINQ might just have it right here. “from, where, Group by, order by, select”- Think about it, it makes sense, you may know what you want but really until you get the tables in you have no idea about names or what conversations are needed.

Gives so so many advantages the best is probably code completion, yes it enables proper intellisense, the compiler knows the table can assume a function or column and let the suggest being. The from can be code completed right from the start.

Within procedure function calling and creation. Not sure what I mean? The ability to write a private method within a SP or other function. No not a GOTO statement a private function like every other code out there. You can easily then break out repetitive functions, make stuff more code reusable, more code readable (yes there are CLI’s), easy to make recursion, easy to make code readable, easy to track code.

Scrap the EXEC stuff, bring it all to the front line, allow me to create dynamic statements, like I can in .Net. This would also allow me at run time to select which columns, Tables, db’s, etc I want.

Much much much better error handling.

A debug mode, although I admit this could potentially ruin compiler query optimizations.

Code commenting properly, Regions, todo, debug, note

Parameters into functions, like “select top 5” to “select top @five”

Highlighted syntax checking

Array type passing

Table type passing (you can do this using permanent tables but its messy)

Skip cursors, give us a better for, for each, while and Iterator (cursor on steroids).

Why where at it, structs, enum, and classes.

No not CLR, I want TSQL to embrace code evolution.

Oh and an easy way to store a database in code repository, that would mean a complete stripped down only programming stuff, with the posibility to filter on non system programming.

Soooo where does that leave me, well unfortunately it leaves me short I should write more on this topic but I think it might be better to start a new post.

Cheers

Choco

I have seen the future and we are all doomed.

Advertisements
Comments
  1. Choco Smith says:

    I’d like to add a little comment on this page.
    Since the release of this article (it was orginally published in 2006ish but moved when i shut down the orginal blog) Sql 2008 and now sql 2012 has been released.
    With these two comes debugging, code complete (sort of it always breaks on my machine), and some other cool features, especially merge. Did I mention merge.. if not well merge.

    Anyway take care my whole 2 readers. I guess I am no Kevin Kline or Guy Harrison (ok his oracle) 😀

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s