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 (http://blog.SQLAuthority.com)
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
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?
SQL Server because it is not Oracle.
Pinal,
I’d expect a more intelligent response from someone such as yourself. Platform xenophobia is not helpful to anyone. Oracle and SQL Server are both large products used to do many important tasks. Its ok to think that one is better overall, but you should be able to provide a better reason than “because I said so.”
Can you justify me your point,Pinal?
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)
[...] also got some interesting emails from Oracle DBAs on my article of DUAL table in SQL Server SQL SERVER – SELECT * FROM dual – Dual Equivalent. There was interesting communication about date time as well. Here are the examples of the [...]
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;
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?
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
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”
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).
What does * mean in “select * fom dual”?
@shekhar..select * from dual; here * means to select all columns present in dual.however since dual has only one column by the name Dummy ,it shows up only that column and the value contained in it ie X
@Egemen Soylu:
Quote: “…you can’t run this sql anywhere but oracle …”
And it’s the only place that you WOULD run it (and you’d also be an unqualified, untrained, clueless web developer with an art degree (in the biggest, most disorganised mish-mash of UNIX-centrix crep out there)).
UNIX and Oracle are like Apple – a dying breed, in the minority, massively over-priced, massively unsupported (attitude of: better to restrict functionality – than support it), full of fan-boys, full of ‘information squirrels’ (it took me 20 years to learn – why should I help you, noob?).
It say’s it all that Oracle deals with banks – banks being responsible for bringing countries to their knees through corruption.
Good one Microsoft – keep your 99% coverage – let Oracle deal with the scum. Keep your business-model, with all users of all levels happy to help all others (that’s why UNIX and Oracle and Apple are failing).
Show me ‘dual’ in the SQL standards.
If it ain’t there, then don’t use it in customer solutions – period (that’s called ‘best-practise’), and if you can’t use it as best-practise, then it isn’t very useful – is it?
You spouting about ‘dual’ suggests that your customers have ‘dual’ scattered about their solutions – I as your customer would be asking why have you given me a lead-weight as a solution?
I’m having to learn PL / SQL atm and the help out there is so disjointed and ‘non-standard’ – you may as well make up your own standards – as you go.
Oracle really is atrocious.
Faster with exponential maintenance cost is no competition against slower with predictable maintenance cost (it’s only banks that don’t need to care about cost – they simply invent a new thieving scam to cover it).
SE
Workaround:
SELECT dual.dummy
FROM (SELECT ‘X’ dummy ) dual
I’m reading this and smiling in amusement at how tempers run high discussing something as trivial as whether SQL server has a dual table equivalent… and the rant about SQL server or Oracle which is ‘better’… May I make a humble observation: It doesn’t matter. Oracle / SQL Server / MySQL / DB2…. they are but repositories for storing business and operational data. The value from storing that data is only achieved when consuming it in one form or another (reports, analytics) and most importantly drawing conclusions from the derived intelligence. If this is not possible – one may question ‘why bother storing the information in the first place’. For those of you who write highly complicated 3 page long SQL or PL/SQL code for a living- trust me. There is a better (and more PROFITABLE) world out there where you can leverage your data skills by assisting business people in their endeavours rather than spending your time worrying about which back-end system is ‘best’ or which syntax is superior to which. Have a glance at the booming Business Intelligence industry – plenty of opportunities to take your honed technical skills and apply them in something that you may one day be able to discuss with your partner without them staring blankly at you. That’s my 5 minute break gone – back to developing customer facing analytics… Best of luck in your careers!:-)
Gud post
Stupid post, select without a from lacks standards as Mickeysoft lacks standards all the time,
He given a solution for those who had the doubt. For me its helpful because I know oracle and which is seemed to be difficult …. I suggest better you can give ur suggestion to Mricrosoft to enhance it….
Fantastic solution. Really it resolved all confusion. Thanks again.
Very useful article. Its really help me lot. Thanks.
Sql server is better than Oracle????
Yeah if you want a database that struggles with high user concurrent connectivity, handles locking of data in the most inefficient way possible, has poor tuning capability, isn’t very scalable (compared to Oracle), handles locks/latches etc horribly, locks data when there is no need, can’t perform anywhere near the capability of oracle, the list is almost endless….
I’ve worked for numerous large banks and NONE use sqlserver as their main database, there are many many reasons for this.
To its credit Sqlserver is very easy to use, that’s its attraction, but to say it is better than Oracle is at best uninformed and worst a total joke where you simply don’t know the facts and are exposing your ignorance.
Don’t write Oracle off simply because as I suspect you have never used it properly and don’t understand it.
you don’t actually need
select sysdate from dual in oracle
you can simply assign it
eg. today’s adte := sysdate;
why can’t you do similar in sqlserver?
why not just mention getdate() as opposed to select getdate().