Why Did Microsoft Create its Own SQL Extension (T-SQL)?

Question

What are the reasons behind Microsoft implementing its own SQL extension as Transact SQL (T-SQL)? What are its advantages over the normal SQL?

Answer 1

Transact-SQL (T-SQL) is Microsoft's and Sybase's proprietary extension to SQL. Microsoft's implementation ships in the Microsoft SQL Server product. Sybase uses the language in its Adaptive Server Enterprise, the successor to Sybase SQL Server. Transact-SQL enhances SQL with these additional features:

• Control-of-flow language
  
• Local variables
  
• Various support functions for string processing, date processing, mathematics, etc.
  
• Improvements[citation needed] to DELETE and UPDATE statements

http://en.wikipedia.org/wiki/Transact-SQL

Wiki connects you to much more expanding information and details.

Answer 2

Everybody extends SQL.

SQL isn't procedural, it's declarative. You describe what you want, and it figures out how to retrieve it using whatever indexes or hashes or whatnot is available.

But sometimes, that's not sufficient. T-SQL gives syntax to run procedural code inside of your queries. This lets you do control structures (begin-end, if-then-else), iteration and move values between local variables, temporary tables and other sources.

Answer 3

A thing to note is that while most RDBMS providers make a clear distinction between their extensions to SQL and the programming languages used to write stored procedures and triggers and so on, Microsoft and Sybase do exactly the contrary, they mix these two concepts into one, namely, T-SQL. You use T-SQL when you write normal queries, but you also can (and usually do) use T-SQL when you are writing stored procedures and triggers.

This has the controversial benefit of encouraging (or at least making very easy) the creation a mix of procedural and SQL code[*].

Nowadays Microsoft makes a distinction between T-SQL stored procedures and those written for the CLR (ie, .NET), but this is a relatively new development (from SQL Server 2005 onwards).

[*]: Controversial because people who don't speak SQL will be tempted to write procedural code (usually very inefficient in databases) instead of learning SQL (the proper thing to do).

Answer 4

What are the reasons behind Microsoft implementing its own SQL extension as Transact SQL (T-SQL)?

To make your life easier.

What are its advantages over the normal SQL?

There is no such thing as "normal SQL"

Transact-SQL both enhances the set-based abilities of SQL and adds procedural abilities.

Other systems (like Oracle and PostgreSQL) clearly distinguish between SQL and procedural languages (PL/SQL and pl/PgSQL).

Microsoft doesn't make such a strict distinction.

Transact-SQL was developed by Sybase around the middle of 80's, when there was no standard at all (the first one was proposed in 1986).

By that time each vendor already had a burden of legacy applications to support, and rewriting their databases to conform to the standard would break the compatibility.

There is more or less commonly supported standard, SQL:92, but it still misses very very much to be really of use.

That's why almost every task beyond a simple SELECT with a JOIN needs some proprietary support to be implemented efficiently.

Answer 5

One other really important reason why vendors create their own flavors of SQL is performance tuning. There are many ways of writing more performant queries using vendor specific code that is written to take advantage of how that particular database engine works.

Answer 6

The applicable SQL Standard for control-of-flow, local variables, etc (i.e. procedural code) is known as SQL/PSM (Persistent Stored Modules).

According to Wikipedia it was adopted in 1996 but I suspect it's the usual problem: vendors were already committed to their own extensions and therefore take up of the Standards is postponed for long periods of time

...but not necessarily indefinitely, there is hope. For example, common table expressions (CTEs) and OLAP functions in SQL Server 2005 and temporal data types in SQL Server 2008 indicate that extensions to TSQL will keep close to the publish Standards.