Transact-SQL
Encyclopedia
Transact-SQL is Microsoft
's and Sybase
's proprietary extension to SQL
. SQL, often expanded to Structured Query Language, is a standardized computer language that was originally developed by IBM for querying, altering and defining relational databases, using declarative
statements. T-SQL expands on the SQL standard to include procedural
programming, local variable
s, various support functions for string processing, date processing, mathematics, etc. and changes to the DELETE
and UPDATE
statements.
These additional features make Transact-SQL Turing complete.
Transact-SQL is central to using Microsoft SQL Server
. All applications that communicate with an instance of SQL Server do so by sending Transact-SQL statements to the server, regardless of the user interface of the application.
IF DATEPART(dw, GETDATE) = 7 OR DATEPART(dw, GETDATE) = 1
PRINT 'It is the weekend.'
ELSE
PRINT 'It is a weekday.'
IF DATEPART(dw, GETDATE) = 7 OR DATEPART(dw, GETDATE) = 1
BEGIN
PRINT 'It is the weekend.'
PRINT 'Get some rest in weekend!'
END
ELSE
BEGIN
PRINT 'It is a weekday.'
PRINT 'Get to work in weekday!'
END
or function.
DECLARE @i INT
SET @i = 0
WHILE @i < 5
BEGIN
PRINT 'Hello world.'
SET @i = @i + 1
END
This example deletes all
DELETE users
FROM users as u
INNER JOIN user_flags as f
ON u.id=f.id
WHERE f.name = 'Idle'
-- begin transaction
BEGIN TRAN
BEGIN TRY
-- execute each statement
INSERT INTO MYTABLE(NAME) VALUES ('ABC')
INSERT INTO MYTABLE(NAME) VALUES ('123')
-- commit the transaction
COMMIT TRAN
END TRY
BEGIN CATCH
-- rollback the transaction because of error
ROLLBACK TRAN
END CATCH
Microsoft
Microsoft Corporation is an American public multinational corporation headquartered in Redmond, Washington, USA that develops, manufactures, licenses, and supports a wide range of products and services predominantly related to computing through its various product divisions...
's and Sybase
Sybase
Sybase, an SAP company, is an enterprise software and services company offering software to manage, analyze, and mobilize information, using relational databases, analytics and data warehousing solutions and mobile applications development platforms....
's proprietary extension to SQL
SQL
SQL is a programming language designed for managing data in relational database management systems ....
. SQL, often expanded to Structured Query Language, is a standardized computer language that was originally developed by IBM for querying, altering and defining relational databases, using declarative
Declarative programming
In computer science, declarative programming is a programming paradigm that expresses the logic of a computation without describing its control flow. Many languages applying this style attempt to minimize or eliminate side effects by describing what the program should accomplish, rather than...
statements. T-SQL expands on the SQL standard to include procedural
Procedural programming
Procedural programming can sometimes be used as a synonym for imperative programming , but can also refer to a programming paradigm, derived from structured programming, based upon the concept of the procedure call...
programming, local variable
Local variable
In computer science, a local variable is a variable that is given local scope. Such a variable is accessible only from the function or block in which it is declared. In programming languages with only two levels of visibility, local variables are contrasted with global variables...
s, various support functions for string processing, date processing, mathematics, etc. and changes to the DELETE
Delete (SQL)
In the database structured query language , the DELETE statement removes one or more records from a table. A subset may be defined for deletion using a condition, otherwise all records are removed.-Usage:The DELETE statement follows the syntax:...
and UPDATE
Update (SQL)
An SQL UPDATE statement changes the data of one or more records in a table. Either all the rows can be updated, or a subset may be chosen using a condition.The UPDATE statement has the following form:...
statements.
These additional features make Transact-SQL Turing complete.
Transact-SQL is central to using Microsoft SQL Server
Microsoft SQL Server
Microsoft SQL Server is a relational database server, developed by Microsoft: It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network...
. All applications that communicate with an instance of SQL Server do so by sending Transact-SQL statements to the server, regardless of the user interface of the application.
Flow control
Keywords for flow control in Transact-SQL includeBEGIN and END, BREAK, CONTINUE, GOTO, IF and ELSE, RETURN, WAITFOR, and WHILE.IF and ELSE allow conditional execution. This batch statement will print "It is the weekend" if the current date is a weekend day, or "It is a weekday" if the current date is a weekday.IF DATEPART(dw, GETDATE) = 7 OR DATEPART(dw, GETDATE) = 1
PRINT 'It is the weekend.'
ELSE
PRINT 'It is a weekday.'
BEGIN and END mark a block of statements. If more than one statement is to be controlled by the conditional in the example above, we can use BEGIN and END like this:IF DATEPART(dw, GETDATE) = 7 OR DATEPART(dw, GETDATE) = 1
BEGIN
PRINT 'It is the weekend.'
PRINT 'Get some rest in weekend!'
END
ELSE
BEGIN
PRINT 'It is a weekday.'
PRINT 'Get to work in weekday!'
END
WAITFOR will wait for a given amount of time, or until a particular time of day. The statement can be used for delays or to block execution until the set time.RETURN is used to immediately return from a stored procedureStored procedure
A stored procedure is a subroutine available to applications that access a relational database system. A stored procedure is actually stored in the database data dictionary.Typical uses for stored procedures include data validation or access control mechanisms...
or function.
BREAK ends the enclosing WHILE loop, while CONTINUE causes the next iteration of the loop to execute. An example of a WHILE loop is given below.DECLARE @i INT
SET @i = 0
WHILE @i < 5
BEGIN
PRINT 'Hello world.'
SET @i = @i + 1
END
Changes to DELETE and UPDATE statements
In Transact-SQL, both the DELETE and UPDATE statements allow a FROM clause to be added, which allows joins to be included.This example deletes all
users who have been flagged with the 'Idle' flag.DELETE users
FROM users as u
INNER JOIN user_flags as f
ON u.id=f.id
WHERE f.name = 'Idle'
BULK INSERT
BULK INSERT is a Transact-SQL statement that implements a bulk data-loading process, inserting multiple rows into a table, reading data from an external sequential file. Use of BULK INSERT results in better performance than processes that issue individual INSERT statements for each row to be added. Additional details are available on Microsoft's MSDN page.TRY CATCH
Beginning with SQL Server 2008, Microsoft introduced additional TRY CATCH logic to support exception type behaviour. This behaviour enables developers to simplify their code and leave out @@ERROR checking after each SQL execution statement.-- begin transaction
BEGIN TRAN
BEGIN TRY
-- execute each statement
INSERT INTO MYTABLE(NAME) VALUES ('ABC')
INSERT INTO MYTABLE(NAME) VALUES ('123')
-- commit the transaction
COMMIT TRAN
END TRY
BEGIN CATCH
-- rollback the transaction because of error
ROLLBACK TRAN
END CATCH
See also
- Adaptive Server Enterprise (Sybase)Adaptive Server EnterpriseAdaptive Server Enterprise is Sybase Corporation's flagship enterprise-class relational model database server product. ASE is predominantly used on the Unix platform but is also available for Windows.-History:...
- PL/SQL (Oracle)PL/SQLPL/SQL is Oracle Corporation's procedural extension language for SQL and the Oracle relational database...
- PL/pgSQL (PostgreSQL)PL/pgSQLPL/pgSQL is a procedural language supported by the PostgreSQL ORDBMS. It closely resembles Oracle's PL/SQL language....