Эволюция не стоит на месте, она затрагивает не только живое, но и все окружающие нас вещи, в том числе и SQL Server.
Появляются новые возможности которые существенно упрощают жизнь не только разработчикам , админам , но и конечным потребителям информации.
Ниже опишу полезные возможности которые были найдены на просторах интернета в различных статьях. Полный список от МС здесь.
Для начала изучения возможностей, скачиваем примеры которые есть у MS, затем распаковываем.
В папке будут примеры по каждой новой возможности, рассмотрим кратко каждую из них.
Temporal – исторические таблицы
Отличается от обычной таблицы возможностью хранить историю изменений строк без создания триггеров и дополнительных таблиц. Подробнее здесь
Для включения опции в таблице должны быть два столбца дат с типом datetime2 и опцией GENERATED ALWAYS AS ROW START и GENERATED ALWAYS AS ROW END
+ необходимо включить опцию версионирования таблицы с указанием имени исторической таблицы WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));
--Исторические таблицы, любую таблицу можно преобразовать в историческую
CREATE TABLE Employee
(
EmployeeID int Primary KEY,
FirstName varchar(20) NOT NULL,
LastName varchar(20) NOT NULL,
DepartmentName varchar(50) NOT NULL
)
GO
INSERT INTO Employee VALUES
(1,'Ken','Sanchez','Executive'),
(2,'Terri','Sanchez','Engeneering'),
(3,'Roberto','Sanchez','Engeneering'),
(4,'Bob','Sanchez','Engeneering'),
(5,'Gail','Sanchez','Support'),
(6,'Dylan','Sanchez','Support')
select * from employee
--чтобы скрыть столбцы дат, добавьте hidden в инструкцию
ALTER TABLE EMPLOYEE ADD
StartDate datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL DEFAULT CAST('1901-01-01 00:00:00.0000000' as datetime2),
EndDate datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL DEFAULT CAST('9999-12-31 23:59:59.9999999' as datetime2),
PERIOD FOR SYSTEM_TIME( StartDate,EndDate)
GO
ALTER TABLE Employee SET (system_versioning = on(history_table=dbo.EmployeeHistory))
GO
--TEST
UPDATE employee
SET LastName = 'Markao'
WHERE employeeid = 2
GO
UPDATE employee
SET LastName = 'Johnson'
WHERE employeeid = 3
GO
UPDATE employee
SET LastName = 'Ganja'
WHERE employeeid = 4
GO
DELETE employee WHERE employeeid = 5
GO
--Disable versioning
Alter table employee SET (system_versioning = off)
--Enable versioning
Alter table employee SET (system_versioning = ON)
--Проверяем данные
select * from employee order by employeeID , Startdate
Declare @datestart datetime2 = dateadd(ss,-5,getdate())
select * from employee for system_time as of @datestart
Declare @datestart datetime2 = dateadd(ss,-10,getdate())
select * from employee for system_time as of @datestart
Declare @datestart datetime2 = dateadd(ss,-15,getdate())
select * from employee for system_time as of @datestart
--Добавление столбца, автоматически добавляет его в историческую таблицу
alter table employee add RegionID int null
--Cleanup
Alter table employee SET (system_versioning = OFF)
drop table if exists EmployeeHistory
drop table if exists employee
Работа с JSON форматом данных
Отдельного типа данных в самом SQL сервере не появилось, однако данные можно хранить как обычный текст в полях типа VARCHAR или NVARCHAR. Подробно описано тут
Поэтому первым шагом нужно убедиться, что в тестовом поле содержится JSON объект функцией ISJSON
declare @jsondata as varchar(max) , @nonjsonData as varchar(max)
set @jsondata = N'{ "Employees": [ { "Contact": { "First": "Vasco", "Last": "Dagama" }, "Location": { "City": "Africa", "Address": "1921 Ranch Road", "Region": "United States" }, "Info": { "Phone": "166-555-0159", "Postal": "98027" } }, { "Contact": { "First": "Andreas", "Last": "Berglund" }, "Location": { "City": "Renton", "Address": "1803 Olive Hill", "Region": "United States" }, "Info": { "Phone": "181-555-0124", "Postal": "98055" } } ] }'
set @nonjsonData = N'Тестовый пример'
select ISJSON(@jsondata) json1, ISJSON(@nonjsonData) nonjson2
--получить значение из JSON_VALUE, JSON_QUERY получить часть JSON
select JSON_VALUE(@jsondata,'$.Employees[0].Location.City'), JSON_QUERY(@jsondata,'$.Employees')
Разбить JSON объект на строки можно функцией OPENJSON при необходимости можно указать схему по которой нужно развернуть данные в конструкции WITH
--разбить на строки json schema
select * from openjson (@jsondata,'$.Employees')
with
(
FirstName varchar(200) '$.Contact.First',
LastName varchar(200) '$.Contact.Last',
[Location] nvarchar(max) '$.Location' as Json,
Phone varchar(200) '$.Info.Phone'
)
-- добавить столбец на основе свойства JSON и проиндексировать его, лучше создавать вычисляемый столбец как persisted
alter table tmp_test_json
add City as json_value(JsonColmn,'$.Employees[0].Location.City') persisted
create index idx_tmp_test_json_city on tmp_test_json(City)
Генерация данных в формате JSON из таблицы, самый простой вариант добавить for json auto , если нужна своя схема for json path
По умолчанию NULL значения игнорируются если нужно включить их в результат добавляйте include_null_values
Опция without_array_wrapper позволяет вывести результат без квадратных скобок
-- сгенерировать свою схему используя json path
select
e.FirstName as [Contact.First],
e.LastName as [Contact.Last],
e.City as [Location.City],
e.AddressLine1 as [Location.Address],
e.CountryRegionName as [Location.Region],
e.PhoneNumber as [Info.Phone],
e.PostalCode as [Info.Postal]
from [HumanResources].[vEmployee] e for json path , root('Employees') , include_null_values
--вложенные json запросы
select
c.CustomerID,
c.AccountNumber,
(
select s.SalesOrderID , s.TotalDue , s.OrderDate , s.ShipDate from sales.SalesOrderHeader as s where c.CustomerID = s.CustomerID for json auto, root('salesorders')
) as Orderdetails
from Sales.Customer as c
where c.CustomerID between 11001 and 11003
order by c.CustomerID
Изменение данных JSON
--изменить значение в JSON
select json_modify(@jsondata, '$.Employees[0].Location.City','Toronto')
Переменные сессии , контекст сеанса
Для взаимодействия различных запросов в рамках одной сессии введено понятие контекста сессии, в рамках которого можно передавать переменные между различными запросами в виде пар ключ\значение.
@key – 128 байт, @value - 8000 байт, если передать значение NULL переменная освобождается
Устанавливается значение переменной процедурой sp_set_session_context, получить значение переменной сессии session_context
--для каждой сессии создается таблица с парой ключ значение и можно использовать как разделяемые переменные для различных запросов , даже с опцией только для чтения.
select session_context(N'Region')
exec sp_set_session_context @key = N'Region', @value = N'South'
--получить значение переменной из контекста
declare @test as nvarchar(20) = ''
set @test = cast(session_context(N'Region') as nvarchar(20))
select @test
--при попытке изменить переменную сессии с readonly возникнет ошибка
exec sp_set_session_context @key = N'Region', @value='West', @readonly=1
select session_context(N'Region')
exec sp_set_session_context @key = N'Region', @value = 'North'
Динамическая маскировка данных
В случае наличия в таблице важных данных которые необходимо скрыть от лишних глаз , таких как телефон, инн, номер паспорта, зп и тд
есть возможность включить на столбце опцию динамической маскировки, без дополнительных разрешений увидеть такие данные нельзя.
Поскольку механизм реализован на стороне SQL Server , то не требуется дополнительных доработок на стороне приложения , можно быстро включать-выключать видимость данных.
--dynamic data masking
DROP TABLE IF EXISTS tmp_DEMO_MASKING
CREATE TABLE tmp_DEMO_MASKING
(
ID uniqueidentifier,
NAME nvarchar(30) masked with (function='partial(1,"****",0)') NULL, --гибкая маскировка по шаблону, задается в формате partial(N1, “XXXXXXX”, N2), где N1 – количество символов с начала строки, которые можно показать, N2 – с конца а между ними указываете произвольную маску
Phone nvarchar(20) masked with (function = 'default()') NULL, --маскировка по умолчанию для строковых полей отображает XXXX, для числовых типов – 0, для дат – 1 января 1900г., а для бинарных – 0x30
Email nvarchar(20) masked with (function = 'email()') NULL, --маскировка почтовых адресов
Age int masked with (function = 'random(20,50)') --маскировка числовых типов данных, отображает случайное число из диапазона
)
INSERT INTO tmp_DEMO_MASKING VALUES
(
NEWID(),N'Jacoby Shardings','89257684567','Shardings@google.com' , 38
),
(
NEWID(),N'Peter Banch','89629999344','Banck@qvaker.coma', 44
)
-- узнать какие столбцы замаскированы
select t.name , m.name , m.is_masked , m.masking_function
from sys.masked_columns m
join sys.tables t on t.object_id = m.object_id
GO
CREATE VIEW dbo.V_UserPermissions as
select Username = pr.name,
Loginname = l.loginname,
Logintype = pr.type_desc,
PermissionState = pe.state_desc,
PermissionName = pe.permission_name,
PermissionClass = Pe.class_desc,
ObjectName = o.name,
ObjectType = o.type_desc
from sys.database_principals pr
join sys.database_permissions pe on pe.grantee_principal_id = pr.principal_id
join sys.sysusers u on u.uid = pr.principal_id
left outer join sys.objects o on o.object_id = pe.major_id
left outer join master..syslogins as l on u.sid = l.sid
where pr.name in('dbo','TestUser')
select * from dbo.V_UserPermissions
create user TestUser Without LOGIN
grant select on tmp_DEMO_MASKING to TestUser
execute as user = 'TestUser'
select * from dbo.tmp_DEMO_MASKING
revert
select * from dbo.tmp_DEMO_MASKING
Grant UNMASK TO TestUSER
select * from dbo.V_UserPermissions
execute as user = 'TestUser'
select * from dbo.tmp_DEMO_MASKING
revert
select CURRENT_USER
revoke UNMASK TO TestUSER
<![if !vml]>
<![endif]>
<![endif]>
Безопасность на уровне строк
Позволяет разграничивать доступ к строкам таблицы отдельно для каждого пользователя. Подробно тут.
Для этого необходимо создать политику безопасности и предикаты для разграничения доступа.
Существуют два вида предикатов, предикаты фильтров которые фильтруют строки , доступные для операций (SELECT, UPDATE и DELETE)
и предикаты блокировки , которые явно блокируют операции записи и изменения (AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELTE)
---ROW LEVEl SECURITY
drop table if exists sales
CREATE TABLE SALES
(
ORDERID int ,
SalesUsername varchar(50),
Product varchar(10),
Qty int
)
Insert into Sales values
(1,'SalesUser1','Valve',5),
(2,'SalesUser1','Wheel',2),
(3,'SalesUser1','Valve',4),
(4,'SalesUser2','Bracket',2),
(5,'SalesUser2','Wheel',5),
(6,'SalesUser2','Valve',5)
select * from sales
Create User SalesUser1 WITHOUT LOGIN
Create User SalesUser2 WITHOUT LOGIN
Create User ManagerUser WITHOUT LOGIN
GO
GRANT SELECT, UPDATE, INSERT,DELETE ON dbo.sales to SalesUser1, SalesUser2, ManagerUser
GO
CREATE SCHEMA sec
GO
CREATE function sec.fn_securitypredicate(@username varchar(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT 1 as fn_securitypredicate_result
WHERE DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID(@username) OR DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('ManagerUser')
GO
--test predicate function
select * from sec.fn_securitypredicate('SalesUser1')
select USER_NAME()
select * from sec.fn_securitypredicate('dbo')
--test for salesuser1
GRANT SELECT ON sec.fn_securitypredicate to SalesUser1
execute as user = 'SalesUser1'
select * from sec.fn_securitypredicate('SalesUser1')
select * from sec.fn_securitypredicate('SalesUser2')
revert
revoke select ON sec.fn_securitypredicate to SalesUser1
GO
--test for salesuser2
GRANT SELECT ON sec.fn_securitypredicate to SalesUser2
execute as user = 'SalesUser2'
select * from sec.fn_securitypredicate('SalesUser1')
select * from sec.fn_securitypredicate('SalesUser2')
revert
revoke select ON sec.fn_securitypredicate to SalesUser2
GO
--test for ManagerUser
GRANT SELECT ON sec.fn_securitypredicate to ManagerUser
execute as user = 'ManagerUser'
select * from sec.fn_securitypredicate('ManagerUser')
select * from sec.fn_securitypredicate('ManagerUser')
revert
revoke select ON sec.fn_securitypredicate to ManagerUser
GO
DROP security policy IF EXISTS sec.SalesPolicyFilter
go
CREATE security policy sec.SalesPolicyFilter
ADD filter predicate sec.fn_securitypredicate(salesusername) on dbo.sales ,
ADD block predicate sec.fn_securitypredicate(salesusername) on dbo.sales AFTER UPDATE,
ADD block predicate sec.fn_securitypredicate(salesusername) on dbo.sales AFTER INSERT
WITH (STATE = ON)
-- test under dbo
select * from sales
-- View salesuser1 rows
execute as user = 'SalesUser1'
select * from sales
update sales set qty=0 where salesusername = 'SalesUser2'
update sales set qty=qty+1 where salesusername = 'SalesUser1'
delete sales where salesusername = 'SalesUser2'
insert sales values ('9', 'SalesUser2','Boombox',1)
insert sales values ('9', 'SalesUser1','Boombox',1)
revert
-- View salesuser2 rows
execute as user = 'SalesUser2'
select * from sales
update sales set qty=0 where salesusername = 'SalesUser1'
update sales set qty=qty+1 where salesusername = 'SalesUser2'
delete sales where salesusername = 'SalesUser1'
insert sales values ('10', 'SalesUser2','Cleaner',1)
insert sales values ('10', 'SalesUser1','Cleaner',1)
revert
-- View ManagerUser rows
execute as user = 'ManagerUser'
select * from sales
revert
Мониторинг производительности с использованием хранилища запросов
Хранилище запросов сохраняет в себе все планы запросов и имеет позволяет использовать план из хранилища принудительный план для запроса, также в хранилище сохраняется статистика выполнения.
включается опцией SET QUERY_STORE = ON для каждой базы и содержит в себе три хранилища:
- хранилище планов для сохранения сведений о планах выполнения;
- хранилище статистики времени выполнения для сохранения статистических сведений о выполнении;
- хранилище статистики ожидания для сохранения статистических сведений об ожидании.
ALTER DATABASE
[AdventureWorks2016CTP3]
SET QUERY_STORE = ON
(
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 31), --длительность хранения данных в хранилище запросов в днях
DATA_FLUSH_INTERVAL_SECONDS = 900, --Определяет частоту, с которой данные, записанные в хранилище запросов, сохраняются на диск в секундах
INTERVAL_LENGTH_MINUTES = 5, --интервал вычисления статистических данных о среде выполнения в хранилище запросов
MAX_STORAGE_SIZE_MB = 1024, -- максимальный размер хранилища запросов.
QUERY_CAPTURE_MODE = ALL, --ALL (регистрировать все запросы), AUTO (игнорировать редкие запросы и запросы с малой продолжительностью компиляции и выполнения) или NONE (остановить регистрацию новых запросов).
SIZE_BASED_CLEANUP_MODE = AUTO --автоматическая очистка хранилища по достижению максимального объема
)
После включения опции появляется вкладка с настроенным анализом данных
--Активно ли сейчас хранилище запросов
SELECT actual_state, actual_state_desc, readonly_reason,
current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;
--параметры Query Store
SELECT * FROM sys.database_query_store_options;
--использование места
SELECT current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;
---------------------Анализ выполнения запросов
--последние запросы в базе данных
SELECT TOP 10 qt.query_sql_text, q.query_id,
qt.query_text_id, p.plan_id, rs.last_execution_time
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
ORDER BY rs.last_execution_time DESC;
--Сколько раз выполнлялся каждый запрос
SELECT q.query_id, qt.query_text_id, qt.query_sql_text,
SUM(rs.count_executions) AS total_execution_count
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
GROUP BY q.query_id, qt.query_text_id, qt.query_sql_text
ORDER BY total_execution_count DESC;
--запросы с самим высоким значение среднего времени выполнения за последний час
SELECT TOP 10 rs.avg_duration, qt.query_sql_text, q.query_id,
qt.query_text_id, p.plan_id, GETUTCDATE() AS CurrentUTCTime,
rs.last_execution_time
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(hour, -1, GETUTCDATE())
ORDER BY rs.avg_duration DESC;
--Большое количество операций ввода выода
SELECT TOP 10 rs.avg_physical_io_reads, qt.query_sql_text,
q.query_id, qt.query_text_id, p.plan_id, rs.runtime_stats_id,
rsi.start_time, rsi.end_time, rs.avg_rowcount, rs.count_executions
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
JOIN sys.query_store_runtime_stats_interval AS rsi
ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE rsi.start_time >= DATEADD(hour, -24, GETUTCDATE())
ORDER BY rs.avg_physical_io_reads DESC;
--Большие ожидания в запросах
SELECT TOP 10
qt.query_text_id,
q.query_id,
p.plan_id,
sum(total_query_wait_time_ms) AS sum_total_wait_ms
FROM sys.query_store_wait_stats ws
JOIN sys.query_store_plan p ON ws.plan_id = p.plan_id
JOIN sys.query_store_query q ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
GROUP BY qt.query_text_id, q.query_id, p.plan_id
ORDER BY sum_total_wait_ms DESC
SELECT TOP 10 qt.query_sql_text, q.query_id,
qt.query_text_id, p.plan_id, rs.last_execution_time
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
ON p.plan_id = rs.plan_id
ORDER BY rs.last_execution_time DESC;
--Очистка пространства
ALTER DATABASE [A_Poseshaemost_Project] SET QUERY_STORE CLEAR;
--Отключение Query Store
ALTER DATABASE [A_Poseshaemost_Project] SET QUERY_STORE = OFF
Небольшие полезные плюшки
Взято с хабра
--#1 – DROP IF EXISTS
--удаление таблицы с проверкой на существование
DROP TABLE IF EXISTS dbo.tbl
--#3 – CHECKDB + MAXDOP
--проверка базы на целостность с использованием потоков по количеству логических ядер.
DBCC CHECKDB(N'AdventureWorks2016CTP3') WITH MAXDOP = 4
--#5 – COMPRESS & DECOMPRESS
--В новой редакции появилась встроенная поддержка GZIP: COMPRESS и DECOMPRESS. При декодировании важно следить за правильным типов данных в который преобразуется результат:
DECLARE @a VARBINARY(MAX) = COMPRESS('test test test')
SELECT
@a
, DECOMPRESS(@a)
, CAST(DECOMPRESS(@a) AS NVARCHAR(MAX)) -- печаль
, CAST(DECOMPRESS(@a) AS VARCHAR(MAX))
--#6 – DATEDIFF_BIG. В SQL Server 2008 для функции DATEDIFF появились новые параметры: MICROSECOND и NANOSECOND, но когда задавался слишком большой диапазон дат:
SELECT DATEDIFF_BIG(NANOSECOND, '20000101', '20160101')
--#7 – AT TIME ZONE. С помощью AT TIME ZONE можно выводить время в заданной часовом поясе:
DECLARE @tz NVARCHAR(256) = N'Belarus Standard Time'
SELECT
GETDATE() AT TIME ZONE @tz
, CONVERT(DATETIME2, GETDATE()) AT TIME ZONE @tz
--#10 – TRUNCATE TABLE + PARTITIONS
-- Очистка секций
CREATE PARTITION FUNCTION PF (SMALLINT) AS RANGE RIGHT FOR VALUES (1, 2, 3, 4, 5)
GO
CREATE PARTITION SCHEME PS AS PARTITION PF ALL TO ([PRIMARY])
GO
DROP TABLE IF EXISTS dbo.tbl
CREATE TABLE dbo.tbl (a SMALLINT PRIMARY KEY) ON PS (a)
GO
INSERT INTO dbo.tbl (a)
VALUES (0), (1), (2), (3), (4), (5)
SELECT partition_number, [rows]
FROM sys.partitions
WHERE [object_id] = OBJECT_ID('dbo.tbl')
AND index_id < 2
TRUNCATE TABLE dbo.tbl WITH (PARTITIONS (1, 4 TO 5))
--#11 – CURRENT_TRANSACTION_ID
--Номер текущей транзакции
SELECT ec.session_id, s.[text]
FROM sys.dm_tran_session_transactions t
JOIN sys.dm_exec_connections ec ON t.session_id = ec.session_id
CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) s
WHERE t.transaction_id != CURRENT_TRANSACTION_ID()
