SQL Database for Modern Developers

覆周發表於2021-03-05

SQLDatabaseDeveloper

好書分享，面向開發者的Azure SQL Database最佳實踐，也適用SQL Server 2016以上的版本。應對不同場景使用的資料庫功能，包括記憶體表，列儲存表，非聚集列儲存索引，JSON等等。

下載地址：

https://all-ebook.info/9978-practical-azure-sql-database-for-modern-developers.html

基礎SQL

Subqueries

Common Table Expressions

Union

Merge

MERGE INTO
[Warehouse].[Colors] AS [target]
USING
(VALUES
    (50, 'Deep Sea Blue'),
    (51, 'Deep Sea Light Blue'),
    (52, 'Deep Sea Dark Blue')
) [source](Id, [Name])
ON
[target].[ColorID] = [source].[Id]
WHEN MATCHED THEN
UPDATE SET [target].[ColorName] = [source].[Name]
WHEN NOT MATCHED THEN
INSERT ([ColorID], [ColorName], [LastEditedBy]) VALUES ([source].Id,
[source].[Name], 1)
WHEN NOT MATCHED BY SOURCE AND [target].[ColorID] BETWEEN 50 AND 100 THEN
DELETE

Windowing Functions

SELECT
    [OrderID],
    [OrderLineID],
    [Description],
    [Quantity],
    SUM(Quantity) OVER ( PARTITION BY [OrderID] ORDER BY [OrderLineID] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM
    [Sales].[OrderLines]
WHERE
[OrderID] in (37, 39)

進階SQL

Variables

T-SQL doesn't support arrays, lists, or dictionaries, you can use a Table Variable

DECLARE @t AS TABLE (
    [Id] INT NOT NULL,
    [Name] NVARCHAR(50) NOT NULL
);
INSERT INTO @t VALUES (42, N'John');
SELECT * FROM @t;

Temporary Tables

Views

Functions

Stored Procedures

使用json傳遞多值引數

CREATE OR ALTER PROCEDURE dbo.GetOrderForCustomer
@CustomerInfo NVARCHAR(MAX)
AS
IF (ISJSON(@CustomerInfo) != 1) BEGIN
THROW 50000, '@CustomerInfo is not a valid JSON document', 16
END
SELECT [Value] INTO #T FROM OPENJSON(@CustomerInfo, '$.CustomerId') AS ci;
SELECT
    [CustomerID],
    COUNT(*) AS OrderCount,
    MIN([OrderDate]) AS FirstOrder,
    MAX([OrderDate]) AS LastOrder
FROM
Sales.[Orders]
WHERE
[CustomerID] IN (SELECT [Value] FROM #T)
GROUP BY
[CustomerID];

EXEC dbo.GetOrderForCustomer N'{"CustomerId": [106, 193, 832]}';

unless you have some specific use case that is perfectly suited for a Function, the recommendation is to use Stored Procedures

永遠不要用觸發器了

JSON

動態引數，擴充屬性列，彈性域

CSV

STRING_SPLIT

CREATE PROCEDURE dbo.AddTagsToPost
@PostId INT,
@Tags NVARCHAR(MAX)
AS
INSERT INTO dbo.PostTags
SELECT @PostId, T.[value] FROM STRING_SPLIT(@Tags, '|') AS T
 
EXEC dbo.AddTagsToPost 1, 'azure-sql|string_split|csv'

Change Tracking

CDC

替代方案Debezium+ Kafka

Row-Level Security

CREATE FUNCTION rls.LoginSecurityPolicy(@PersonID AS INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT
1 As [Authorized]
FROM
[Application].[People]
WHERE
LoginName = SESSION_CONTEXT(N'Login')
AND
PersonID = @PersonId;

system function SESSION_CONTEXT to retrieve the value of the Logon key

Dynamic Data Masking

Always Encrypted

多模

JSON

SELECT
    severity,
    ip = JSON_VALUE(log, '$.ip'),
    duration = AVG(CAST(JSON_VALUE(log,'$.duration') as int))
FROM
WebSite.Logs
WHERE
CAST(JSON_VALUE(log,'$.date') as datetime) > @datetime 
GROUP BY
severity, JSON_VALUE(log, '$.ip')
HAVING
AVG(CAST(JSON_VALUE(log,'$.duration') as int) ) > 100
ORDER BY
AVG(CAST(JSON_VALUE(log,'$.duration') as int) );

ALTER TABLE Webite.Logs
ADD CONSTRAINT [Data should be formatted as JSON]
CHECK (ISJSON(log) = 1);

CREATE CLUSTERED COLUMNSTORE INDEX cci ON WebSite.Logs;

alter table WebSite.Logs
add [$severity] AS JSON_VALUE(log, '$.severity');

create index ix_severity on WebSite.Logs ([$severity]);

Graph

Spatial data

XML data

Key-value

Azure SQL doesn't have a specialized structure that holds key-value pairs.

With memory-optimized tables, you can index the key column using B-tree or Hash indexes-使用記憶體表代替

CREATE TABLE [Cache] (
    [key] BIGINT IDENTITY,
    value NVARCHAR(MAX),
    INDEX IX_Hash_Key HASH ([key]) WITH (BUCKET_COUNT = 100000)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);

More Than Tables

列儲存表

聚集列儲存表

非聚集列儲存索引

記憶體表

using classic rowstore tables with NCCI indexes for HTAP scenarios is the general approach，

Memory-Optimized Clustered Columnstore Indexes are used only on very performance-intensive workloads.

--Columnstore memory-optimized tables
CREATE TABLE Accounts (
    AccountKey int NOT NULL PRIMARY KEY NONCLUSTERED,
    Description nvarchar (50),
    Type nvarchar(50),
    UnitSold int,
INDEX cci CLUSTERED COLUMNSTORE
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)

--memory-optimized tables and the NONCLUSTERED HASH index
CREATE TABLE [dbo].[Employees](
    [EmpID] [int] NOT NULL
    CONSTRAINT PK_Employees_EmpID PRIMARY KEY
    NONCLUSTERED HASH (EmpID) WITH (BUCKET_COUNT = 100000),
    [EmpName] [varchar](50) NOT NULL,
    [EmpAddress] [varchar](50) NOT NULL,
    [EmpDEPID] [int] NOT NULL,
    [EmpBirthDay] [datetime] NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)

Natively compiled code

CREATE FUNCTION PeopleData(@json nvarchar(max))
RETURNS TABLE
WITH NATIVE_COMPILATION, SCHEMABINDING
AS RETURN (
SELECT Title, HireDate, PrimarySalesTerritory,
    CommissionRate, OtherLanguages
FROM OPENJSON(@json)
WITH(Title nvarchar(50),
    HireDate datetime2,
    PrimarySalesTerritory nvarchar(50),
    CommissionRate float,
    OtherLanguages nvarchar(max) AS JSON)
)

--呼叫函式
select p.FullName, p.EmailAddress, j.Title, j.CommissionRate
from Application.People p
cross apply PeopleData(p.CustomFields) j

Temporal tables

--當前表使用記憶體表
--歷史表使用列儲存表+非聚集行索引
CREATE CLUSTERED COLUMNSTORE INDEX cci_DepartmentHistory
ON DepartmentHistory;

CREATE NONCLUSTERED INDEX IX_DepartmentHistory_ID_PERIOD_COLUMNS
ON DepartmentHistory (SysEndTime, SysStartTime, DeptID);

HTAP

列儲存索引+記憶體表做實時混合負載，HTAP場景

https://docs.microsoft.com/zh-cn/sql/relational-databases/indexes/get-started-with-columnstore-for-real-time-operational-analytics?view=sql-server-ver15

本書作者的部落格

https://github.com/yorek

insert if not exists

insert into [dbo].[tags] ([post_id], [tag]) 
select * from ( 
    values (10, 'tag123') -- sample value 
) as s([post_id], [tag]) 
where not exists ( 
    select * from [dbo].[tags] t with (updlock) 
    where s.[post_id] = t.[post_id] and s.[tag] = t.[tag] 
)