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.


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?


select sysdate from dual

SQL Server:


What is the equivalent of DUAL in SQL Server?

None. There is no need of Dual table in SQL Server at all.


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.


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)

62 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

    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.


    • @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.
      Haha..(actually i also want to end it like this..) ;)


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


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


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


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

  5. 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’
    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:

        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.


  6. When you say “After creating the DUAL table above just like in Oracle, you can now use DUAL table in SQL Server”

    Give a simple example please.
    select sysdate from dual – errors out


  7. 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
    ‘SELECT * FROM packagename.functionname(”value”);’

    Could you please help me out!


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


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


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


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


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


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



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


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


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


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


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

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

    TO_DATE(‘2013-10-02’, ‘YYYY-MM-DD’) + (3 * (ROWNUM-1)) AS DAYS

    It gives me that:


    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…


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


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