Apply built-in scalar functions

May include but is not limited to: CAST and CONVERT; REPLACE; LEN and DATALENGTH; PATINDEX and CHARINDEX

You can define functions in SQL Server for enhancing your select or where clauses. These functions are called user-defined functions (a shocking name for them). However, there are many-many predefined functions you can work with, and they are called built-in functions. Now we’ll take a closer look on them.

The main purpose of these scalar functions is to enhance your select statements. You can also use them in your where clause, but with a considerable performance overhead, so try to restrict yourself here.

Because there are so many built-in functions, it’s a good idea to categorize them. Microsoft did this for us, so we know date/time functions,  system functions, string functions, and additional functions.
Continue reading

Advertisements

Modify data by using MERGE statements

May include but is not limited to: INSERTED, DELETED, OUTPUT

Merge is a shiny new feature in SQL Server 2008, which lets you specify complex, conditioned data modifications within a single statement. For the minimum, you need to include the MERGE keyword, the target and the source tables, a condition on how to match those tables, and what should happen when matches are found/not found. I’m quite sure that a code sample would be much more explanatory, so here’s one:

MERGE Customers AS target
USING Agents AS source
ON (target.CustomerID = source.CustomerID)
WHEN MATCHED THEN
                UPDATE
                SET Name = source.Name
WHEN NOT MATCHED THEN
                INSERT (CustomerID, CustomerName)
                VALUES (source.CustomerID, source.CustomerName)
;

I have deliberately put the semicolon on a new line, because it’s necessary to close your MERGE commands with them.

Furthermore, you can specify the matches you are interested in. This means you can write a WHEN NOT MATCHED BY SOURCE/TARGET THEN statement. It’s quite straightforward, but I’d mention here that you cannot issue a DELETE or UPDATE command when you don’t match by target, and you cannot issue an INSERT when you don’t match by source.

The MERGE command is a very powerful tool, make sure you use it regularly.

Combine datasets

This objective may include but is not limited to: CROSS APPLY, OUTER APPLY, all join types, UNION, UNION ALL, INTERSECT, EXCEPT

Datasets tend to reside in different tables. Usually you need to find some technique to create a unified dataset from the different tables. The combining functions provides just this.

We’ll discuss the following clauses here: JOIN, APPLY, UNION, INTERSECT, EXCEPT.

Let’s start with the simplest ones. A JOIN (the types are LEFT (OUTER) JOIN, RIGHT (OUTER) JOIN, (INNER) JOIN) is simply joins two or more tables, which have the same values in one or more of their columns. INNER JOIN returns those rows where both tables have the same values. LEFT OUTER returns all values from the left side of the operation, right does the same with the right side. There is also a CROSS JOIN, which results in the Cartesian product of the tables queried.

UNIONs can be used to merge two result sets into one, thus the products of more select statements results in one dataset. However, you must make sure that you define UNION between the same number of columns, with compatible data types. Be aware of that the column titles are from the first select statement, so you should define aliases in the first select query. The ALL keyword after the UNION specifies that even duplicate rows should be returned.

There are two more methods of combining datasets. The first one is using the INTERSECT and EXCEPT clauses. INTERSECT returns only those rows which are present in both result sets (defined as the intersection of them), EXCEPT returns those which are present in only one result set.

The last way to combine is the APPLY operator. It has two forms: OUTER APPLY and CROSS APPLY.  These operators lets you use columns from the left-side table to be passed as parameters for a table-valued function of the right side. CROSS APPLY returns every row for which the TVF produces results, while OUTER APPLY, just like an OUTER JOIN, returns all rows, even those for which there’s no result.

Implement aggregate queries

May include but is not limited to: built-in aggregate functions, GROUPING SETS, GROUP BY, HAVING.

Aggregation is a powerful feature of SQL Server, which enables you to aggregate data. After this fantastic prologue, let’s see the built-in aggregate functions of SQL Server 2008:

  • AVG: returns the average of the result set.
  • CHEKSUM_AGG: returns the checksum  of all values of the result set.
  • COUNT: returns the number of rows in the result set. NULL values omitted.
  • COUNT_BIG: same as COUNT, but returns a bigint.
  • GROUPING: returns 0 for detail rows, and 1 for aggregate rows, when the GROUP BY clause is used.
  • MAX: returns the maximum value of a sequence.
  • MIN: returns the minimum value of a sequence.
  • SUM: returns the total of a result set.

The others aren’t so interesting. When working with aggregate functions, you often want to group your data based on those aggregates. You can do so by specifying the GROUP BY clause. All columns listed in the SELECT clause must be an aggregate, or listed in the GROUP BY clause.

You can use the WITH CUBE and WITH ROLLUP functions to return subtotals for more than one column.  WITH ROLLUP is used to get subtotals for a single column. WITH CUBE can handle multiple columns.

To filter the results based on an aggregate function, you can use the HAVING clause. It is very similar to the WHERE clause, the key difference is that it is designed to work with aggregates.

Return data by using the OUTPUT clause

This objective may include but is not limited to: INSERTED, DELETED, INTO.

Sometimes you might need values from an insert/update/delete. One method is to use DML triggers, which I’ll cover later. The other way is (available from SQL Server 2005) is to use the OUTPUT clause in conjunction with a DML statement (DML is stands for Data Modification Language, one of the fundamental parts of SQL).

The syntax is simple and straightforward:

INSERT INTO Employees
(LastName, FirstName)
OUTPUT inserted.EmployeeID INTO AuditTable
VALUES(‘Gergely’, ‘Koncz’);

A delete is essentially the same, but you need to refer to the table affected as deleted:

DELETE FROM Employees
OUTPUT deleted.EmployeeID INTO AuditTable
WHERE EmployeeID = 10;

The interesting part is an update. An update, from the point of view of a trigger, or the OUTPUT clause, is a DELETE and an INSERT statement. You can get the values of the specified row before the update operation by querying the deleted virtual table, and the values after the update by the inserted virtual table. The syntax:

UPDATE Employees
SET LastName = ‘Zac’
OUTPUT deleted.EmployeeID, inserted.EmployeeID INTO AuditTable
WHERE EmployeeID = 10;

Query data by using SELECT statements

May include but is not limited to: LIKE, WHERE, ORDER BY, INTO

To retrieve data from an SQL database, you need to write SELECT commands. A minimal SELECT is as follows:

SELECT * FROM Table

Where * means all the columns in the given table. Typically, it’s not a good idea to retrieve all columns, because of the performance penalties, so you should replace the asterisk symbol with the concrete column names, like this:

SELECT Column1, Column2, Column3 FROM Table

So simple, so good. But generally, you need to filter your searches by some logic. The WHERE keyword does just this:

SELECT Column1, Column2 FROM Table
WHERE Column1 = 1

There are multiple further conditions can be applied to a WHERE clause, which forms a Boolean expression. These are the following: LIKE, BETWEEN, EXISTS, IS (NOT) NULL, CONTAINS. Let’s examine the LIKE clause deeper. It lets you find a matching character string in the column specified in the WHERE clause. It uses the following wildcards:
Continue reading