In a relational database the use of JOIN is invaluable when combining data in one table to another. Through stored procedures the ability to use JOIN to combine physical tables to views, temporary tables, derived tables and table variables is equally important in our realm of data retrieval activities.

In SQL Server 2000, the table-valued function was introduced. The table-valued function provides a user defined function the ability to return a table object. The ability to combine the results of a table-valued function has been available through the use of JOIN; but presented some challenges when the argument that is to be passed is a value that changes for each record of the parent table. The following statement will result in a syntax error:

    SELECT
&nbs ...

If date and time precision is important to you, an odd experience may be waiting for your discovery.  No, it is not a wrinkle in time it is the rounding of milliseconds in the datetime data type.

According to SQL Server 2000 Books On Line (BOL), the DateTime data type is described as follows:
Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table.

Below is a series of data entries into a table that illustrate the affect of the millisecond rounding. The value on the left is the date and time value captured in a varchar data type which reflects the actual value that was submitted to the database. The value on the right is the date and time value captured in a datetime datatype that reflects the millisecond rounding that is described by BOL.< ...

As we work with other Professionals in our industry, we quickly learn that there are many ways to accomplish a given task. When presented with multiple options in a situation, there are times where one option stands out as the optimal choice based upon our general knowledge of the database engine. There are also times where the optimal choice may not be so obvious and will vary depending on many considerations such as database architecture, level of use, indexing, hardware configuration or general best practices. Any given approach may perform superbly with one database and drag on another.

A sample case might be that we are given two versions of a seemingly simple query that filters the data. One option is the common use of a WHERE clause. The other is utilizing the filters within the JOIN clause. The examples below illustrate the syntax differences between these options.

WHERE Filter Option< ...

In my previous blog entries I have spoken about temporary tables, table variables and derived tables when building stored procedures. All of these are useful features of SQL Server. When SQL Server 2005 was released another feature was added that joins in this happy family of temporary datasets: Common Table Expressions (CTE).

The syntax of a CTE is:
    
    WITH [CTE Name] ([Columns To Return]) AS ([SQL Statement);

A sample of this syntax is:

    USE [AdventureWorks];

    WITH myCTE (
              [AddressID],
   &a ...