Integrate Database Mail

Database Mail is a shiny new feature of SQL Server, it is the successor of SQLMail, so use this one instead. It doesn’t rely on any external programs (e.g. Outlook) in contrast of its predecessor. It uses a simple SMTP server.

You should be familiar with one stored procedure: msdb.dbo.sp_send_dbmail, which accepts a whole lot of parameters. The most important ones are:

  • @profile_name: specifies the Database Mail profile to use when sending the mail.
  • @recipients: the recipients of the message, in a comma delimited list.
  • @(blind_)copy_recipients: CC and BCC.
  • @subject: the subject of the message.
  • @body: the content of the message.
  • @query: the query to attach to (or place in the body of) the message.
  • @execute_query_database: the name of the database the query should be executed in.
  • @append_query_error: a bit value. If you set  it to one, it sends the mail if the specified query fails, along with the error message.

Continue reading “Integrate Database Mail”

Create and modify constrains

May include but is not limited to: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, cascading referential integrity, enabling/disabling, NOCHECK, SET IDENTITY_INSERT

Some of us (including me) with the ASP.NET WebForms background may think that validating data before it can be sent to the database eliminates the need of further refinements. However, we don’t have the power of ensuring that our database can be only accessed from our application, and some extra layers of validation certainly won’t hurt for anyone.

SQL Server 2008 allows a set of validation mechanisms, such as ensuring that two rows can’t hold the same values, identities, and cascading referential support. We can also define custom validation logic with the help of the CHECK keyword. But let’s start from the beginning.

SQL Server stores relational data. To be able to query this data efficiently, it’s a good idea to create a key for each column in a table. Primary keys are the tool that’s designed for this. A primary key creates a set of unique identifiers on a given column, thus preventing the same data to be inserted, and even creates clustered indexes.

There are three ways to define a primary key: in the CREATE TABLE clause, you can set one on the column level, on the table level, or you can use an ALTER TABLE clause to add one later. I think the only one worth mentioning is adding a constraint later:
Continue reading “Create and modify constrains”

Control execution plans

May include but is not limited to: table hints, query hints

As you may know, SQL Server tries to use the best possible way to execute a query. It has very powerful query optimization techniques, with which you’ll be happy most of the time. But sometimes (very very rarely) you need to provide hints on how to execute a given statement (in a real world, you’d never do that, because it’s the responsibility of the DBA).

Fortunately, there are a bunch of built-in query hints. Even more fortunately, there isn’t a single goddamn word on these hints (to be funny, there isn’t any hint of them) in the Training Kit, so let’s query our old friend, MSDN.

Now there are two kind of these hints, query and table. Let’s start with the former. You can set a query hint on any query (the sole exception is when you use UNIONs), by adding the OPTION keyword, along with the desired hint to it. Here’s a brief list of what you may encounter:
Continue reading “Control execution plans”

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.

Capture execution plans

May include but is not limited to: graphical execution plans, SHOWPLAN

I’m a bit confused about this one being a separate objective. As all of us know, SQL Server produces graphical (and textual) execution plans for your queries. You can watch these plans any time you’d like by pressing Ctrl+M.

A few things to know about execution plans:

  • They are read from left to right, and from top to bottom.
  • The thicker an arrow, the more data it returns.

I think that’s good enough. Another way to access graphical execution plans is the SHOWPLAN clause. When specifying it, SQL Server returns the execution plan in an XML format, without executing the commands. The syntax to use:

SET SHOWPLAN_XML ON

If you prefer plain text, use the SHOWPLAN_TEXT clause instead. But make sure you turn off these features, because you only get execution plans as a result.

You can get additional information about your queries when using the STATISTICS clause. The difference is that when you specify STATISTICS, you get a result after your query is executed. There are four things to query for: XML, PROFILE, IO, TIME. The syntax is:

SET STATISTICS TIME ON

Then you can view the results in the  Messages pane in SSMS.

Implement CTE (common table expression) queries

May include but is not limited to: recursive, non recursive

A common table expression is a powerful new tool which generates a named result set for the lifetime of the current query. In some cases, they can replace views (you don’t always need views), and therefore reducing complexity. They can be referenced by their name in the query multiple times, and they can be self-referencing, thus enabling recursion.

Here’s how a CTE looks like:

WITH Name of CTE
( List of columns)
AS
( SELECT statement )
Outer SELECT statement

This structure is fairly useful, but let’s see the real cool feature, the self-referencing and the recursion:

WITH Numbers (n)
AS
(SELECT 1 AS n
UNION ALL
SELECT n + 1
FROM Numbers
WHERE n < 10)
SELECT n FROM Numbers

The sample above, which originates from Michael Coles: Pro T-SQL 2008 Programmer’s Guide p.255, does nothing but lists the numbers from one to ten. But it does this in a recursive manner, which opens up new horizons in data access code.

A last thing to remember: CTEs have a recursion level of a hundred, by default. You can override this behavior by specifying the MAXRECURSION option, can be 0 to 32767.

Manage international considerations

May include but is not limited to: collations, defining custom errors, filtering data, sort order, nvarchar, database collation, column collation

SQL Server supports a tremendous set of different cultures and languages, which leads to interesting things. For a second, try to imagine my situation. As a native Hungarian, I constantly have to battle with characters like ‘ő’ or ‘í’, and so on. Let’s I send you a table with the names of my customers, who happen to be Hungarians, too. How would you sort this table, with names like Álmos or Éva?

Every language and accent has sorting rules and SQL Server respects those rules, and enforces them within collations. In Hungary, the letter á comes immediately after a, and not after z. But in Latin, á is seen as an accent variable of a. Now the sorting is obfuscated! Even worse, what if we’re filtering? In Latin, ‘á’ = ‘a’ would return true, but in Hungarian, it certainly isn’t the case.

If you create a database, SQL Server asks you which collation you’d like to use (or uses the default one, if you don’t specify anything). You have the ability to override this setting on the column level, using the COLLATE keyword, and the collation name. For example:

CREATE TABLE GreekTable
( Name nvarchar(20) COLLATE greek_ci_as)

I’m sure you found out which part of the code above sets the collation to Greek. The other two values CI means case insensitive (CS is the case-sensitive one), and AS means accent-sensitive (guess how they mark accent-insensitive).

The last thing in this post will be the SQL string types, stay tuned! We’ve all encountered them, char, nchar, varchar, nvarchar, text, ntext… But what do they do? The good news is that text and ntext are deprecated, so avoid using them. The others: n means Unicode, var means variable length. When you use char and varchar (the ones without the n prefix), you use the underlying collation to specify the code page of the string. This is not the case with Unicode strings, which can store any character, because it uses two bytes for each of them. In both cases, the specified collation determines the filtering and sorting rules.