SQL: Converting to SQL Server - Where is the DUAL table?

I’m often working with developers who are migrating to SQL Server from Oracle. (Well, who can blame them?) One of the first questions that I’m often asked though, is where is the dual table?

In Oracle, a SELECT statement must have a FROM clause.

In SQL Server, this code will work fine:

SELECT UPPER('Hello');

It will just return the string converted to upper case. You can alias it as well:

SELECT UPPER('Hello') AS Greeting;

Again, you’ll get the converted string but the column will be called Greeting.

In Oracle though, that doesn’t work. Instead, you’d need to write code like this:

SELECT UPPER('Hello') AS Greeting FROM dual;

When you’re converting code, ideally, you’d just remove all the “FROM dual” parts from the source code, but if that could cause any issues, or if the code is embedded in apps, etc. it might be easier to just create the table that the code is looking for.

You can do that like this:

DROP TABLE IF EXISTS dbo.dual;
GO

CREATE TABLE dbo.dual
(
    DUMMY varchar(1) NOT NULL
);
GO

INSERT dbo.dual (DUMMY) 
VALUES ('X');
GO

The dual table has a single row with a single column named DUMMY. It’s data type in Oracle is varchar2(1) but varchar(1) in SQL Server will work just fine for this.

Then it works as expected, as you can see in the main image above.

2020-05-29