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 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.
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.”
Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.