VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > 数据库 > T-SQL >

  • 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');

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] 
)
复制代码

 

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场景性能测试

https://devblogs.microsoft.com/azure-sql/ingest-millions-of-events-per-second-on-azure-sql-leveraging-shock-absorber-pattern/

 

JSON性能测试

https://devblogs.microsoft.com/azure-sql/json-in-your-azure-sql-database-lets-benchmark-some-options/

 

开源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/

出处：https://www.cnblogs.com/dexterz/p/14483866.html


相关教程