Whither T-SQL?

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:
XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes

ransomware cyber attack

Why Is Ransomware Such a Major Threat?

One of the most significant cyber threats faced by modern organizations is a ransomware attack. Ransomware attacks have grown in both sophistication and frequency over the past few years, forcing

data dictionary

Tools You Need to Make a Data Dictionary

Data dictionaries are crucial for organizations of all sizes that deal with large amounts of data. they are centralized repositories of all the data in organizations, including metadata such as