Whither T-SQL?

number of developments over the past 12 months have brought me to a conclusion: Transact SQL (T-SQL) must go. The trusty 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.

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 in release form until the release of the “Orcas” version of Visual Studio.

Lack of Innovation
Last and probably 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 such as 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:

   MyProc(@customername varchar(100))

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.

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.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin


The Latest

iOS app development

The Future of iOS App Development: Trends to Watch

When it launched in 2008, the Apple App Store only had 500 apps available. By the first quarter of 2022, the store had about 2.18 million iOS-exclusive apps. Average monthly app releases for the platform reached 34,000 in the first half of 2022, indicating rapid growth in iOS app development.

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a