So today I’m going to veer completely away from GP and talk about SQL in general. The reality is that I’m a data and database geek first and a Dynamics GP geek second; and my (current) database language of choice is T-SQL.
But what is the future of that technology? Is T-SQL a data dinosaur? Years ago I actually programmed in assembly language, the language of the CPU itself (ok so technically that would be machine code but let’s not split hairs). Today, processing power and memory have gotten to the point that you can run a language like PHP or C# so many layers above the CPU as to make it invisible. You have an JIT compiled language running on a CLR on top of an operating system on a virtual processor on a hypervisor on a physical processor. Only the most determined driver developer would even consider assembly.
So what of T-SQL? With Visual Studio 2010, Microsoft will be releasing their latest iteration of the ADO.NET Entity Framework, the latest in a long line of tools to abstract away the data layer and hide the dirty underwear of SQL from the developer. The Entity Framework essentially allows you to design, build and implement your database without ever having to get your hands dirty in SQL Management Studio.
Tools like the Entity Framework, LINQ and even the old standby, ADO.NET datasets allow you to do some pretty cool things like being able to join data and enforce referential integrity across disparate data sources and in-memory data structures. They make it easier to make your applications database agnostic.
To be fair, there has always been significant proportion of the developer community who have lived by the philosophy, for some very valid reasons, that the database should be simply a dumb data store. Even GP was built with that philosophy when it supported multiple back end technologies.
I have tended towards embedding as much logic as possible in the database itself; to use the power of functions, views, referential integrity and constraints; to make make stored procedures the middle tier. I have also not found anything quite like T-SQL for querying and analyzing data. The ability to twist and munge and aggregate your data in a single statement of sub-queries (or better yet, Common Table Expressions)
So one day soon will T-SQL be standing among the assemblers and punch card readers in the dusty closet of computer history? Or will the it continue to have a place in our data toolbox?