Recently I worked on a project where I copied the tables and some of the SQL queries from an Access 2007 database to a SQL Server 2005 database. Most of the queries easily transcribed from Access to SQL Server. After upsizing the tables from Access to SQL Server Express, I simply ran the Access queries in a SQL Management Studio Express query window.
However, two functions, iif() and DLookup(), were inserted into some of my project’s Access SQL queries. These added some excitement to my port, since neither is supported by SQL Server.
This article provides an introduction to these two Access functions, and demonstrates how to convert an Access SQL statement that uses these functions into T-SQL that will work on your SQL Server.
To illustrate these functions, let’s use some sample data that is a list of 8 pets. Each pet has 6 characteristics. The data is listed in the table below.
| Type | HasFur | Barks | NumLegs | Name | Color |
| Dog | Yes | Yes | 4 | Spike | black/brown |
| Dog | Yes | Yes | 4 | Rex | black/white |
| Snake | No | No | 0 | Slither | green |
| Pig | No | No | 4 | Wilbur | pink |
| Cat | Yes | No | 4 | Fluffy | black/white |
| Cat | Yes | No | 4 | Hunter | tabby |
| Monkey | Yes | No | 2 | Mr. Biggles | dark brown |
| Iguana | No | No | 4 | Godzilla | lime green |
To build these data tables in Access, create a module and insert the following code. You can call the CreateDatabase() subroutine by building a form with a button to invoke this function.
Option Compare Database Option Explicit Public Sub CreateDatabase() ' --------------------------------------- On Error GoTo CreateDatabaseError Dim sqlCommand As String On Error GoTo DropDatabaseError sqlCommand = "DROP TABLE Pets" RunSQLCommand sqlCommand DropDatabaseError: sqlCommand = "CREATE TABLE Pets " _ & "(Type TEXT, HasFur YESNO, " _ & "Barks YESNO, NumLegs SINGLE, Name TEXT, Color TEXT);" RunSQLCommand sqlCommand sqlCommand = "INSERT INTO Pets VALUES ('Dog', Yes, Yes, 4, 'Spike', 'black/brown')" RunSQLCommand sqlCommand sqlCommand = "INSERT INTO Pets VALUES ('Dog', Yes, Yes, 4, 'Rex', 'black/white')" RunSQLCommand sqlCommand sqlCommand = "INSERT INTO Pets VALUES ('Snake', No, No, 0, 'Slither', 'green')" RunSQLCommand sqlCommand sqlCommand = "INSERT INTO Pets VALUES ('Pig', No, No, 4, 'Wilbur', 'pink')" RunSQLCommand sqlCommand sqlCommand = "INSERT INTO Pets VALUES ('Cat', Yes, No, 4, 'Fluffy', 'black/white')" RunSQLCommand sqlCommand sqlCommand = "INSERT INTO Pets VALUES ('Cat', Yes, No, 4, 'Hunter', 'tabby')" RunSQLCommand sqlCommand sqlCommand = "INSERT INTO Pets VALUES ('Monkey', Yes, No, 2, 'Mr. Biggles', 'dark brown')" RunSQLCommand sqlCommand sqlCommand = "INSERT INTO Pets VALUES ('Iguana', No, No, 4, 'Godzilla', 'lime green')" RunSQLCommand sqlCommand Exit Sub CreateDatabaseError: ' You only get here if an error occurred. ' Show the error. MsgBox Err.Description End Sub Private Sub RunSQLCommand(ByVal sqlCmd As String) On Error GoTo RunSQLCommandError Dim db As DAO.Database ' ------------------------------------- Set db = CurrentDb ' Run the command. db.Execute sqlCmd Exit Sub RunSQLCommandError: ' You only get here if an error occurred. ' Show the error. MsgBox Err.Description End Sub |
Since I copied my table data from Access to SQL Server, I created an ODBC connection, and upsized the Pets table to SQL Server. However, you can run the following T-SQL statements in a SQL query window to create the Pets table, and populate it with data.
CREATE TABLE [Pets]( [Type] [nvarchar](255)NULL, [HasFur] [bit] NULL DEFAULT((0)), [Barks] [bit] NULL DEFAULT((0)), [NumLegs] [real] NULL, [Name] [nvarchar](255)NULL, [Color] [nvarchar](255)NULL ); GO INSERT INTO Pets VALUES('Dog','True', 'True', 4, 'Spike','black/brown'); INSERT INTO Pets VALUES('Dog','True', 'True', 4, 'Rex','black/white'); INSERT INTO Pets VALUES('Snake','False', 'False', 0, 'Slither','green'); INSERT INTO Pets VALUES('Pig','False', 'False', 4, 'Wilbur','pink'); INSERT INTO Pets VALUES('Cat','True', 'False', 4, 'Fluffy','black/white'); INSERT INTO Pets VALUES('Cat','True', 'False', 4, 'Hunter','tabby'); INSERT INTO Pets VALUES('Monkey','True', 'False', 2, 'Mr. Biggles', 'dark brown'); INSERT INTO Pets VALUES('Iguana','False', 'False', 4, 'Godzilla','lime green'); GO |
Once the data is available in the Access and SQL Server platforms you can try out the iif() and Dlookup() functions in Access, and their functional equivalents in SQL Server.
The iif() Function
The iif() function allows you to insert inline conditional statements into your Access SQL queries. The format is iif(, , ). The function design is straightforward. If the evaluates to true, the is performed. Otherwise, the is invoked.
Let’s start with a simple example using iif(). For any cats in the Pets table, list their sound in this new column (named Sound) as “Meow”.
SELECT Name, IIF(Type = 'Cat', 'Meow', '') As Sound From Pets
Given the domain of the table Pets, two animals are cats, Fluffy and Hunter.
The result of the Access query is shown below.
In SQL Server, conditional logic can be implemented with a CASE statement. Below is the Access statement migrated to T-SQL. The CASE uses the Type of pet as the condition. A ‘Cat’ type returns the Sound ‘Meow’. Otherwise nothing is shown.
SELECT Name, Sound =CASE Type WHEN 'Cat' THEN'Meow' ELSE '' END FROM Pets |
The results are displayed below.
This diagram illustrates the conversion. Replace IIF with CASE, the becomes the WHEN clause, and the is the ELSE clause.
The problem with the iif() function and the SELECT … CASE statement is that the former is supported only in Access, and the latter is supported only in SQL Server. Is there a statement that would work for both platforms?
Using the set diagrams, notice that the result of this conditional query is the union between the set of pets that “Meow” and those that don’t.
The SQL ‘UNION ALL’ statement below will run correctly on both Access and SQL Server.
SELECT Name, 'Meow' As Sound FROM Pets WHERE Type = 'Cat' UNION ALL SELECT Name, ' ' As Sound FROM Pets WHERE Type <> 'Cat'; |
The two sets could also be joined using a ‘UNION’ statement, but, since the members are already distinct, the faster ‘UNION ALL’ is a better choice. A UNION, by definition, must eliminate all duplicate rows as opposed to UNION ALL.
The above was an easy example. Although the cats may meow, the other pets also make sounds. The dogs bark. The snake hisses. The pig oinks. Mr Biggles, the monkey, likes to scream, “Eek-eek.” The only quiet pet of the lot is Godzilla, the iguana.
An Access query could represent this cacophony with the following query.
SELECT Name, IIF(Barks, 'Ruff Ruff', IIF(Type = 'Cat', 'Meow', IIF(Type = 'Snake', 'Hiss', IIF(Type ='Monkey', 'Eek Eek', IIF(Type = 'Pig', 'Oink', ''))))) As Sound From Pets
The resultant table would be.
Converting this to a SQL Server CASE statement would result in this query below. For complex logic, the CASE statement is much easier to read.
SELECT Name, Sound =CASE Barks WHEN'True' THEN 'Ruff Ruff' ELSE CaseType WHEN 'Cat' THEN'Meow' WHEN 'Snake' THEN'Hiss' WHEN 'Pig' THEN'Oink' WHEN 'Monkey' THEN'Eek Eek' ELSE ' ' END END FROM Pets; |
Notice that the Barks attribute, which is a Yes/No type in Access, becomes a bit type upon migration to SQL Server.
Hence, in the SQL Server query you will see Barks being compared to ‘True’, whereas in the Access query the Yes/No data type allow you to use just Barks.
Using the diagram above as a guide, you can convert the logic of these queries to a UNION ALL. Below is the SQL Server query using a mass of UNION ALL’s.
SELECT Name, 'Ruff Ruff' As Sound FROM Pets WHERE Barks = 'True' UNION ALL SELECT Name, 'Meow' As Sound FROM Pets WHERE Type = 'Cat' UNION ALL SELECT Name, 'Hiss' As Sound FROM Pets WHERE Type = 'Snake' UNION ALL SELECT Name, 'Oink' As Sound FROM Pets WHERE Type = 'Pig' UNION ALL SELECT Name, 'Eek Eek' As Sound FROM Pets WHERE Type = 'Monkey' UNION ALL SELECT Name, ''As Sound FROM Pets WHERE Type NOT IN (SELECT Type FROM Pets WHERE Barks = 'True' OR Type = 'Cat'OR Type = 'Snake'OR Type = 'Pig'OR Type ='Monkey') |
The last SELECT covers the case of any quiet pets. If you added Sally, a snail and silent pet, the logic for the UNION query would still work. For the above query to run in Access 2007, you need to change Barks = ‘True’ to just Barks.
Execution times of CASE versus UNION and UNION ALL's on SQL Server
For data set of 8 or 9 pets, the difference in execution times between the CASE and the UNION or UNION ALL SQL queries is negligible. Thus, I created the following stored procedure, uspLoadPetsTable , which simply inserts the same 10 pet types with unique pet names, into the Pets table. The number of insertions of this group of ten pets is controlled by the input variable, NVal.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE uspLoadPetsTable -- Add the parameters for the stored procedure here @NVal int= 0 AS BEGIN DELETE FROM Pets; DECLARE @count int SET @count = 1 WHILE @count <= @NVal BEGIN INSERTINTO Pets VALUES ('Dog','True', 'True', 4, 'Spike' +CONVERT(nvarchar(5), @count) , 'black/brown'); INSERTINTO Pets VALUES ('Dog','True', 'True', 4, 'Rex' +CONVERT(nvarchar(5),@count),'black/white'); INSERTINTO Pets VALUES ('Snake','False', 'False', 0, 'Slither' +CONVERT(nvarchar(5),@count),'green'); INSERTINTO Pets VALUES ('Pig','False', 'False', 4, 'Wilbur' +CONVERT(nvarchar(5),@count),'pink'); INSERTINTO Pets VALUES ('Cat','True', 'False', 4, 'Fluffy' +CONVERT(nvarchar(5),@count),'black/white'); INSERTINTO Pets VALUES ('Cat','True', 'False', 4, 'Hunter' +CONVERT(nvarchar(5),@count),'tabby'); INSERTINTO Pets VALUES ('Monkey','True', 'False', 2, 'Mr. Biggles' + CONVERT(nvarchar(5),@count),'dark brown'); INSERT INTO Pets VALUES ('Iguana','False', 'False', 4, 'Godzilla' +CONVERT(nvarchar(5),@count),'lime green'); INSERT INTO Pets VALUES ('Snail','False', 'False', 0, 'Sally' +CONVERT(nvarchar(5),@count),'light brown'); INSERTINTO Pets VALUES ('Goldfish','False', 'False', 0, 'Goldfinger' +CONVERT(nvarchar(5),@count),'orange'); SET @count = @count + 1 END END GO |
To get a base line on the execution time for 10000 records in the Pets table I ran the following. First, I called the stored procedure uspLoadPetsTable with the parameter 1000. This created 10000 records in the Pets table.
EXECUTE uspLoadPetsTable 1000; GO CHECKPOINT DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE GO SET STATISTICS TIME ON GO SELECT Name, Sound =CASE Barks WHEN'True' THEN 'Ruff Ruff' ELSE CaseType WHEN 'Cat' THEN'Meow' WHEN 'Snake' THEN'Hiss' WHEN 'Pig' THEN'Oink' WHEN 'Monkey' THEN'Eek Eek' ELSE ' ' END END FROM Pets; GO SET STATISTICS TIME OFF CHECKPOINT DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE GO SET STATISTICS TIME ON GO SELECT Name, 'Ruff Ruff' As Sound FROM Pets WHERE Barks = 'True' UNION ALL SELECT Name, 'Meow' As Sound FROM Pets WHERE Type = 'Cat' UNION ALL SELECT Name, 'Hiss' As Sound FROM Pets WHERE Type = 'Snake' UNION ALL SELECT Name, 'Oink' As Sound FROM Pets WHERE Type = 'Pig' UNION ALL SELECT Name, 'Eek Eek' As Sound FROM Pets WHERE Type = 'Monkey' UNION ALL SELECT Name, ''As Sound FROM Pets WHERE Type NOT IN (SELECT Type FROM Pets WHERE Barks = 'True' OR Type = 'Cat'OR Type = 'Snake'OR Type = 'Pig'OR Type = 'Monkey'); GO SET STATISTICS TIME OFF CHECKPOINT |
After building the table, the subsequent three commands were preparation for gathering clean statistics about the query execution. The CHECKPOINT writes all dirty pages for the current database to disk. DBCC DROPCLEANBUFFERS removes all the clean buffers. Finally, DBCC FREEPROCCACHE cleans out the query plan cache. Bracketing each query - the CASE, UNION ALL and UNION ones - are calls turning STATISTICS TIME on and off.
For a Pets table with 10000 records, on my humble workstation, the execution of the CASE query was constently 30% faster than the UNION ALL query. In turn, the execution of UNION ALL was less than 10% faster than the UNION query. When I increased the number of pet records to 100000 (EXECUTE uspLoadPetsTable 10000), the CASE query execution was almost 50% faster than either the UNION ALL or UNION queries.
The time to parse and compile the CASE query was from 1 to 5 ms on my workstation, and it was over 10 times longer for the labyrinthine UNION or UNION ALL queries. You pay a performance penalty on both ends - compilation and execution - for the UNION's.
If you want performance, use CASE statements.
What happens if you add a primary key to the Pets table in SQL Server?
Based on how the Pets table is being used, you could consider the Name field to be a primary key, since the pet's name is never null and always unique.Thus, I ran the two following lines to make Name a primary key.
| ALTER TABLE Pets ALTER COLUMN Name NVARCHAR(255) NOT NULL; ALTER TABLE Pets ADD PRIMARY KEY (Name); |
Then I repeated the tests using the data generated by the uspLoadPetsTable stored procedure. I used both a table with 10000 and 100000 records. The parse and compilation times for the UNION queries remained much higher than the CASE query, but the execution times between the CASE and UNION's differed by 20% or less. The UNION ALL query was only 1-5% faster than the UNION query.
Adding a primary key did affect the execution speed of the CASE query, however, it reduced the execution time for the UNION's queries by 30% to 50%.
Once again, the CASE statement is the fastest. If you want to use bulky UNION queries, then add a primary key.
The DLookup Function
The syntax for DLookup() is defined by the Microsoft Access 2007 Developers’ Guide as DLookup(, , ). This domain aggregate function, DLookup(), returns the value of a field for a single record in a table or query.
For example, DLookup is used below to find the name of the pig in the Pets table.
SELECT DLookup("Name", "Pets", "Type='Pig'") As [Piggy Name]
The domain is the Pets table, and the criterion is that the Type of pet is a pig.
The result Wilbur, as shown is the following.
The domain for DLookup does not have to be a table. It can be the result of another query. Using Access 2007, create a query that contains the following SQL statement:
SELECT Name, Type FROM Pets Where NumLegs >=4
Name this query LegsQuery. This will be the domain for a DLookup function query. The result of this LegsQuery is the Name and Type of all pets that have 4 or more legs.
Create a DLookup() query with the following SQL.
SELECT DLookup("Name","LegsQuery","Type='Pig'") AS [Piggy Name];
Notice that the second parameter, the domain, “LegsQuery”, is not a table but a reference to another query. The quotation marks around the query name are required.
To demonstrate what occurs when the DLookup() function finds two or more matching criteria, invoke it using the Pets table with the condition Color=’black/white’.
From the list of pets, you know that there are two black and white animals: Rex and Fluffy.
If you use invoke the following Access SQL query using DLookup(),
SELECT DLookup("Name", "Pets", "Color='black/white'") As [Black White]
The resulting table has a single column, Black White, with the name of just one of the pets, Rex. Thus, the DLookup() function returned a subset of black and white pets.
What if you decided to convert the DLookup() function into a SELECT statement with a WHERE clause?
SELECT Name As [Black White] FROM Pets WHERE Color="black/white"
You will have two rows in your result. This matches the expected result. It shows both black and white animals, Rex and Fluffy.
In order to get the same result using SQL as the DLookup() invocation, you must include the FIRST() function. This would just return one row, Rex.
SELECT FIRST(Name) As [Black White] FROM Pets WHERE Color="black/white"
The conversion from a query with a DLookup() call to SQL is shown in the diagram below.
In general, DLookup(, , ) can be changed to SELECT FIRST FROM WHERE .
Conclusion
Using the simple Pets database, you learned how to invoke the iif() and DLookup() functions inside of Access 2007 queries. You also were shown how to convert these functions to T-SQL that could be invoked on SQL Server 2005. For the case of iif(), you were shown two methods of conversion: one used the CASE construct and the other two used the UNION's construct. Finally, the execution speed of the CASE query, the UNION and the UNION ALL query were discussed, with the CASE clearly being the faster.
