in what situations the dynamic port number in sql server is used?

Question

I have the following script to find out what is the Static and dynamic port numbers:

--===============================
-- sys.dm_exec_connections
--===============================
SELECT 
[user]=suser_sname()
,local_tcp_port
,session_id
,connect_time
,net_transport
,protocol_type
,encrypt_option
,auth_scheme
,last_read
,last_write

FROM   sys.dm_exec_connections
WHERE  session_id = @@SPID
GO

--===============================
-- Execute below script if SQL Server is configured with dynamic port number
--===============================
DECLARE       @portNo   NVARCHAR(10)

EXEC   xp_instance_regread
@rootkey    = 'HKEY_LOCAL_MACHINE',
@key        =
'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
@value_name = 'TcpDynamicPorts',
@value      = @portNo OUTPUT

SELECT [PortNumber] = @portNo
GO

--===============================
-- Execute below script if SQL Server is configured with static port number
--===============================
DECLARE       @portNo   NVARCHAR(10)

EXEC   xp_instance_regread
@rootkey    = 'HKEY_LOCAL_MACHINE',
@key        =
'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
@value_name = 'TcpPort',
@value      = @portNo OUTPUT

SELECT [PortNumber] = @portNo
GO

The following link mentions netstat -ano to check usage from the cmdline.

When is a Dynamic Port “dynamic”?

when is the dynamic port used?

Answer 1

Dynamic ports are used when a SQL Server instance is configured to use dynamic ports on the specified interface(s). By default, only named instances use dynamic ports as described in the documentation. Below is an excerpt from the doc:

At startup, when an instance of SQL Server is configured to listen on dynamic ports, it checks with the operating system for an available port, and opens an endpoint for that port. Incoming connections must specify that port number to connect. Since the port number can change each time SQL Server starts, SQL Server provides the SQL Server Browser Service, to monitor the ports, and direct incoming connections to the current port for that instance. Using dynamic ports complicates connecting SQL Server through a firewall because the port number may change when SQL Server is restarted, requiring changes to the firewall settings. To avoid connection problems through a firewall, configure SQL Server to use a static port.

The SQL Server error log will also include messages indicating the listening interface IP addresses and ports.

EDIT:

As mentioned by @TiborKaraszi, SQL Server remembers that last used dynamic port and will continue to use that as the going forward unless there's a conflict, in which case a new one is used.