Will max(columnid) and "top 1 ... order by desc" use the same execution plan?

 >>> testcase.sql

DROP TABLE IF EXISTS Users

GO

CREATE TABLE Users (

        id int IDENTITY(1,1) PRIMARY KEY,

        Reputation NUMERIC(8,2)

)

GO

WITH RandomValues AS (

        select 1 id, (cast(rand(checksum(newid()))*100000 as numeric(8,2))) AS RandomNumber

        union all

        select id+1, (cast(rand(checksum(newid()))*100000 as numeric(8,2))) AS RandomNumber from RandomValues where id<1000000

)

insert into Users (Reputation)

select RandomNumber from RandomValues OPTION(MAXRECURSION 0)

GO

>> show_plan.sql

Update statistics Users

go

set showplan_text on

go

select top 1 * from Users order by Reputation desc

go

select max(Reputation) from Users

go

set showplan_text off

go

set statistics time on

set statistics io on

go

select top 1 * from Users order by Reputation desc

go

select max(Reputation) from Users

go

set statistics time off

set statistics io off

go

Execute: 

[root@ol8 ~]# sqlcmd -S localhost -U SA -P 'xxxxx' -d TestDB -e -i testcase.sql

[root@ol8 ~]# sqlcmd -S localhost -U SA -P 'xxxxx' -d TestDB -e -i show_plan.sql

Output:

[root@ol8 ~]# sqlcmd -S localhost -U SA -P 'xxxxxx' -d TestDB -e -i show_plan.sql

Update statistics Users

set showplan_text on

select top 1 * from Users order by Reputation desc

StmtText

----------------------------------------------------

select top 1 * from Users order by Reputation desc

(1 rows affected)

StmtText

--------------------------------------------------------------------------------------------------------

  |--Top(TOP EXPRESSION:((1)))

       |--Parallelism(Gather Streams, ORDER BY:([TestDB].[dbo].[Users].[Reputation] DESC))

            |--Sort(TOP 1, ORDER BY:([TestDB].[dbo].[Users].[Reputation] DESC))

                 |--Clustered Index Scan(OBJECT:([TestDB].[dbo].[Users].[PK__Users__3213E83F7CAE2D67]))

(4 rows affected)

select max(Reputation) from Users

StmtText

-----------------------------------

select max(Reputation) from Users

(1 rows affected)

StmtText

-------------------------------------------------------------------------------------------------

  |--Hash Match(Aggregate, HASH:() DEFINE:([Expr1002]=MAX([TestDB].[dbo].[Users].[Reputation])))

       |--Clustered Index Scan(OBJECT:([TestDB].[dbo].[Users].[PK__Users__3213E83F7CAE2D67]))

(2 rows affected)

set showplan_text off

set statistics time on

set statistics io on

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

   

   

   

   

select top 1 * from Users order by Reputation desc

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

id          Reputation

----------- ----------

     385843   99999.87

(1 rows affected)

Table 'Users'. Scan count 3, logical reads 2263, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

 SQL Server Execution Times:

   CPU time = 191 ms,  elapsed time = 122 ms.

   

   

   

   

select max(Reputation) from Users

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

----------

  99999.87

(1 rows affected)

Table 'Users'. Scan count 1, logical reads 2237, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

 SQL Server Execution Times:

   CPU time = 54 ms,  elapsed time = 55 ms.

   

   

   

   

   

set statistics time off

set statistics io off

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

Refer to SQL Server setup URL if u need SQL Server setup on Linux: http://www.dbaglobe.com/2020/08/install-sql-server-2019-on-ol8.html