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
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().
So helpful. Thanks!
Since we have so many experts here the answer to this question should come quick.
I need a simple way to generate a list dates. As an example: 12 dates, starting today, incremented by 3 days.
In Oracle I can do this:
SELECT
TO_DATE(‘2013-10-02’, ‘YYYY-MM-DD’) + (3 * (ROWNUM-1)) AS DAYS
FROM
DUAL
CONNECT BY ROWNUM <= 12;
It gives me that:
DAYS
———
02-OCT-13
05-OCT-13
08-OCT-13
11-OCT-13
14-OCT-13
17-OCT-13
20-OCT-13
23-OCT-13
26-OCT-13
29-OCT-13
01-NOV-13
04-NOV-13
12 rows selected.
Or in other words, exactly what I need.
Note that with very simple tweaks I can:
– Get as many dates as I want
– With the increment I want
– Starting from any date I want
– Going forward or backward in time
Of course, I can use this to:
– Join with a table, a view, use any date operator and pull the data I need for these dates.
– Fill tables
– Update / Delete table entries…
Please note also that:
– I can do this in any DB as long as I have the most basic SELECT privileges.
– I don't have to create temporary stuff (and leave crap behind me)
– I don't have to create permanent stuff (and leave crap behind me)
– Everything is in one place (one SQL statement)
– I've used it on every Oracle DB I came across since 1999 without issue (and it's probably gonna work until I retire)
– So far I never had to complain about performance
Now, how do I do that:
– In Access 2010 today ?
– In an SQL Server next week ?
Thank you very much
(Hopefully the answer is shorter than the question)
You need to use a temp table for this. most likely need a stored procedure.
Thanks for the comment.
Ok, this place is useless, nobody brings any solution.
No doubt about it. Oracle is better a better functioning database with a more evolved set of useful functionality. Yes – there is all that extra time to type “from dual” and all…
Hi guys, I personally believe that when Pinal said the SQL server is better than Oracle, he actually meant it’s better on this particular aspect when you want to select a scalar value you don’t have to bother to specify a table. I guess Pinal is educated enough not to compare SQL server to Oracle, as a whole, with one word.
Hi guys,
which one is better depends on users needs ;-)
Just find someting strange, which is surely documented at some BOL: Try to use the getdate() function as parameter for a stored procedure at MSSQL. I found no way to do that directly, like “exec dbo.someSP getdate()” or “exec dbo.someSP (select getdate() from someTable)”. The only chance is to use a variable. It looks like sp’s never resolve theyre parameters as sql statements…
it’s easy to understand thanks
Hello Guys,
What an interesting argument there! I came across this article when I was trying to find out how to execute no-op SQL statements in SQL Server 2008. My issue is I want to execute no-op SQL statements before and after an MS NAV application user takes charge of a connection in a connection pool.
I was wondering how I can configure MS NAV & SQL to do that for me. Anyone with an idea.
I have no idea about NAV.
If you just want to select a single scalar value just drop the FROM DUAL off the end for sql server. instead of SELECT SYSTIME FROM DUAL, just do SELECT getdate(). or SELECT 1 FROM DUAL, instead SELECT 1
Sure. Makes sense.