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

Install SQL Server 2019 on OL8

 Reference: https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-red-hat?view=sql-server-ver15

Commands:

sudo alternatives --config python

# If not configured, install python2 and openssl10 using the following commands: 

sudo yum install python2

sudo yum install compat-openssl10

# Configure python2 as the default interpreter using this command: 

sudo alternatives --config python

sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/8/mssql-server-2019.repo

sudo yum install -y mssql-server

sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/8/prod.repo

sudo yum install -y mssql-tools unixODBC-devel

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc

source ~/.bashrc

sudo /opt/mssql/bin/mssql-conf setup

Output for reference:

[root@ol8 ~]# sudo /opt/mssql/bin/mssql-conf setup

usermod: no changes

Choose an edition of SQL Server:

  1) Evaluation (free, no production use rights, 180-day limit)

  2) Developer (free, no production use rights)

  3) Express (free)

  4) Web (PAID)

  5) Standard (PAID)

  6) Enterprise (PAID) - CPU Core utilization restricted to 20 physical/40 hyperthreaded

  7) Enterprise Core (PAID) - CPU Core utilization up to Operating System Maximum

  8) I bought a license through a retail sales channel and have a product key to enter.

Details about editions can be found at

https://go.microsoft.com/fwlink/?LinkId=2109348&clcid=0x409

Use of PAID editions of this software requires separate licensing through a

Microsoft Volume Licensing program.

By choosing a PAID edition, you are verifying that you have the appropriate

number of licenses in place to install and run this software.

Enter your edition(1-8): 2

The license terms for this product can be found in

/usr/share/doc/mssql-server or downloaded from:

https://go.microsoft.com/fwlink/?LinkId=2104294&clcid=0x409

The privacy statement can be viewed at:

https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409

Do you accept the license terms? [Yes/No]:Yes

Enter the SQL Server system administrator password:

Confirm the SQL Server system administrator password:

Configuring SQL Server...

ForceFlush is enabled for this instance.

ForceFlush feature is enabled for log durability.

Created symlink /etc/systemd/system/multi-user.target.wants/mssql-server.service → /usr/lib/systemd/system/mssql-server.service.

Setup has completed successfully. SQL Server is now starting.

[root@ol8 ~]# systemctl status mssql-server

● mssql-server.service - Microsoft SQL Server Database Engine

   Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendor preset: disabled)

   Active: active (running) since Sat 2020-08-29 13:14:01 +08; 19s ago

     Docs: https://docs.microsoft.com/en-us/sql/linux

 Main PID: 94338 (sqlservr)

    Tasks: 142

   Memory: 645.6M

   CGroup: /system.slice/mssql-server.service

           ├─94338 /opt/mssql/bin/sqlservr

           └─94361 /opt/mssql/bin/sqlservr

Aug 29 13:14:05 ol8.oci.net sqlservr[94338]: [158B blob data]

Aug 29 13:14:05 ol8.oci.net sqlservr[94338]: [155B blob data]

Aug 29 13:14:05 ol8.oci.net sqlservr[94338]: [61B blob data]

Aug 29 13:14:05 ol8.oci.net sqlservr[94338]: [96B blob data]

Aug 29 13:14:05 ol8.oci.net sqlservr[94338]: [66B blob data]

Aug 29 13:14:06 ol8.oci.net sqlservr[94338]: [75B blob data]

Aug 29 13:14:06 ol8.oci.net sqlservr[94338]: [96B blob data]

Aug 29 13:14:06 ol8.oci.net sqlservr[94338]: [100B blob data]

Aug 29 13:14:06 ol8.oci.net sqlservr[94338]: [71B blob data]

Aug 29 13:14:06 ol8.oci.net sqlservr[94338]: [124B blob data]

Recursive "WITH" clause

Code:

create table flights (
    source          varchar(10),
    destination     varchar2(10),
    flight_time     number(3,1)
    );
    
insert into flights values ('Bei Jing', 'Shang Hai',2);
insert into flights values ('Shang Hai', 'Singapore',5.5);
insert into flights values ('Singapore', 'Sydney',9);

commit;

WITH Reachable_From (source, destination, total_flight_time) 
AS
(
    SELECT source, destination, flight_time from flights
  UNION ALL
    SELECT incoming.source, outgoing.destination, 
        incoming.total_flight_time+outgoing.flight_time
    FROM Reachable_From incoming, flights outgoing
    WHERE incoming.destination=outgoing.source
)
SELECT source, destination, total_flight_time
FROM Reachable_From;

Output: