SQL SERVER – SELECT * FROM dual – Dual Equivalent

This blog post is for all the Oracle developers who keep on asking for the lack of “dual” table in SQL Server. Here is a quick note about DUAL table, in an easy question-and-answer format.

What is DUAL in Oracle?

Dual is a table that is created by Oracle together with data dictionary. It consists of exactly one column named “dummy”, and one record. The value of that record is X.

You can check the content of the DUAL table using the following syntax.

SELECT * FROM dual

It will return only one record with the value ‘X’.

What is the reason for following error in SQL Server?

Msg 208, Level 16, State 1, Line 1
Invalid object name ‘dual’.

The reason behind the error shown above is your attempt to SELECT values from DUAL table in SQL Server. This table does not exist in SQL Server. Continue reading for workaround.

What is the Equivalent of DUAL in SQL Server to get current datetime?

Oracle:

select sysdate from dual

SQL Server:

SELECT GETDATE()

What is the equivalent of DUAL in SQL Server?

None. There is no need of Dual table in SQL Server at all.

Oracle:

select ‘something’ from dual

SQL Server:

SELECT ‘something’

I have to have DUAL table in SQL Server, what is the workaround?

If you have transferred your code from Oracle and you do not want to remove DUAL yet, you can create a DUAL table yourself in the SQL Server and use it.

Here is a quick script to do the said procedure.

CREATE TABLE DUAL(DUMMY VARCHAR(1)
)GOINSERT INTO DUAL (DUMMY)VALUES ('X')GO

After creating the DUAL table above just like in Oracle, you can now use DUAL table in SQL Server.

What is better SQL Server or Oracle?

SQL Server.