SQL, PL-SQL, T-SQL: What’s the Difference?

Microsoft has developed or licensed a number of SQL variants. Yes, they're all procedural languages used to manage relational databases, but they're used differently. They were also created by different organizations. Here are the differences between SQL, PL/SQL, and T-SQL.

Regular Ole SQL

Let's start with plain vanilla SQL. The original. SQL stands for Structured Query Language, and it is overwhelmingly the most common language used for managing and accessing data in a relational DBMS (database management system).

SQL was originally developed by IBM and is an ANSI/ISO standard. (There's also a war raging about how to pronounce it, but that's a different story.) It's a data-oriented language designed for specifying the structure of data, and selecting and manipulating sets of that data. It assumes the data is in a relational database, stored in a collection of tables composed of rows (tuples) made up of columns (fields containing values of particular types).

The language has two main parts: A data definition language (DDL), and a data manipulation language (DML). The DDL is used for defining data structures and thus for creating objects like tables, views, and procedures. DDL keywords include CREATE, ALTER, DROP, etc. The DML is used to access and manipulate the data. DML keywords include SELECT, INSERT, UPDATE, MERGE, DELETE, etc.

SQL is largely declarative. Procedural languages like Java, C++, and Javascript provide many features to let you say "how" to do things by specifying control flow, like statements to be executed in sequence, statements executed conditionally, statements executed repeatedly inside a loop construct.

In contrast, SQL aims to say what to do, not how to do it. SQL's ideal is that you describe the structure of the data (e.g., "CREATE TABLE tablename…"), and describe what information you want to be fetched (e.g., "SELECT fieldname FROM tablename WHERE…"), and let the DBMS system software worry about the low-level details of how to store the data, and how exactly to retrieve the data specified by your query.

This SQL purity has gradually been compromised a bit. Real-world database systems rapidly added the idea of stored procedures: chunks of server-resident code, written in a mix of SQL statements and proprietary extensions, and precompiled and stored in the database. Every DBMS has its own dialect of SQL with non-standard extensions, proprietary features added by the vendor for interacting with the DBMS and to make it more convenient to do things that are clumsy to do in the pure declarative style.

The Lesser Known, but Powerful, Dialects of SQL

Both T-SQL and PL/SQL are extensions to SQL: Extended subsets of the ANSI standard. Pure SQL is sufficient for create/read/update/delete (CRUD) operations but lacks the breadth of expressibility of a general-purpose language. It's difficult or impossible to code up complex business rules and other commonly-desired routines purely with operations for selecting and manipulating sets of data. Thus, database vendors always go beyond the standard, extending the abilities of certain SQL statements, adding procedural mechanisms, and providing built-in functions for common capabilities like strings, math, even things like sending an email and accessing the web.

Thus, where SQL itself is a largely declarative language for specifying and manipulating data, T-SQL and PL/SQL are imperative languages giving you the range of features you'd expect for writing apps. Both dialects provide direct embedding of SQL statements and storage and execution of their code inside a database. It's straightforward to use SQL statements in T-SQL or PL/SQL, in comparison to the hoops required to invoke SQL facilities in ordinary general-purpose programming languages.

T-SQL: The SQL for Bankers

T-SQL (Transact SQL) is commonly associated with Microsoft SQL Server. Originally developed by Sybase, it also became the proprietary SQL dialect of Microsoft's primary relational DBMS. When an application communicates with SQL Server, it does so by sending a T-SQL statement.

T-SQL adds general-purpose language features: the ability to declare local variables, conditionals, loops, transaction control, Kill and exception handling; built-in functions for math, strings, date/time, etc; and some extensions to SQL statements.

CBT Nuggets trainer Garth Schulte has a great MCSA course for Querying Data with Transact-SQL.

PL/SQL: Oracle's SQL

PL/SQL (Procedural Language SQL) is the proprietary extension to SQL for Oracle's DBMS systems, a procedural language tightly integrated with both SQL and Oracle databases. Interacting with Oracle databases is done via SQL and PL/SQL.

PL/SQL borrows its syntax from Ada and adds expected procedural language features: variables, arrays, loops, conditionals, exceptions, etc. It can do encapsulation, overloading, and packages (but not inheritance). It adds features to some SQL statements.

Comparison

Sticking to the ANSI SQL standard, where possible, ensures portability. If you use a given dialect's proprietary keywords and features, you've entailed the cost of updating code if you ever migrate your app to a new DBMS.

But pure SQL is targeted at the domain of manipulating sets of data in a relational way – it's not a general purpose language. Both T-SQL and PL/SQL are more convenient for creating full-up applications. And there's a strong connection between the IT and dev side with SQL.

T-SQL and PL/SQL add many extensions to SQL that serve the same purposes, but they've made different choices in how to do particular things. Thus T-SQL and PL/SQL code differ significantly. Migrating code from one dialect to the other is nontrivial. But both Oracle and Microsoft provide tooling to automate part of the process of moving from the other guy's DBMS to their own.

SQL Server – and thus T-SQL – runs on Windows and Linux. Oracle (and PL/SQL) runs on dozens of platforms, from huge hoary mainframes to desktop PCs and Macs. Oracle's platform of choice is Oracle Linux, a Linux distro custom-configured for the needs of a database. In Microsoft-land, Oracle is available on Windows. In Microsoft's Azure cloud, it's on both Windows and Linux.

Oracle DBMS systems are usually seen as complex large-scale solutions, where SQL Server is generally more at home in small- and medium-sized businesses. This distinction carries over to their SQL variants: PL/SQL is seen as sophisticated and potentially more powerful, where T-SQL is seen as flexible and simpler, but potentially limited.

ANSI SQL is a powerful data-oriented language for particular tasks. T-SQL and PL/SQL are extensions to SQL, general-purpose procedural languages that make it convenient to write apps for their particular vendor's DBMS.