Lesson 3:Transact-SQL Syntax Elements | MCSE Training Kit (Exam 70-229): Microsoft SQL Server(TM) 2000 Database Design and Implementation (Pro Certification)

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.

After this lesson, you will be able to:

Describe and use many of the syntax elements used in Transact-SQL statements, including identifiers, variables, functions, data types, expressions, control-of-flow language, and comments.
Create and execute Transact-SQL statements that include many of the syntax elements discussed in this lesson.

Estimated lesson time: 35 minutes

Identifiers

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.

Classes of Identifiers

There are two classes of identifiers: regular and delimited.

Regular Identifiers

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

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.

Variables

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:

As a counter, either to count the number of times a loop is performed or to control how many times the loop is performed
To hold a data value to be tested by a control-of-flow statement
To save a data value to be returned by a stored procedure return code

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.

Functions

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:

Built-in functions. These functions operate as defined in Transact-SQL and cannot be modified. The functions can be referenced only in Transact-SQL statements.
User-defined functions. These functions enable you to define your own Transact-SQL functions by using the CREATE FUNCTION statement.

Built-In Functions

The Transact-SQL programming language contains three types of built-in functions: rowset, aggregate, and scalar.

Rowset Functions

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

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:

The select list of a SELECT statement (either a subquery or an outer query)
A COMPUTE or COMPUTE BY clause
A HAVING clause

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

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

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.

Types of User-Defined Functions

The Transact-SQL programming language supports two types of user-defined functions: scalar and table.

Scalar Functions

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

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.

Function Determinism

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.

Data Types

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."

Expressions

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.

Using Operators in Expressions

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 Elements

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

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:

Double hyphens ( --). These comment characters can be used on the same line as code to be executed or on a line by themselves. Everything from the double hyphens to the end of the line is part of the comment. For a multiple-line comment, the double hyphens must appear at the beginning of each comment line, as shown in the following example:

 USE Northwind GO -- First line of a multiple-line comment. -- Second line of a multiple-line comment. SELECT * FROM Employees GO

Forward slash-asterisk pairs ( /*...*/ ). These comment characters can be used on the same line as code to be executed, on lines by themselves, or even within executable code. Everything from the open-comment pair ( /* ) to the close-comment pair ( */ ) is considered part of the comment. For a multiple-line comment, the open-comment character pair ( /* ) must begin the comment, and the close-comment character pair ( */ ) must end the comment. No other comment characters should appear on any lines of the comment, as shown in the following example:

 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.

Exercise 3: Using Transact-SQL Syntax Elements to Create a Script

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.

To create a table in the Northwind database

Open SQL Query Analyzer and connect to your local server.
In the Editor pane of the Query window, enter the following Transact-SQL code:

 -- 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

The SET NOCOUNT ON statement is used to stop the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the result. Use the SET NOCOUNT OFF statement at the end of the script to enable the message to appear in subsequent queries.

The GO keyword signals the end of a batch of Transact-SQL statements to the SQL Server utilities. GO is not a Transact-SQL statement; rather, it is a command recognized by the osql and isql utilities and by SQL Query Analyzer. SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to SQL Server. The current batch of statements is composed of all statements entered since the last GO or since the start of the ad hoc session or script (if this GO is the first GO).

To declare and initialize a variable and use control-of-flow language

Directly below the Transact-SQL statements that you just created, add the following code:

 -- 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.

Adding to the script that you already created, enter the following code:

 /* 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.

To execute the Transact-SQL script and then drop the table from the database

Execute the script in its entirety.

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).

Execute the following SELECT statement:

 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.

Execute the following SELECT statement:

 DROP TABLE "New Table"

The Messages tab displays a message indicating that the command has been completed successfully.

Close SQL Query Analyzer.

You can save the Transact-SQL script for later use if you like; otherwise, close SQL Query Analyzer without saving the script.

Lesson Summary

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).