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)
)
GO
INSERT 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.
Reference: Pinal Dave (https://blog.sqlauthority.com)
70 Comments. Leave new
Good post – I remember looking for this way back when – I much prefer SELECT without DUAL :)
Nice article sir……i really feel without dual, working is more efficient in a database…..
Its very good and useful for SQL Learners and Trainers. Whenever i explained some queries in SQL Server, they ask how to use DUAL table in SQL. But, i think its impossible. NOw, u do its possible. Thank You for Nice information.
Of course nobody here recognize to Oracle superior to MS-SQL. But, if i be honest, Oracle is much better.
Totally I agree. I get frustrated, when even select statement acquires locks…
A’m not agree with.
The locks depend of you’are ISOLATION LEVEL parameter.
Actually, ORACLE requires FROM clause to execute a query. SQL Server does not require that.
DUAL is a dummy table, which will be used for querying pseudocolumns like “SYSUSER”, “SYSDATE”
etc. It will also be useful in JOIN clauses to use these pseudocolumns in the SELECT
queries.
FROM clause is required in a query, as per ANSI standard. So, I feel that eventhough DUAL
is a dummy table. It satisfies ANSI standard. It is also supported by MySQL also. ORACLE is following ANSI standard in this case. Nonetheless, SQL Server, PostGRE SQL dont require Dummy table.
The way to have this kind of thing in SQL Server is to create a dummy table,
as told by Pinal in the article above.
Can you tell me what is the equivalent of ‘level connect by ‘of oracle in sql server2008?
It is done using a recursive CTE
Id like an answer to this too
Good points by pinal,venkat. Really never felt like i have missed the DUAL table, the way sql server handles getting values for GETDATE() works for me.
I don’t see any reason to use/create Dual table in sql server.
Haha… is this really an expert level blog? I mean… come on dude…everyone knows about DUAL!!!
@Anonymous.
This blog contains article for beginner and Experts. This article was meant for beginners.
Feel free to browse through article list on this blog that are meant for Experts like you.
And, NOT every one knows about DUAL.
~ Peace.
@ Anonymous
Haha..(ahh like to start as u did..)
Well i didn’t know about dual….before reading DUAL on this blog…
and as Imran said go and browse some of the articles in this blog which may be much more beyond your knowledge..
and the best thing about this blog is “SQLAuthority” is intended to help all the SQL Community and this community includes me and you. So never comment on the knowledge of the person who has courage to write the basic thing in his much superior blog.
I am very thankful that we have that kind of community hero with us to help us out in any situation.
Thanks…
Haha..(actually i also want to end it like this..) ;)
I have just switched to Oracle after 3-4 years of MSSQL and I didn’t know what the DUAL table was. Thank you Dave.
You don’t need DUAL in SQL Server because you can do a select without mentioning a table e.g.:
SELECT ‘Hello’ Greeting;
That will work in SQL server and will return 1 row with the value of ‘Hello’ and a column name of ‘Greeting’
In Oracle you would have to write:
SELECT ‘Hello’ Greeting FROM DUAL;
So I can’t see a need for DUAL in SQL Server at all.
The point of having DUAL in SQL server is if you had moved your code from Oracle to SQL server, and still want your queries to work without changing the code.
C’mon guys, obviously MySQL is better than both SQL and Oracle.
I seriously hope you’re being sarcastic. The only thing that MySQL is better than MSSQS and Oracle is in price.
coulu you plese tell me?What is better SQL Server or Oracle? and why?
MSSQS is better than Oracle because the Microsoft stack is much more developer orientated. It also has an extremely lower TCO than oracle combine this with that MSSQS is built for regular hardware whereas if you use Oracle you should be running solaris which is an entire huge cost and risk endeavor on it’s own.
Oracle is better than MSSQS when you need a live transactional database that has TRILLIONS of rows and would need to be deployed across the Oracle grid computing. (aka 0.000000000000001% of applications)
What about using left join in a manner that could be used for a pivot table?
SELECT 1 as i, f.bar, f.jar FROM dual LEFT JOIN foo AS f on f.bar = 1 WHERE dual.dummy = ‘X’
UNION
SELECT 2 as i, f.bar, f.jar FROM dual LEFT JOIN foo AS f on f.bar = 2 WHERE dual.dummy = ‘X’
This way you always get a result set even if table foo has been truncated.
I’ve got a similar example, is there a SQL Server way to do that is equivalently easy?
declare @opcode varchar(6)
SET @opcode = ‘fred’
Insert into actions_log(OperatorsID, ActionText)
select O.ID, ‘Actions that might or might not have an operator associated’
from dual
left join operators O on operatorcode = @opcode
Pinal Dave is our hero!
There’s no need to join for that, even in oracle this should work:
insert into actions_log(operatorsid, actiontext) select o.id, ‘actions etc’ from operators o where o.operatorcode = @opcode;
Yes, there is a way to do this in SQL but it’s fairly contrived using CTEs to generate a 1-row result set and then using it as the root table in a LEFT JOIN query.
Basically, re-create “DUAL” as a CTE:
With
AlwaysReturn1 AS (
SELECT 1 as “One”
),
SELECT “One”, Operators.* FROM AlwaysReturnOne LEFT JOIN Operators ON 1 = 1
The point of “AlwaysReturn1” is to literally always return a 1-row result set. Just like DUAL.
Now if we use that as the root table of our query, we can left join it to a result set that may be empty and we will still always get back at least 1 row.
NOTE: LEFT JOIN x ON 1 = 1 creates a condition where the single row from AlwaysReturn1 matches to any and every row from the other result set.
Alternatively, you could create a view called DUAL (or whatever else you wanted) that simply returned “SELECT 1” as well.
When you say “After creating the DUAL table above just like in Oracle, you can now use DUAL table in SQL Server”
HOW?
Give a simple example please.
select sysdate from dual – errors out
Did you try the code that Pinal posted?
sysdate is a reserved keyword in Oracle, not the name of any column in DUAL.
For SQL, you would want to use GETDATE() or similar:
SELECT GETDATE() FROM DUAL
Hi Pinal , I have to call an Oracle function from SQL Server.
This Oracle function accepts a varchar input parameter and returns an ID (number output parameter)
I have tries several ways but nothing works–>
declare @InputPara varchar(100)
set @InputPara=’protest’
declare @OutputPara bigint
EXECUTE ( ‘BEGIN ? := packagename.functionname(?,?); END;’, @InputPara, @OutputPara OUTPUT )at linkedservername;
select * from linkedservername..packagename.functionname(‘tom’)
select * from openquery
(
LinkedServerName,
‘SELECT * FROM packagename.functionname(”value”);’
)
Could you please help me out!
Thanks
Janki
Hi,
Try including schema name before package/procedure name and ensure the user id used to connect linked server having sufficient privileges to execute the procedure.
Thanks for the Post. : ) It was of help.
Hi Pinal,
in one of the app am supporting i found that a select statement is being used to retrieve value from dual table:
“select seq_XXXXX.nextval from dual” and as i saw the dual table has only 1 column i think its not able to proceed with the next value and here its failing. correct me if i am wrong… and suggest me what shall i do?
Thanks in Adv.
Anup Kumar
I dont agree .. dual is lot more what is given here.
do we have equivalent for
SELECT 1
FROM DUAL
CONNECT BY LEVEL <=
in sql server.
People saying “I’m using oracle but I don’t know dual”,
“Sql is better then Oracle”
create a table contain ‘X’ name dual then use dual…
Are you insane?
select ‘anything you want’ from dual;
return -> anything you want.
select function(param) from dual;
return -> what function returns.
select builtinFunctions from dual;
return -> what function returns.
you CANNOT achieve a functionality of the magical dual table by creating single column single record table.
you CANNOT compare sql s*it with Oracle (by the way its name is Oracle =)
you can create a “PRIMARY KEY” constraint on column which has duplicated records with sql, where is integrity?
you can’t run this sql anywhere but oracle
select f_giantCalculator(f_anotherFunctionThatOnlyReturnsAnId(complicatedTable.id), anotherFunctionProvideSomethingToGiantCalculator(complicatedTable.sillyColumn)) from complicatedTable
where complicatedTable.color = f_colorChooser(‘mycolor’);
aaah, I’m using mysql for web projects, it does’nt care if i using php or .net Single thing to do with sql server is uninstall it and don’t criticize about Oracle without knowledge you expert.
Do you know what Microsoft says to Communication or Bank IT’s?
“We cannot provide service for your demands, go to Oracle”
Dude you’re clueluess. DUAL isn’t a magic table. It really is literally a 1-row, 1-column result set.
SELECT ‘anything you want’ FROM AnyTableYouWant
Is completely legit, non-magical functionality. You get back ‘anything you want’ for each row in the table. If the table is empty, you get back nothing.
In Oracle, DUAL is guaranteed to always have 1 row (and exactly 1 row) therefore you get back ‘anything you want’ exactly 1 time.
Again, not magical, it’s actually just a normal, dumb default of a table.
For hilarity’s sake, try modifying its contents on a non-production system.
please explain the function of dual
Anup Kumar,
Since it doesn’t look like anyone has responded to you, “select seq_XXXXX.nextval from dual” (presumably from Oracle) returns the NEXT SEQUENCE NUMBER (aka “next value”) from the SEQUENCE “seq_XXXXX”.
This handy feature of Oracle allows for UNIQUE ids to be returned to MUTLIPLE queries (executing “simultaneously” [or as close to simultaneously as the scheduler will allow]).
THIS is another “feature” that cannot be mapped to SQL Server (espeically NOT with creating a DUAL table with a single column).