Transact-SQL

Microsoft's implementation of the Structured Query Language (SQL) standard for SQL Server is named Transact- SQL (T- SQL ) . This language may be used in order to perform ad-hoc queries submitted at runtime or precompiled stored procedures stored on the server itself. Both methods use variations of the following basic SQL statements:

• The SELECT statement Used to retrieve stored data

• The INSERT statement Used to add new data

• The UPDATE statement Used to modify existing data

• The DELETE statement Used to remove existing data

T-SQL is derived from the American National Standards Institute (ANSI) SQL-92 standard.

The SELECT Statement

The SELECT statement is used to retrieve data from one or more tables in a database. A SELECT statement looks like this:

 SELECT field_list  FROM table_list WHERE where_clause GROUP BY group_by_clause HAVING having_clause ORDER BY sort_clause 

The order of these clauses is fixed, although only the SELECT and FROM clauses are required. The rest are optional, as in this example:

 SELECT CustomerID as CustID, CustomerName as CustName  FROM Customers 

This would return the CustomerID and CustomerName fields for all records in the Customers table, naming the resulting fields CustID and CustName, respectively. It is also possible to select all fields in a table by using the asterisk ( * ) character, as show here:

 SELECT *  FROM Customers 

It is also possible to select data from multiple tables at once, as in the following example:

 SELECT Customers.CustomerID as CustID, O.OrderID as OrdID  FROM Customers INNER JOIN Orders as O  ON Customers.CustomerID = O.CustomerID 

Note the use of the <table1> INNER JOIN <table2> ON <relationship> syntax. This causes the result to only include those records in each table that have a matching value in the other table. Without this, the query could result in a cross-product result set, where every field in one table is individually matched against every field in the other table, which can rapidly bloom into a huge result set. A cross-product of two tables, each with only 100 records, will result in 10,000 records returned (100x100=10,000).

You may restrict the result set generated by a SELECT query by including a WHERE clause, as shown in this example:

 SELECT TOP 50 *  FROM Customers  WHERE State = 'Washington'  AND CustomerName LIKE 'B%'  AND CurrentCredit < 5000  ORDER BY CurrentCredit DESC, CustomerName 

This example selects the first 50 matching records from the Customers database, where the State field holds the value 'Washington' , the CustomerName value begins with the letter B , and the customer's CurrentCredit value is less than 5000. When the first 50 matching records are selected, the order is determined by the ORDER BY clause. In this case, the records are sorted in descending order on the CurrentCredit value first and then in ascending order on the CustomerName field when the CurrentCredit values match. You can use any expression you like in an ORDER BY clause, although the resulting records will only be ordered if the expression refers to data columns you're returning in the SELECT clause.

Wildcard values may be used in SQL language queries by using the LIKE keyword instead of the equals sign ( = ) and placing the wildcard symbol in the desired location within the search string. SQL Server supports two different wildcard symbols. The percent sign ( % ) matches any number of characters, including zero characters . The underscore ( _ ) matches any single character. It is also possible to search for a single-replacement character within a set of values using square brackets ( [ ] ). In this way,

 ...WHERE ProductID LIKE 'PANT-[ABC]'... 

would return all records whose ProductID matches PANT-A, PANT-B, or PANT-C.

It is also possible to perform aggregate functions within a SQL querying statement, as shown here:

 SELECT COUNT(CustomerID) as CustCount, State  FROM [Customer Data]  GROUP BY State  ORDER BY CustCount DESC 

This generates a result set that counts the number of customers in each state from the Customer Data table and is ordered by the customer count per state in descending order. Note the use of square brackets ( [ ] ) to enclose the Customer Data table name. This is required because the table name includes a space.

Aggregate queries must include both a GROUP BY clause and an aggregate function. The GROUP BY clause tells SQL Server which records should be grouped together. For example, using GROUP BY State specifies that the query results should have one row for each value in the State column.

Aggregate functions specify how to calculate the value for each group in the result. Some of the more common aggregate functions used with the GROUP BY clause include the following:

• The COUNT() function Counts the number of returned records

• The SUM() function Adds the values of all returned records

• The AVG() function Returns the average value for all returned records

• The MIN() function Returns the smallest value within all returned records

• The MAX() function Returns the largest value within all returned records

When you're working with aggregate functions, it is important to identify whether a restricting filter is applied in the proper manner. The WHERE clause allows for filtering of values that will be handed to the aggregating function, whereas the HAVING clause may be used in order to filter based on the final aggregated value, as shown here:

 SELECT ProductID, Sum(Quantity) AS TotalSales  FROM [Order Details]  WHERE Quantity > 10  GROUP BY ProductID  HAVING  Sum(Quantity) > 1000  ORDER BY Sum(Quantity) DESC 

In this example, the WHERE clause causes the Sum() action to exclude records with a Quantity value greater than 10, whereas the HAVING clause causes the record set to return only TotalSales values for products whose Quantity value (ignoring the already excluded records) exceeds 1000.

SELECT INTO is a special case of the SELECT statement that may be used to create a new table from the result set, as shown here:

 SELECT * INTO BadCustomers  FROM Customers  WHERE CurrentCredit < 5000 

The INSERT Statement

The INSERT statement is used to add new records to a table and may be used to insert a single record or multiple records in one statement. An example of a single-record insertion is as follows :

 INSERT INTO [Order Details]   (OrderID, ProductID, UnitPrice, Quantity, Discount) VALUES (10248, 2, 12.00, 5, 0) 

Here, the values of the new record are inserted in the order of the defined field list. If the list of values exactly matches the order of the fields in the table, you may omit the field list, as shown here:

 INSERT INTO [Order Details] VALUES (10248, 1, 12.00, 5, 0) 

This method has several drawbacks, including the need to rewrite your queries if the underlying table organization has to be changed as well as instances in which a field may be automatically given a unique value generated by the SQL server.

It is also possible to insert multiple records using a single INSERT statement, as shown in this example:

 INSERT INTO Products (SupplierID, ProductName, CategoryID )  SELECT SupplierID, 'Trout', 8  FROM Suppliers 

Note that a SELECT statement is used to provide the record set that will be inserted into the table.

The UPDATE Statement

The UPDATE statement allows modification of existing values stored within the database. It is possible to update a single record or multiple records in a single statement, even allowing multiple fields to be updated using a single UPDATE statement, as shown here:

 UPDATE Customers  SET ContactName = 'Maria Anders', Discount = Discount * 2  WHERE CustomerID LIKE 'AL%' 

In this example, all records within the Customers table with a CustomerID value starting with the letters AL are updated with a ContactName of 'Maria Anders' and a Discount value that is twice the current Discount value.

Updating values in one table based on those in another related table is also possible, as shown here:

 UPDATE Products  SET Discontinued = 1  FROM Suppliers INNER JOIN Products  ON Suppliers.SupplierID = Products.SupplierID WHERE Suppliers.Country = 'Italy' 

The DELETE Statement

The DELETE statement uses an identical format as the SELECT statement in order to specify those records that will be deleted. Be careful when using this command, particularly when using the * placeholder to specify all records in a table. Otherwise, you might delete records you want to keep!