New Development in SQL Server 2019 – Scalar Functions

Download SQLDepository Database:

http://sqldepository.com/sqldepository-database/

CREATE OR ALTER Function dbo.fn_TrackLatest (@OrderNumber int)
RETURNS tinyint
AS
BEGIN
DECLARE @TNum tinyint
DECLARE @StatusID tinyint
Select @TNum=Max(TrackingNumber)  from Tracking Where OrderNumber=@OrderNumber
Select @StatusID=StatusID from Tracking Where OrderNumber=@OrderNumber And TrackingNumber=@TNum
RETURN (SELECT @StatusID AS StatusID)
END
GO


DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE


USE [master]
GO
ALTER DATABASE [SQLDepository] SET COMPATIBILITY_LEVEL = 140
GO
USE [SQLDepository]
GO
select  top 20000 o.OrderNumber,O.SourceID,dbo.fn_TrackLatest(o.OrderNumber) Latest_Status from Orders o

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE


USE [master]
GO
ALTER DATABASE [SQLDepository] SET COMPATIBILITY_LEVEL = 150
GO
USE [SQLDepository]
GO
select  top 20000 o.OrderNumber,O.SourceID,dbo.fn_TrackLatest(o.OrderNumber) Latest_Status from Orders o

Author: Sauras Pandey

Sauras Pandey is an MCP, SQL Server DBA/Architect with 9+ years of experience working in a variety of environments

Leave a Reply

Your email address will not be published.