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

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)

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

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]}‘;

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‘

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;

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]);

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

--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)

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

--当前表使用内存表
--历史表使用列存储表+非聚集行索引
CREATE CLUSTERED COLUMNSTORE INDEX cci_DepartmentHistory
ON DepartmentHistory;

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

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] 
)