SQL Server 的隐藏功能

SQL Server 的隐藏功能

sql
-- 创建表
CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name NVARCHAR(50),
    Salary INT MASKED WITH (FUNCTION = 'random(5000, 10000)')
);
-- 创建角色并授权
CREATE ROLE HR;
GRANT SELECT ON Employees TO HR;
-- 创建动态数据遮罩
ALTER TABLE Employees
ALTER COLUMN Salary ADD MASKED WITH (FUNCTION = 'default()');
-- 授予角色成员
ALTER ROLE HR ADD MEMBER [YourDomain\YourUser];

sql
-- 创建计划指南
EXEC sp_create_plan_guide 
    @name = N'Guide1',
    @stmt = N'SELECT * FROM Customers WHERE Country = @country',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@country nvarchar(15)',
    @hints = N'OPTION (OPTIMIZE FOR (@country = N''USA''))';
-- 使用计划指南执行查询
EXEC sp_execute_external_script 
    @language = N'R',
    @script = N'SELECT * FROM Customers WHERE Country = @country',
    @input_data_1 = N'@country nvarchar(15)',
    @params = N'@country = N''USA''';

sql
-- 创建增量统计
CREATE STATISTICS MyStats ON Orders (OrderDate) 
    WITH FULLSCAN, INCREMENTAL = ON;
-- 查询使用增量统计的计划
DBCC SHOW_STATISTICS('Orders', MyStats) WITH STAT_HEADER;