What is the SQL Server equivalent of dual table?

 In SQL Server, there is no direct equivalent of the DUAL table that exists in Oracle. The DUAL table in Oracle is a special one-row, one-column table that is commonly used when you want to select a constant value or perform a function without referencing any actual table.

However, in SQL Server, you can achieve the same result using a SELECT statement without referencing any table at all. Here's how you can do it:

Example 1: Selecting a constant value

In Oracle, you might write:

SELECT 'Hello, World!' FROM DUAL;

In SQL Server, you can do the same with:

SELECT 'Hello, World!';

Example 2: Using functions or expressions

In Oracle, you might write:

SELECT SYSDATE FROM DUAL;

In SQL Server, you can do the same with:

SELECT GETDATE();

Example 3: Selecting a calculated value

In Oracle, you might write:

SELECT 2 + 2 FROM DUAL;

In SQL Server, you can do:

SELECT 2 + 2;

Example 4: Using VALUES (to simulate DUAL for more complex scenarios)

If you need a single row result but want to select more than one value or perform more complex expressions, you can use VALUES in SQL Server:

SELECT * FROM (VALUES (1), (2), (3)) AS t (Number);

This syntax is similar to using DUAL in Oracle for generating row-based expressions. It allows you to return a set of values without having to rely on an actual table.

Conclusion:

In SQL Server, you don’t need a special table like DUAL because the system allows you to execute SELECT statements directly with expressions, constants, or functions. The DUAL table in Oracle was a convenience for this, but SQL Server provides more flexible ways to perform similar queries.