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?
select sysdate from dual
What is the equivalent of DUAL in SQL Server?
None. There is no need of Dual table in SQL Server at all.
select ‘something’ from dual
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
INSERT INTO DUAL (DUMMY)
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?
Reference: Pinal Dave (http://blog.SQLAuthority.com)