SET Statistics TIME ON

--String Truncation
--Trace flag defaulted in CTP2.4, backported to 2017 CU12
--DBCC TRACEON(460)

CREATE TABLE #LongString
(
 [FirstName] VARCHAR(8),
 [LastName] VARCHAR(5),
)
GO

INSERT INTO #LongString VALUES
('Bob', 'Ward'),
('Brent', 'Ozar'),
('Joe', 'Sack'),
('Eugene', 'Meidinger')

DROP TABLE #LongString

SELECT APPROX_COUNT_DISTINCT(CarrierTrackingNumber) FROM AdventureWorks2016.sales.SalesOrderDetail;
SELECT COUNT(DISTINCT CarrierTrackingNumber) FROM AdventureWorks2016.sales.SalesOrderDetail;

SELECT (APPROX_COUNT_DISTINCT(CarrierTrackingNumber)-COUNT(DISTINCT CarrierTrackingNumber))/COUNT(DISTINCT CarrierTrackingNumber) FROM AdventureWorks2016.sales.SalesOrderDetail;

-- Adaptive memory grant

ALTER INDEX [PK_SalesOrderHeader_SalesOrderID] ON [Sales].[SalesOrderHeader] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

SELECT TOP 101 * FROM Sales.SalesOrderHeader ORDER by Comment, TotalDue, SalesOrderNumber;

--Further reading: https://www.brentozar.com/archive/2018/09/whats-new-in-sql-server-2019-




-- Scalar UDF Inlining
USE AdventureWorks2016
ALTER DATABASE [AdventureWorks2016] SET COMPATIBILITY_LEVEL = 140

GO

CREATE FUNCTION dbo.discount_price(@OrderQty smallint, @UnitPrice money, @UnitPriceDiscount money) 
RETURNS DECIMAL (12,2) AS
BEGIN
  RETURN @OrderQty * (@UnitPrice - @UnitPriceDiscount);
END

GO

SELECT sod.CarrierTrackingNumber, SOH.SalesOrderID, SUM(OrderQty * (UnitPrice - UnitPriceDiscount)) AS TotalSales 
FROM Sales.SalesOrderDetail sod
INNER JOIN Sales.SalesOrderHeader soh
ON sod.SalesOrderID = soh.SalesOrderID
GROUP BY sod.CarrierTrackingNumber, Soh.SalesOrderID
ORDER BY sod.CarrierTrackingNumber;

SELECT sod.CarrierTrackingNumber, soh.SalesOrderID, SUM(dbo.discount_price(OrderQty, UnitPrice, UnitPriceDiscount)) AS TotalSales
FROM Sales.SalesOrderDetail sod
INNER JOIN Sales.SalesOrderHeader soh
ON sod.SalesOrderID = soh.SalesOrderID
GROUP BY sod.CarrierTrackingNumber, Soh.SalesOrderID
ORDER BY sod.CarrierTrackingNumber;

USE AdventureWorks2016
ALTER DATABASE [AdventureWorks2016] SET COMPATIBILITY_LEVEL = 150

GO

SELECT sod.CarrierTrackingNumber, SOH.SalesOrderID, SUM(OrderQty * (UnitPrice - UnitPriceDiscount)) AS TotalSales 
FROM Sales.SalesOrderDetail sod
INNER JOIN Sales.SalesOrderHeader soh
ON sod.SalesOrderID = soh.SalesOrderID
GROUP BY sod.CarrierTrackingNumber, Soh.SalesOrderID
ORDER BY sod.CarrierTrackingNumber;

SELECT sod.CarrierTrackingNumber, soh.SalesOrderID, SUM(dbo.discount_price(OrderQty, UnitPrice, UnitPriceDiscount)) AS TotalSales
FROM Sales.SalesOrderDetail sod
INNER JOIN Sales.SalesOrderHeader soh
ON sod.SalesOrderID = soh.SalesOrderID
GROUP BY sod.CarrierTrackingNumber, Soh.SalesOrderID
ORDER BY sod.CarrierTrackingNumber;


--Batch mode
USE [WideWorldImporters] 
GO
ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 140
GO


--https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/09/24/introducing-batch-mode-on-rowstore/
SELECT StockItemID,
       SUM([Quantity])                                 AS SUM_QTY,
       SUM([UnitPrice])                        AS SUM_BASE_PRICE,
       SUM([UnitPrice]*(1+[TaxRate]))         AS SUM_DISC_PRICE,
       SUM(([UnitPrice]+[Quantity] )*(1+[TaxRate]))     AS SUM_CHARGE,
       AVG([Quantity])                                 AS AVG_QTY,
       AVG([UnitPrice])                        AS AVG_PRICE,
       COUNT(*)                                 AS COUNT_ORDER
FROM   WideWorldImporters.Sales.OrderLines
GROUP  BY     StockItemID
ORDER  BY     StockItemID;


USE [WideWorldImporters] 
GO
ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 150
GO

SELECT StockItemID,
       SUM([Quantity])                                 AS SUM_QTY,
       SUM([UnitPrice])                        AS SUM_BASE_PRICE,
       SUM([UnitPrice]*(1+[TaxRate]))         AS SUM_DISC_PRICE,
       SUM(([UnitPrice]+[Quantity] )*(1+[TaxRate]))     AS SUM_CHARGE,
       AVG([Quantity])                                 AS AVG_QTY,
       AVG([UnitPrice])                        AS AVG_PRICE,
       COUNT(*)                                 AS COUNT_ORDER
FROM   WideWorldImporters.Sales.OrderLines
GROUP  BY     StockItemID
ORDER  BY     StockItemID;

-- UTF-8
-- Queries courtesy of sqlquantumleap.com

;WITH nums([CodePoint]) AS
(
  SELECT TOP (65536) (ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1)
  FROM   [master].[sys].[columns] col
  CROSS JOIN [master].[sys].[objects] obj
), chars AS
(
  SELECT nums.[CodePoint],
         CONVERT(VARCHAR(4), NCHAR(nums.[CodePoint])
                    COLLATE Latin1_General_100_CI_AS_SC_UTF8) AS [TheChar],
         CONVERT(VARBINARY(4), CONVERT(VARCHAR(4), NCHAR(nums.[CodePoint])
                    COLLATE Latin1_General_100_CI_AS_SC_UTF8)) AS [UTF8]
  FROM   nums
  UNION ALL
  SELECT tmp.val,
         CONVERT(VARCHAR(4), CONVERT(NVARCHAR(5), tmp.hex)
                    COLLATE Latin1_General_100_CI_AS_SC_UTF8) AS [TheChar],
         CONVERT(VARBINARY(4), CONVERT(VARCHAR(4), CONVERT(NVARCHAR(5),
              tmp.hex) COLLATE Latin1_General_100_CI_AS_SC_UTF8)) AS [UTF8]
  FROM   (VALUES (65536, 0x00D800DC), -- Linear B Syllable B008 A (U+10000)
                 (67618, 0x02D822DC), -- Cypriot Syllable Pu (U+10822)
                 (129384,0x3ED868DD)  -- Pretzel (U+1F968)
         ) tmp(val, hex)
)
SELECT chr.[CodePoint],
       COALESCE(chr.[TheChar], N'TOTALS:') AS [Character],
       chr.[UTF8] AS [UTF8_Hex],
       DATALENGTH(chr.[UTF8]) AS [UTF8_Bytes],
       COUNT(CASE DATALENGTH(chr.[UTF8]) WHEN 1 THEN 'x' END) AS [1-byte],
       COUNT(CASE DATALENGTH(chr.[UTF8]) WHEN 2 THEN 'x' END) AS [2-bytes],
       COUNT(CASE DATALENGTH(chr.[UTF8]) WHEN 3 THEN 'x' END) AS [3-bytes],
       COUNT(CASE DATALENGTH(chr.[UTF8]) WHEN 4 THEN 'x' END) AS [4-bytes],
      ---
      CONVERT(VARBINARY(4), CONVERT(NVARCHAR(3), chr.[TheChar]))
         AS [UTF16(LE)_Hex],
      DATALENGTH(CONVERT(NVARCHAR(3), chr.[TheChar])) AS [UTF16_Bytes],
      ---
      ((DATALENGTH(CONVERT(NVARCHAR(3), chr.[TheChar]))) -
            (DATALENGTH(chr.[TheChar]))) AS [UTF8savingsOverUTF16]
FROM   chars chr
GROUP BY ROLLUP ((chr.[CodePoint], chr.[TheChar], chr.[UTF8]));

-- Further Reading: https://sqlquantumleap.com/2018/09/28/native-utf-8-support-in-sql-server-2019-savior-false-prophet-or-both/



/*

-- https://www.sqlshack.com/row-mode-memory-grant-feedback-sql-server-2019/

USE [WideWorldImporters] 
GO
ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 140
GO

DBCC FREEPROCCACHE

--Takes a minute to run
SELECT 
  OD.CustomerID,OD.CustomerPurchaseOrderNumber,
  OD.InternalComments,OL.Quantity,OL.UnitPrice
  FROM [Sales].[Orders] OD
INNER JOIN [Sales].[OrderLines] OL
ON OD.OrderID = OL.OrderID
ORDER BY OD.[Comments]

ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 150
GO

Select Compatibility_level,name  from sys.databases
where name='WideWorldImporters'

DBCC FREEPROCCACHE

SELECT 
  OD.CustomerID,OD.CustomerPurchaseOrderNumber,
  OD.InternalComments,OL.Quantity,OL.UnitPrice
  FROM [Sales].[Orders] OD
INNER JOIN [Sales].[OrderLines] OL
ON OD.OrderID = OL.OrderID
ORDER BY OD.[Comments]

SELECT 
  OD.CustomerID,OD.CustomerPurchaseOrderNumber,
  OD.InternalComments,OL.Quantity,OL.UnitPrice
  FROM [Sales].[Orders] OD
INNER JOIN [Sales].[OrderLines] OL
ON OD.OrderID = OL.OrderID
ORDER BY OD.[Comments]
*/