SQL Database for Modern Developers
好书分享，面向开发者的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’);
go
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场景
本书作者的博客
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]
)
https://devblogs.microsoft.com/azure-sql/the-insert-if-not-exists-challenge-a-solution/
多行插入使用JSON, Table Valued Parameters，与MERGE的比较
key-value store性能测试
https://devblogs.microsoft.com/azure-sql/azure-sql-database-as-a-key-value-store/
IoT场景性能测试
JSON性能测试
开源web sql编辑器SQLPad
https://devblogs.microsoft.com/azure-sql/querying-and-visualizing-data-using-sqlpad/
DevOps for Azure SQL
https://devblogs.microsoft.com/azure-sql/devops-for-azure-sql/