Sql server's Constant scan - clarification?

sql-server

Solution

Because most of the operators that are used in fulfilling a query are specified in terms of one or more input rowsets and one or more output rowsets, it makes sense that if you write a query that requires the use of one of those operators, you'd better make sure that the input that you present to that operator is a rowset.

So the constant scan operator is a special operator that can take one or more scalar inputs and generate a rowset.

It makes composing operators far easier.

Why does `select top 1 getdate()` yield different result than plain `select getdate()`

Because you've asked to use the `TOP` operator. And the `TOP` operator expects to have an input rowset.

To why I think focussing on the performance is irrelevant:

SET STATISTICS TIME ON
GO
SELECT TOP 1  GETDATE()
GO
SELECT GETDATE()

Messages:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

That is, we can't even get a measure of the amount of time that either statement takes to execute.

Problem

I've read this article about Constant scan but still didn't understand when it would be used ( people commented the author that his article still isn't understood): MSDN : The Constant Scan operator introduces one or more constant rows into a query. A Compute Scalar operator is often used to add columns to a row produced by a Constant Scan operator. very unclear. For example : why does `SELECT TOP 1 GETDATE()` produces : While `select getdate()` produces nothing. ( in execution plan). I guess it has to do with constant scan. so : Question : - What exactly is constant scan - Why do it needs to SCAN something if it's a constant , and - Why does `select top 1 getdate()` yield different result than plain `select getdate()`

Original source