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)
)
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)

About these ads

56 thoughts on “SQL SERVER – SELECT * FROM dual – Dual Equivalent

  1. 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.

  2. 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.

  3. 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.

    • @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..) ;)

  4. 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.

      • 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.”

    • 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)

  5. Pingback: SQLAuthority News – A Monthly Round Up of SQLAuthority Blog Posts Journey to SQL Authority with Pinal Dave

  6. 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;

  7. 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

  8. 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

  9. 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

  10. 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”

  11. 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).

  12. @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

  13. @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

  14. 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!:-)

  15. 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….

  16. 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.

  17. 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().

  18. Pingback: SQL SERVER – Weekly Series – Memory Lane – #038 | Journey to SQL Authority with Pinal Dave

  19. 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)

    • 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…

  20. 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.

  21. 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…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s