3 4
Transact-SQL includes many syntax elements that are used by or that influence most statements. These elements include identifiers, variables, functions, data types, expressions, control-of-flow language, and comments. This lesson discusses these elements and provides examples that illustrate how they are applied in Transact-SQL statements.
The database object name is known as its identifier. Everything in SQL Server can have an identifier, including servers, databases, and database objects such as tables, views, columns, indexes, triggers, procedures, constraints, and rules. Identifiers are required for most objects but are optional for some objects (such as constraints).
An object identifier is created when the object is defined. The identifier is then used to reference the object. For example, the following statement creates a table with the identifier TableX and two columns with the identifiers KeyCol and Description:
CREATE TABLE TableX (KeyCol INT PRIMARY KEY, Description NVARCHAR(80))
This table also has an unnamed constraint, PRIMARY KEY, which has no identifier.
There are two classes of identifiers: regular and delimited.
Regular identifiers conform to the rules for the format of identifiers. They are not delimited when used in Transact-SQL statements. The following SELECT statement includes two identifiers: TableX and KeyCol (neither of which are delimited):
SELECT * FROM TableX WHERE KeyCol = 124
For information about how identifiers should be formatted, refer to SQL Server Books Online.
Delimited identifiers are enclosed in double quotation marks ( " "
) or in brackets ( [ ] ). Identifiers that comply with the rules for the format of identifiers may or may not be delimited. In the following SELECT statement, the delimiters are optional:
SELECT * FROM [TableX] WHERE [KeyCol] = 124
Identifiers that do not comply with all of the rules for identifiers must be delimited in a Transact-SQL statement. In the following SELECT statement, you must use a delimiter for My Table because the identifier contains a space, and you must use a delimiter for Order because the identifier is also a reserved keyword.
SELECT * FROM [My Table] WHERE [Order] = 10
For information about when and how identifiers should be delimited, refer to SQL Server Books Online.
A Transact-SQL variable is an object in Transact-SQL batches and scripts that can hold a data value. After the variable has been declared or defined, one Transact-SQL statement in a batch can set the variable to a value—and a later statement in the batch can get the value from the variable. The following Transact-SQL statements declare a variable named EmpIDVar, set the value for that variable to 3, and then use the variable in a SELECT statement:
USE Northwind DECLARE @EmpIDVar INT SET @EmpIDVar = 3 SELECT * FROM Employees WHERE EmployeeID = @EmpIDVar + 1
Variables in batches and scripts are typically used for the following functions:
Variables are often used in a batch or procedure as counters for WHILE and LOOP statements or for IF...ELSE blocks. Variables can be used only in expressions, however, and not in place of object names or keywords.
A function encapsulates frequently performed logic in a subroutine made up of one or more Transact-SQL statements. Any code that must perform the logic incorporated in a function can call the function rather than having to repeat all of the function logic.
SQL Server2000 supports two types of functions:
The Transact-SQL programming language contains three types of built-in functions: rowset, aggregate, and scalar.
Rowset functions can be used like table references in a Transact-SQL statement. These functions return an object that can be used in place of a table reference in a Transact-SQL statement. For example, the OPENQUERY function is a rowset function that executes the specified pass-through query on the given linked server, which is an OLE DB data source. The OPENQUERY function can be referenced in the FROM clause of a query as though it were a table name.
All rowset functions are non-deterministic; that is, they do not return the same result every time they are called with a specific set of input values. Function determinism are discussed in more detail later in this section.
Aggregate functions operate on a collection of values but return a single, summarizing value. For example, the AVG function is an aggregate function that returns the average of the values in a group.
Aggregate functions are allowed as expressions only in the following statements:
With the exception of COUNT, aggregate functions ignore null values. Aggregate functions are often used with the GROUP BY clause of the SELECT statement.
All aggregate functions are deterministic; they return the same value any time they are called with a given set of input values.
Scalar functions operate on a single value and then return a single value. Scalar functions can be used wherever an expression is valid. Scalar functions are divided into categories, as described in the following table:
Scalar Category | Description |
---|---|
Configuration functions | Return information about the current configuration |
Cursor functions | Return information about cursors |
Date and time functions | Perform an operation on a date and a time input value and return either a string, numeric, or date and time value |
Mathematical functions | Perform a calculation based on input values provided as parameters to the function and return a numeric value |
Metadata functions | Return information about the database and database objects |
Security functions | Return information about users and roles |
String functions | Perform an operation on a string (char or varchar) input value and return a string or numeric value |
System functions | Perform operations and return information about values, objects, and settings in SQL Server |
System statistical functions | Return statistical information about the system |
Text and image functions | Perform an operation on a text or image input value or column and return information about the value |
Each category of scalar functions includes its own set of functions. For example, the MONTH function, which is included in the date and time category, is a scalar function that returns an integer representing the month part of a specified date.
User-defined functions are created by using the CREATE FUNCTION statement, are modified by using the ALTER FUNCTION statement, and are removed by using the DROP FUNCTION statement. Each fully qualified user-defined function name (database_name.owner_name.function_name) must be unique.
A user-defined function takes zero or more input parameters and returns either a scalar value or a table. A function can have a maximum of 1024 input parameters. When a parameter of the function has a default value, the keyword DEFAULT must be specified when calling the function to get the default value. This behavior is different from parameters that have default values in stored procedures in which omitting the parameter also implies the default value. User-defined functions do not support output parameters.
The Transact-SQL programming language supports two types of user-defined functions: scalar and table.
Scalar functions return a single data value of the type defined in a RETURNS clause. You can use all scalar data types, including bigint and sql_variant. The timestamp data type, user-defined data type, and non-scalar types (such as table or cursor) are not supported. The body of the function, defined in a BEGIN...END block, contains the series of Transact-SQL statements that return the value. The return type can be any data type except text, ntext, image, cursor, and timestamp.
Table functions return a table. There are two types of table functions: inline and multi-statement. For an inline table function, there is no function body; instead, the table is the result set of a single SELECT statement. For a multi-statement table function, the function body, defined in a BEGIN...END block, contains the Transact-SQL statements that build and insert rows into the table that will be returned.
The statements in a BEGIN...END block cannot have any side effects. Function side effects are any permanent changes to the state of a resource that has a scope outside the function, such as a modification to a database table. The only changes that can be made by the statements in the function are changes to objects local to the function, such as local cursors or variables. Modifications to database tables, operations on cursors that are not local to the function, sending e-mail, attempting a catalog modification, and generating a result set that is returned to the user are examples of actions that cannot be performed in a function.
SQL Server functions are either deterministic or non-deterministic. Functions are deterministic when they always return the same result any time they are called with a specific set of input values. Functions are non-deterministic when they could return a different result each time they are called, even with the same specific set of input values.
The determinism of functions dictates whether they can be used in indexed computed columns and indexed views. Index scans must always produce a consistent result. Thus, only deterministic functions can be used to define computed columns and views that are to be indexed.
Configuration, cursor, metadata, security, and system statistical functions (as well as other built-in functions) are non-deterministic.
A data type is an attribute defining the type of data that an object can contain. Columns, parameters, variables, functions that return data values, and stored procedures that have a return code all have data types. Transact-SQL includes a number of base data types, such as varchar, text, and int. All data that is stored in SQL Server must be compatible with one of these base data types. You can create user-defined data types, but these data types are always defined in terms of a base data type.
Data types are discussed in detail in Chapter 4, "Creating Database Objects."
An expression is a combination of identifiers, values, and operators that SQL Server can evaluate in order to obtain a result. Expressions can be used in several different places when accessing or changing data. Expressions can be used, for example, as part of the data to retrieve (in a query) or as a search condition, to look for data that meets a set of criteria.
Operators enable you to perform arithmetic, comparison, concatenation, or assignment of values. For example, you can test data to verify that the country column for your customer data is populated (or not NULL).
In queries, anyone who can see the data in the table requiring an operator can perform operations. You need the appropriate permissions before you can successfully change the data.
SQL Server has seven categories of operators. The following table describes each of those categories:
Operator Category | Description |
---|---|
Comparison | Compares a value against another value or an expression |
Logical | Tests for the truth of a condition, such as AND, OR, NOT, LIKE, ANY, ALL, or IN |
Arithmetic | Performs addition, subtraction, multiplication, division, and modulo |
Unary | Performs an operation on only one expression of any of the data types of the numeric data type category |
Bitwise | Temporarily turns a regular numeric value (such as 150) into an integer and performs bitwise (0 and 1) arithmetic |
String concatenation | Either permanently or temporarily combines two strings (character or binary data) into one string |
Assignment | Assigns a value to a variable or associates a result set column with an alias |
The following SELECT statement uses arithmetic operators to subtract the part of the year-to-date sales that the author receives (sales X author's royalty percentage / 100) from the total sales. The result is the amount of money that the publisher receives. The product of ytd_sales and royalty is calculated first because the operator is multiplication. Next, the total is divided by 100. The result is subtracted from ytd_sales.
USE pubs SELECT title_id, ytd_sales - ytd_sales * royalty / 100 FROM titles
Control-of-flow language consists of special words that control the flow of execution in Transact-SQL statements, statement blocks, and stored procedures. These words can be used in Transact-SQL statements, batches, and stored procedures.
Without control-of-flow language, separate Transact-SQL statements are performed sequentially, as they occur. Control-of-flow language permits statements to be connected, related to each other, and made interdependent by using programming-like constructs. Control-of-flow statements cannot span multiple batches or stored procedures, however.
Control-of-flow keywords are useful when you need to direct Transact-SQL to take some kind of action. For example, use a BEGIN...END pair of statements when including more than one Transact-SQL statement in a logical block. Use an IF...ELSE pair of statements when a certain statement or block of statements needs to be executed if some condition is met, and another statement or block of statements should be executed if that condition is not met (the ELSE condition).
The following table describes the control-of-flow keywords that are included in Transact-SQL:
Keyword | Description |
---|---|
BEGIN...END | Encloses a series of Transact-SQL statements so that a group of Transact-SQL statements can be executed |
BREAK | Exits the innermost WHILE loop |
CONTINUE | Restarts a WHILE loop |
GOTO | Causes the execution of a Transact-SQL batch to jump to a label without executing the statements between the GOTO statement and the label |
IF...ELSE | Imposes conditions on the execution of a Transact-SQL statement, and if ELSE is used, it introduces an alternate statement that is executed when the IF condition is not satisfied |
RETURN | Unconditionally terminates a query, stored procedure, or batch |
WAITFOR | Suspends the execution of a connection until either a specified time interval has passed or until a specified time of day is reached |
WHILE | Repeats a statement or block of statements as long as a specified condition remains true; commonly used with BREAK or CONTINUE statements |
Comments are non-executing text strings in program code (also known as remarks). Comments can be used to document code or to temporarily disable parts of Transact-SQL statements and batches that are being diagnosed. Using comments to document code makes future program-code maintenance easier. Comments are often used to record the program name, the author name, and the dates of major code changes. Comments can be used to describe complicated calculations or to explain a programming method.
SQL Server supports two types of comment characters:
USE Northwind GO -- First line of a multiple-line comment. -- Second line of a multiple-line comment. SELECT * FROM Employees GO
USE Northwind GO /* First line of a multiple-line comment. Second line of a multiple-line comment. */ SELECT * FROM Products GO
Multiple-line /* */ comments cannot span a batch. The complete comment must be contained within a batch. For example, in SQL Query Analyzer and the osql utility, the GO command signals the end of a batch. When the utilities read the characters GO in the first two bytes of a line, they send all of the code since the last GO command to the server as one batch. If a GO occurs at the start of a line between the /* and */ delimiters, then an unmatched comment delimiter will be sent with each batch (triggering syntax errors). All alphanumeric characters or symbols can be used within the comment. SQL Server ignores all characters within a comment (except the GO command). In addition, there is no maximum length for a comment within a batch. A comment can consist of one or more lines.
In this exercise, you will use SQL Query Analyzer to create scripts incorporating the various syntax elements of Transact-SQL that are discussed in this lesson. To perform this exercise, you should be logged into your Windows 2000 Server computer as Administrator.
-- Select database. USE Northwind GO -- Create the table. CREATE TABLE [New Table] (ColumnA INT, ColumnB CHAR(3)) GO SET NOCOUNT ON GO
In this statement, you are identifying the database to be used and are creating a table whose identifier is New Table. Notice that this identifier is a delimited identifier and is enclosed in brackets. The object name contains a space as well as a Transact-SQL keyword (TABLE). The column names (ColumnA and ColumnB) are regular identifiers and do not require brackets or quotation marks. In addition, the columns are also defined with the int and char data types.
The code also includes comments that indicate that the code directly beneath the comment is used to select the database or to create a table. Comments will be used throughout this script to identify what each portion of the script is supposed to achieve.
NOTE
-- Declare the variable to be used. DECLARE @MyCounter INT -- Initialize the variable. SET @MyCounter = 0
This statement declares the @MyCounter variable, defines that variable with the int data type, and assigns the variable a value of 0.
/* Use the variable to define when the loop should be completed.*/ WHILE (@MyCounter < 26) BEGIN -- Insert a row into the table. INSERT INTO [New Table] VALUES -- Use the variable to provide the integer value -- for ColumnA. Also use it to generate a unique letter -- for each row. Use the ASCII function to get the -- integer value of 'a.' Add @MyCounter. Use the CHAR -- function to convert the sum back to the character -- @MyCounter characters after 'a.' ( @MyCounter + 1, CHAR( ( @MyCounter + ASCII('a') ) ) ) /*Increment the variable to count this iteration of the loop.*/ SET @MyCounter = @MyCounter + 1 END GO SET NOCOUNT OFF GO
This statement completes the script. Notice that two control-of-flow language elements are being used here: WHILE and BEGIN...END. In addition, this statement uses expressions and operators (@MyCounter + 1 and @MyCounter + ASCII ('a')) and functions (CHAR and ASCII) to determine the values to enter into the rows. At the end of the script, SET NOCOUNT OFF is used to enable row-count messages to be generated once more.
After you execute the script, the Messages tab is displayed—indicating that the command has been completed successfully. If the SET NOCOUNT ON statement had not been used when this script was executed, the Messages tab would have displayed a row-count message for each row that was added to the table (a total of 26).
SELECT * FROM [New Table]
The result set is displayed in the Grids tab. Notice that there are 26 rows and that the values in the two columns are incremental.
DROP TABLE "New Table"
The Messages tab displays a message indicating that the command has been completed successfully.
You can save the Transact-SQL script for later use if you like; otherwise, close SQL Query Analyzer without saving the script.
Transact-SQL includes many syntax elements that are used by or that influence most statements. These elements include identifiers, variables, functions, data types, expressions, control-of-flow language, and comments. An identifier is a database object name. Every object in SQL Server can have an identifier. A variable is an object in Transact-SQL batches and scripts that can hold a data value. A function encapsulates frequently performed logic in a subroutine made up of one or more Transact-SQL statements. Transact-SQL also contains data types, which are attributes defining the type of data that an object can contain. An expression is a combination of identifiers, values, and operators that SQL Server can evaluate in order to obtain a result. Control-of-flow language consists of special words that control the flow of execution in Transact-SQL statements, statement blocks, and stored procedures. Comments are non-executing text strings in program code (also known as remarks).