You might have noticed that some of the earlier queries in this chapter use logical column names for aggregate functions such as COUNT() and SUM(). Labels such as these are known as column aliases and make the query and its result set more readable. As with joins, Transact-SQL provides two separate syntaxes for establishing column aliases: legacy or classical and ANSI standard. In the classical syntax, the column alias immediately precedes the column and the two are separated with an equal sign, like so:
ANSI syntax, by contrast, places a column alias immediately to the right of its corresponding column and optionally separates the two with the AS keyword, like so:
SELECT GETDATE() AS TodaysDate
SELECT GETDATE() TodaysDate
Unlike joins, the column alias syntax you choose won't affect query result sets. This is largely a matter of preference, though it's always advisable to use the ANSI syntax when you can if for no other reason than compatibility with other products.
You can use column aliases for any item in a result set, not just aggregate functions. For example, the following example substitutes the column alias LName for the LastName column in the result set:
SELECT customers.LastName AS LName, COUNT(*) AS NumberWithNameFROM customersGROUP BY customers.LastName
Note, however, that you cannot use column aliases in other parts of the query except in the ORDER BY clause. In the WHERE, GROUP BY, and HAVING clauses, you must use the actual column name or value. In addition to supporting column aliases, ORDER BY supports a variation on this in which you can specify a sort column by its ordinal position in the SELECT list, like so:
SELECT FirstName, LastNameFROM customersORDER BY 2
This syntax has been deprecated and is less clear than simply using a column name or alias.
Similar to column aliases, you can use table aliases to avoid having to refer to a table's full name. You specify table aliases in the FROM clause of queries. Place the alias to the right of the actual table name (optionally separated with the AS keyword), as illustrated here:
SELECT c.LastName, COUNT(*) AS NumberWithNameFROM customers AS cGROUP BY c.LastName
Notice that the alias can be used in the field list of the SELECT list before it is even syntactically defined. This is possible because a query's references to database objects are resolved before the query is executed.
Transaction management is really outside the scope of introductory T-SQL. Nevertheless, transactions are at the heart of database applications development and a basic understanding of them is key to writing good SQL (see Chapter 14, "Transactions," for in-depth coverage of transactions).
The term transaction refers to a group of changes to a database. Transactions provide for change atomicity—which means that either all the changes within the group occur or none of them do. SQL Server applications use transactions to ensure data integrity and to avoid leaving the database in an interim state if an operation fails.
The COMMIT command writes a transaction permanently to disk (technically speaking, if nested transactions are present, this is true only of the outermost COMMIT, but that's an advanced topic). Think of it as a database save command. ROLLBACK, by contrast, throws away the changes a transaction would have made to the database; it functions like a database undo command. Both of these commands affect only the changes made since the last COMMIT; you cannot roll back changes that have already been committed.
Unless the IMPLICIT_TRANSACTIONS session variable has been enabled, you must explicitly start a transaction in order to commit or roll it back. Transactions can be nested, and you can check the current nesting level by querying the @@TRANCOUNT automatic variable, like so:
SELECT @@TRANCOUNT AS TranNestingLevel
Here's an example of some Transact-SQL code that uses transactions to undo changes to the database:
BEGIN TRANDELETE customersGOROLLBACKSELECT * FROM customersCustomerNumber LastName FirstName StreetAddress City State Zip-------------- -------- --------- --------------- ----- ----- -----1 Doe John 123 Joshua Tree Plano TX 750252 Doe Jane 123 Joshua Tree Plano TX 750253 Citizen John 57 Riverside Reo CA 90120
As you can see, ROLLBACK reverses the row removals carried out by the DELETE statement.
Be sure to match BEGIN TRAN with either COMMIT or ROLLBACK. Orphaned transactions can cause serious performance and management problems on the server.
Page 9 of 10