[SQL Server]comparison of In-memory OLTP differences between SQL2016 and SQL2017

2018-02-25

587
0
SQL Server 2017
2018-02-27

SQL2017針對In-Memory OLTP又改善不少，這篇來整理一下和SQL2016的比較。

我在正式環境使用In-Memory技術也快15個月了，未來我可能會使用SQL2017，

我很開心MS有持續改善In-Memory，這對一位技術人來說就是一大福音。

Memory optimized object

對於JSON、cross apply、CASE能使用在native真的太棒了，

另外，終於可以透過sp_reanme重新命名 natively modules or memory tables。

我額外測試了alter table(example:change size of column,change null/not null)，

但和SQL2016比較起來感覺差沒多少，雖然MS宣稱SQL2017執行時採取最小log和parallel，

大部分情況會比較快。

Transaction log reDO也採取parallel(disk table在SQL2016已經為parallel)，

看來可以減少發生high redo latency潛在瓶頸。

另外，我測試新的string function in native modules一切正常(希望可以支援更多function)

TRANSLATE:取代字元更簡單，可以不用寫很醜的nested replace

CREATE OR ALTER PROCEDURE dbo.My_TranslateTest_Native
WITH NATIVE_COMPILATION, SCHEMABINDING AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
    SELECT TRANSLATE('[137.4, 72.3]' , '[,]', '( )') AS Point,
    TRANSLATE('(137.4 72.3)' , '( )', '[,]') AS Coordinates;
END;
GO
EXEC dbo.My_TranslateTest_Native
GO

TRIM (Transact-SQL):移除首尾空白字元(character(32))

CREATE OR ALTER PROCEDURE dbo.My_TrimTest_Native
WITH NATIVE_COMPILATION, SCHEMABINDING AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
    select len(withoutspace), len(withspace) from (
	SELECT TRIM (' rico is me ') AS withoutspace,' rico is me ' AS withspace
	) base
   
END;
GO
EXEC dbo.My_TrimTest_Native
GO

CONCAT_WS:指定分隔符號串接

CREATE OR ALTER PROCEDURE dbo.My_CONCAT_WSTest_Native
WITH NATIVE_COMPILATION, SCHEMABINDING AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
   SELECT CONCAT_WS( ' - ', VehicleTemperatureID, ChillerSensorNumber, Temperature) AS MyInfo
 FROM Warehouse.VehicleTemperatures
   
END;
GO
EXEC dbo.My_CONCAT_WSTest_Native
GO

STRING_AGG:合併string，會忽略null，長度限制會參考來源長度

CREATE OR ALTER PROCEDURE dbo.My_STRING_AGGTest_Native
WITH NATIVE_COMPILATION, SCHEMABINDING AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
   --you cant use CHAR(13) in native modules
   SELECT STRING_AGG(VehicleRegistration, ',') AS csv
    FROM Warehouse.VehicleTemperatures
    WHERE Warehouse.VehicleTemperatures.VehicleTemperatureID BETWEEN 65000 AND 65100
   
END;
GO
EXEC dbo.My_STRING_AGGTest_Native
GO