LearnNowOnline

Blog

Overview of Transact-SQL (T-SQL)

Excerpt by Don Kiely | July 29, 2013

Transact-SQL is the SQL Server implementation of SQL, a standard codified by the American National Standards Institute (ANSI) and also adopted by the International Organization for Standardization (ISO). No single vendor has fully implemented every part of the ANSI/ISO standard, and each vendor has added its own proprietary extensions to the language, so you'll find plenty of things in Transact-SQL (T-SQL) that you won't find in other database products.

The standard is updated roughly every three or four years, and so is a moving target that no SQL product ever fully implements. The SQL language came about as a result of the work that Dr. E. F. Codd did in the 1960s on his Relational Database Model. The first version of the language was known as SEQUEL. It was then completely rewritten in the seventies, and eventually became known as SQL for Structured Query Language, because it turned out that the acronym SEQUEL had already been trademarked. The original "sequel" pronunciation has stuck to this day-SQL Server is still widely referred to as sequel server, although some purists insist that the language name should be pronounced ess-que-ell. However you pronounce it, the SQL standard has been relatively well received and is the most widely supported standard today.

Transact-SQL (T-SQL)

Transact-SQL is Microsoft's implementation of the SQL language in SQL Server. The language implements a significant subset of the features of standard SQL, as well as some very useful extensions to the ANSI standard that add procedural capabilities, which makes Transact-SQL more like a programming language. There are control-of-flow features, such as IF...ELSE syntax and WHILE loops, as well as support for variables, parameters, and user-defined functions. Like other programming languages, Transact-SQL supports built-in functions for manipulating strings, numbers, and date/time information, and for returning system information. Although Transact-SQL has programming language features, you'd never want to use Transact-SQL to replace a general purpose programming language.

It has no user interface and its programming constructs are very limited. The main advantage to programming in Transact-SQL is that your routines execute on the server and are highly efficient for set-based data operations. Transact- SQL provides the building blocks for all your views, stored procedures, userdefined functions, and triggers. When you perform as much processing as possible in Transact-SQL, performance improves because less data has to traverse the network for processing on the client. T-SQL has two broad sets of statement types: data definition language (DDL) and data manipulation language (DML) statements. DDL statements let you create and update database and server objects, while DML statements let you work with the data itself to create, retrieve, update, and delete rows of data.

These four operations are so common in relational databases that they are often collectively referred to as CRUD operations. Microsoft adds a number of new T-SQL features in every new version of SQL Server, some of which are proprietary to that product, while others are implementations of the features in the SQL standard. This chapter focuses on the fundamentals of the T-SQL language.

NOTE SQL Server hosts the .NET Common Language Runtime (CLR), which allows you to write code in any supported .NET language, such as C# or Visual Basic. You can use Visual Studio to write programs that are then compiled into assemblies and loaded into SQL Server. CLR procedures are designed for code that is processor-intensive, such as complex mathematical or string manipulation. The CLR is not designed to replace Transact-SQL, which is always the best choice for data access and for set-based operations. Any time a CLR procedure performs data access, it uses Transact-SQL "under the covers."

This post is an excerpt from the online courseware for our SQL Server 2012: Introduction to T-SQL course written by expert Don Kiely.