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 (https://blog.sqlauthority.com)

Oracle
Previous Post
SQL SERVER – Identifying Statistics Used by Query
Next Post
SQL SERVER – Win USD 11,899 worth MSDN Subscription 5 Days to go

Related Posts

69 Comments. Leave new

  • Kevin Mckenna
    July 20, 2010 7:25 am

    Good post – I remember looking for this way back when – I much prefer SELECT without DUAL :)

    Reply
  • Sumit Thapar
    July 20, 2010 9:29 am

    Nice article sir……i really feel without dual, working is more efficient in a database…..

    Reply
  • Kalyana sundaram
    July 20, 2010 10:18 am

    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.

    Reply
  • Of course nobody here recognize to Oracle superior to MS-SQL. But, if i be honest, Oracle is much better.

    Reply
  • venkataraman
    July 20, 2010 6:48 pm

    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.

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

    Reply
  • I don’t see any reason to use/create Dual table in sql server.

    Reply
  • Haha… is this really an expert level blog? I mean… come on dude…everyone knows about DUAL!!!

    Reply
    • Imran Mohammed
      July 22, 2010 5:18 am

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

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

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

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

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

      Reply
  • C’mon guys, obviously MySQL is better than both SQL and Oracle.

    Reply
    • Chris Marisic
      August 3, 2010 8:12 pm

      I seriously hope you’re being sarcastic. The only thing that MySQL is better than MSSQS and Oracle is in price.

      Reply
  • coulu you plese tell me?What is better SQL Server or Oracle? and why?

    Reply
    • SQL Server because it is not Oracle.

      Reply
      • Justin Dearing
        August 2, 2010 11:03 pm

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

      • Brandon Kirsch
        June 6, 2015 2:12 am

        If you’re not happy with his (correct) response, feel free to open your own blog.

        For those of us who have spent a serious amount of time working on both systems, it’s painfully clear that SQL Server is far superior to Oracle in almost every imaginable way. DUAL is one of those reasons. VERY incorrect treatment of empty strings ” are another. The fact they created CHAR2 and VARCHAR2 just to guarantee continuity of this bad behavior is kind of funny to me. The wildly incorrect comparison logic for CHAR to VARCHAR has lead to 100’s of bugs across a variety of enterprise applications. This list of reasons why Oracle sucks goes on and on.

        Sarath doesn’t seem to be asking for, or frankly very interested at all, in the actual reasons.

      • Can you justify me your point,Pinal?

    • Chris Marisic
      August 3, 2010 8:15 pm

      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)

      Reply
  • Jed Reynolds
    March 18, 2011 5:28 am

    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.

    Reply
    • 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!

      Reply
      • 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;

      • Brandon Kirsch
        June 6, 2015 2:23 am

        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

    Reply
    • Did you try the code that Pinal posted?

      Reply
    • Brandon Kirsch
      June 6, 2015 2:24 am

      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

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

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

      Reply
  • Thanks for the Post. : ) It was of help.

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

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

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

    Reply
    • Brandon Kirsch
      June 6, 2015 2:27 am

      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.

      Reply
  • Ravindra kumar
    February 6, 2012 4:29 pm

    please explain the function of dual

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

    Reply

Leave a Reply

Menu
Exit mobile version