Equivalent of DUAL table in Microsoft SQL Server

I spent quite a few few years developing PL/SQL on Oracle databases and had grown accustomed to its syntax.  One of the things which I didn’t enjoy was that the ‘FROM’ clause in a SELECT statement was required (it is not in TSQL).  This causes a  bit of a problem when all you want to do is do retrieve 1 row with a result.  The workaround for this problem was to introduce a dummy table which just contains one row which you could use in your queries.  This table was called DUAL and has a single VARCHAR2(1) column called DUMMY that has a value of ‘X’.

To demonstrate the use of DUAL, the following displays the use of a SELECT statment that calculates 1+1 in TSQL and PL/SQL:

TSQL:

PL/SQL:

The name DUAL seems a bit odd for a table which just has one record in it.  The reason for this is that it originally had two records and was intended to be used to return two records when being used.   There is a bit of history which explains this on wikipedia article.

What if you wanted to port existing code from PL/SQL to TSQL and your code already utilizes DUAL?  I would personally rewrite it as you generally have to write rewrite code anyways but the other option is to actually recreate the DUAL table in your database.   The following code will assist creating this:

If your a TSQL programmer working on a PLSQL you will need to remember to utilize the DUAL table,