Wither T-SQL

Rod Paddock

A number of developments over past 12 months have brought me to a conclusion: Transact SQL (T-SQL) must go. That trusted old workhorse language that powers our SQL Server databases needs to be replaced.

What developments brought me to this conclusion?

Integration of the CLR into SQL Server 2005

LINQ

Lack of innovation in T-SQL

CLR Integration

From 2000 to 2005, Microsoft expended many resources incorporating the CLR and .NET Framework into SQL Server 2005. You can now use VB.NET and C# to create triggers, stored procedures, functions, custom data types, and custom aggregates. Microsoft also expended a lot of time and effort to make sure that the CLR had great performance inside of SQL server.

LINQ

Microsoft’s investment to create Language Integrated Query (LINQ) marries database-style queries and OOP languages like VB.NET and C#. With LINQ you can add queries that use databases, objects, and XML as sources of data. You can then manipulate the data returned using VB.NET or C#. The beauty of this is that it provides a common set of tools and language constructs for handling multiple heterogeneous data sources. Check out the Mar/Apr 2006 issue of CoDe Magazine for more information about LINQ. The CTP version of LINQ is out now and looks promising. Unfortunately, I don’t think LINQ will be available until Visual Studio code name “Orcas.”

Lack of Innovation

Last and probably the most important is the lack of real innovation in the T-SQL language. There have been very few changes to the T-SQL language over the last five years. While T-SQL has added new programming concepts like User Defined Functions, TRY/CATCH exception handling, and more SQL capabilities, the basic structure of the language has changed very little. It is still a procedural language and a cumbersome one at that. T-SQL needs to provide OOP constructs like classes, properties, methods, etc. When compared to languages like PL/SQL (Oracle’s language) T-SQL pales in comparison. For example, in PL/SQL you can bind stored procedure parameters to a table schema rather than SQL Server's explicit type declaration. For instance, you can define an Oracle procedure as follows:

SQL Server's syntax for this is:

So whenever the database schema for the customer table changes you need to find all stored procedures that reference this column and change them accordingly. In Oracle you just change the schema and the PL/SQL stored procedures will update automatically. This is just one example where a simple change to T-SQL could save DBAs a great deal of time.

Conclusion

With these three developments I conclude that T-SQL needs to be updated to reflect modern development principles and tools. The choices are that T-SQL radically change, adding concepts like OOP and a more rich development environment, or that the SQL Server language be replaced wholesale with a modern set of tools which, in this editor’s opinion, would be a combination of CLR and LINQ.