Перейти к основному содержимому

Справочник по Microsoft SQL Server

ОБЯЗАТЕЛЬНОДЛЯ НОВИЧКОВ

Разработчику Аналитику Тестировщику
Архитектору Инженеру

В энциклопедии ниже — справочник по T-SQL и объектам SQL Server на русском. Актуальные учебники, установка, инструменты и полный справочник языка — на Microsoft Learn (версия документации sql-server-ver17, SQL Server 2025). Тексты статей Learn сюда не копируются — только структура, ссылки и привязка к разделам проекта.

Интерактивный каталог: Microsoft Learn — навигатор · Подборка документации.

Официальная документация (Microsoft Learn)​

Документация по Microsoft SQL — хаб: SQL Server on Windows/Linux, Azure SQL, Fabric, миграция, разработка и развёртывание.

С чего начать​

ШагГде в энциклопедииОфициальный источник
1СУБД — обзорДвижок СУБД Microsoft SQL
2SQL — первые шагиУчебник: написание Transact-SQL
3Процедурные расширения — T-SQLСхема: запросы на Transact-SQL
4Эта глава (справочник)Справочник T-SQL
5Взаимодействие с СУБДПодключение к Database Engine

Дополнительно: советы по навигации в документации SQL, обучающие ресурсы.

Подключение и первый запрос​

ЗадачаСсылка Learn
Подключиться к экземпляру (Windows auth, Azure SQL, Docker)connect-to-database-engine
SSMS: подключение и запросssms-connect-query-sql-server
Azure SQL / локальный сервер в VS Codeconnect-query-vscode
Примеры БД (AdventureWorks и др.)sql-samples-where-are

Инструменты SQL Server​

Обзор бесплатных и платных средств: overview-sql-tools (вкладки DBA / разработчик / аналитик).

ИнструментНазначениеДокументация
SSMSАдминистрирование, T-SQL, планы выполненияУстановка SSMS · скачать
Azure Data StudioКросс-платформенный клиент, заметки, графикисм. overview-sql-tools
SSDTПроекты схемы БД в Visual Studio, DACPACsql-server-data-tools
sqlcmd / bcpСкрипты и автоматизация из CLIраздел "Утилиты" в overview-sql-tools
SqlPackageЭкспорт/импорт BACPAC, CI/CD схемыsqlpackage
SQL PowerShellInvoke-Sqlcmd, бэкапы, управлениеsql-server-powershell

T-SQL — учебник и справочник​

МатериалСсылка
Учебник (3 занятия: DDL/DML, права, очистка)tutorial-writing-transact-sql-statements
Занятие 1 — объекты и запросыlesson-1-creating-database-objects
Занятие 2 — разрешения, VIEW, процедурыlesson-2-configuring-permissions-on-database-objects
Занятие 3 — удаление объектовlesson-3-deleting-database-objects
Справочник языка (алфавитный)t-sql/language-reference
Функцииt-sql/functions
Интерактивная практикаGet started querying with T-SQL

Сравнение синтаксиса PL/SQL и T-SQL в энциклопедии: Процедурные расширения - PL/pgSQL, T-SQL — процедурные расширения.

Установка и подключение из приложений​

СценарийСсылка
SQL Server на Windowsinstall-sql-server
Express / Developer (бесплатно)скачать SQL Server
Драйверы и библиотеки (.NET, JDBC, ODBC, Node и др.)sql/connect

Практика после теории в энциклопедии​

  1. Установите SQL Server Express или Developer и SSMS (ссылки выше).
  2. Пройдите быстрый старт SSMS и учебник T-SQL (занятия 1–3).
  3. Разверните образцы БД и сверяйте конструкции с разделами 1–31 ниже.
  4. Для .NET-приложений — драйверы подключения и главу 4.10 — код и СУБД.

Справочник по Microsoft SQL Server​

1. Основные компоненты SQL Server​

1.1. Ядро СУБД (Database Engine)​

  • Отвечает за хранение, обработку и безопасность данных.
  • Поддерживает транзакции, репликацию, зеркальное отображение, Always On.
  • Работает в режимах: Windows Authentication, Mixed Mode (Windows + SQL Auth).

1.2. SQL Server Management Studio (SSMS)​

  • Графический клиент для управления экземплярами SQL Server.
  • Версии: SSMS 18.x, 19.x — совместимы с SQL Server 2016–2022.
  • Включает:
    • Object Explorer
    • Query Editor
    • Activity Monitor
    • Profiler (через расширения или Azure Data Studio)
    • Template Explorer
    • Registered Servers

1.3. T-SQL (Transact-SQL)​

  • Расширение стандарта SQL от Microsoft.
  • Поддерживает процедурное программирование, переменные, поток управления, обработку ошибок.
  • Используется во всех операциях — DDL, DML, DCL, TCL.

2. Типы данных SQL Server​

2.1. Числовые​

ТипДиапазонПримечание
BIT0, 1, NULLЛогическое значение
TINYINT0–2551 байт
SMALLINT-32 768 – 32 7672 байта
INT-2 147 483 648 – 2 147 483 6474 байта
BIGINT-2⁶³ – 2⁶³-18 байт
DECIMAL(p,s) / NUMERIC(p,s)До 38 цифрТочная дробь
MONEY-922 337 203 685 477.5808 – +922 337 203 685 477.58078 байт
SMALLMONEY-214 748.3648 – +214 748.36474 байта
FLOAT(n)Прибл. ±1.79E+308n = 1–53 (точность)
REALFLOAT(24)4 байта

2.2. Строковые​

ТипМакс. длинаЮникодПримечание
CHAR(n)8000НетФиксированная длина
VARCHAR(n)8000НетПеременная длина
VARCHAR(MAX)2³¹-1НетLOB-тип
NCHAR(n)4000ДаФиксированная, UTF-16
NVARCHAR(n)4000ДаПеременная, UTF-16
NVARCHAR(MAX)2³¹-1ДаLOB-тип
TEXTУстаревшийНетНе использовать
NTEXTУстаревшийДаНе использовать

2.3. Дата и время​

ТипДиапазонТочностьРазмер
DATE0001-01-01 – 9999-12-311 день3 байта
TIME(p)00:00:00.0000000 – 23:59:59.9999999100 нс3–5 байт
DATETIME2(p)0001-01-01 – 9999-12-31100 нс6–8 байт
DATETIME1753-01-01 – 9999-12-313.33 мс8 байт
SMALLDATETIME1900-01-01 – 2079-06-061 мин4 байта
DATETIMEOFFSET(p)Как DATETIME2 + часовой пояс100 нс8–10 байт

2.4. Бинарные​

ТипМакс. длинаПримечание
BINARY(n)8000Фиксированная
VARBINARY(n)8000Переменная
VARBINARY(MAX)2³¹-1LOB-тип
IMAGEУстаревшийНе использовать

2.5. Специальные​

  • UNIQUEIDENTIFIER — GUID (16 байт)
  • SQL_VARIANT — хранит любой тип (кроме LOB и TIMESTAMP)
  • TIMESTAMP / ROWVERSION — автоматически генерируемый номер версии строки
  • HIERARCHYID — древовидная структура
  • GEOMETRY, GEOGRAPHY — пространственные данные
  • XML — типизированный или нетипизированный XML

3. Объекты базы данных​

3.1. Таблицы​

  • CREATE TABLE
CREATE TABLE
  • ALTER TABLE
ALTER TABLE
  • DROP TABLE
DROP TABLE
  • Поддержка:
    • Identity (IDENTITY(1,1))
    • Computed columns
    • Constraints (PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE, DEFAULT)
    • Filegroups
    • Partitioning

3.2. Представления (Views)​

  • Виртуальные таблицы на основе запроса.
  • , ALTER VIEW, DROP VIEW
CREATE VIEW
  • привязка к схеме
WITH SCHEMABINDING
  • Обновляемые представления при соблюдении условий

3.3. Хранимые процедуры​

CREATE PROCEDURE dbo.GetUsers
    @Status INT = 1
AS
BEGIN
    SELECT * FROM Users WHERE Status = @Status;
END
  • Поддержка входных/выходных параметров
  • EXEC / EXECUTE
  • sp_executesql — для динамического SQL с параметрами

3.4. Функции​

Скалярные​

CREATE FUNCTION dbo.GetFullName(@FirstName NVARCHAR(50), @LastName NVARCHAR(50))
RETURNS NVARCHAR(101)
AS
BEGIN
    RETURN @FirstName + ' ' + @LastName;
END

Табличные​

  • Inline (возвращают одно выражение SELECT)
  • Multi-statement (создают временную таблицу внутри)

3.5. Триггеры​

  • AFTER / INSTEAD OF
  • На INSERT, UPDATE, DELETE
  • Доступ к inserted и deleted виртуальным таблицам

3.6. Индексы​

  • Кластеризованный — определяет физический порядок строк (один на таблицу)
  • Некластеризованный — до 999 на таблицу (SQL Server 2016+)
  • Columnstore — для аналитических нагрузок
  • Filtered — индекс с условием (WHERE Status = 1)
  • Unique, Composite, Included columns

3.7. Синонимы (Synonyms)​

  • Алиасы для объектов
CREATE SYNONYM dbo.Users FOR RemoteDB.dbo.Users

3.8. Последовательности (Sequences)​

  • Альтернатива IDENTITY
  • CREATE SEQUENCE seq_UserID START WITH 1 INCREMENT BY 1
CREATE SEQUENCE seq_UserID START WITH 1 INCREMENT BY 1

3.9. Типы данных (User-Defined Types)​

  • CREATE TYPE PhoneNumber FROM VARCHAR(20)
CREATE TYPE PhoneNumber FROM VARCHAR(20)
  • CREATE TYPE UserTableType AS TABLE (...)
CREATE TYPE UserTableType AS TABLE (...)

4. Системные функции T-SQL​

4.1. Строковые​

  • LEN(), DATALENGTH()
  • SUBSTRING(), LEFT(), RIGHT()
  • REPLACE(), STUFF()
  • TRIM(), LTRIM(), RTRIM()
  • UPPER(), LOWER()
  • CONCAT(), FORMAT()
  • STRING_AGG() (SQL Server 2017+)
  • STRING_SPLIT() (возвращает таблицу)

4.2. Числовые​

  • ABS(), CEILING(), FLOOR()
  • ROUND(), POWER(), SQRT()
  • RAND(), CHECKSUM()
  • ISNULL(), COALESCE()

4.3. Дата и время​

  • GETDATE(), GETUTCDATE()
  • SYSDATETIME(), SYSUTCDATETIME()
  • DATEADD(), DATEDIFF(), DATEDIFF_BIG()
  • DATENAME(), DATEPART()
  • EOMONTH(), ISDATE()

4.4. Системные​

  • @@VERSION, @@SERVERNAME, @@SERVICENAME
  • @@ROWCOUNT, @@ERROR, @@IDENTITY, SCOPE_IDENTITY()
  • USER_NAME(), SUSER_SNAME(), IS_MEMBER()
  • OBJECT_ID(), OBJECT_NAME()
  • HAS_PERMS_BY_NAME()

4.5. JSON (SQL Server 2016+)​

  • JSON_VALUE(), JSON_QUERY()
  • ISJSON(), JSON_MODIFY()
  • FOR JSON PATH/AUTO

4.6. XML​

  • value(), query(), exist(), modify(), nodes()

Базис: Операторы — оператор, операнд и приоритет в коде.

5. Операторы и конструкции T-SQL​

5.1. DDL​

  • CREATE, ALTER, DROP
  • TRUNCATE TABLE
TRUNCATE TABLE
  • RENAME — через sp_rename

5.2. DML​

  • SELECT, INSERT, UPDATE, DELETE, MERGE
  • OUTPUT clause — возвращает изменённые строки
  • TOP (n), OFFSET-FETCH (SQL Server 2012+)

5.3. Управление потоком​

  • IF ... ELSE
  • WHILE
  • BEGIN ... END
BEGIN ... END
  • TRY ... CATCH
  • THROW (SQL Server 2012+)
  • WAITFOR DELAY / TIME

5.4. Курсоры​

  • DECLARE cursor_name CURSOR FOR SELECT ...
  • OPEN, FETCH, CLOSE, DEALLOCATE
  • Использовать только при крайней необходимости

5.5. Динамический SQL​

  • EXEC('SELECT * FROM ' + @table)
EXEC('SELECT * FROM ' + @table)
  • sp_executesql N'SELECT * FROM Users WHERE ID = @id', N'@id INT', @id = 5

6. Безопасность и права​

6.1. Пользователи и логины​

  • на уровне сервера
CREATE LOGIN
  • на уровне БД
CREATE USER
  • ALTER ROLE db_datareader ADD MEMBER user1
ALTER ROLE db_datareader ADD MEMBER user1

6.2. Разрешения​

  • GRANT SELECT ON dbo.Users TO user1
GRANT SELECT ON dbo.Users TO user1
  • DENY DELETE ON SCHEMA::dbo TO user1
  • REVOKE INSERT ON OBJECT::Orders FROM user1
REVOKE INSERT ON OBJECT::Orders FROM user1

6.3. Схемы​

  • CREATE SCHEMA reporting
CREATE SCHEMA reporting
  • По умолчанию: dbo
  • Объекты: schema.object

6.4. Аудит​

  • SQL Server Audit (на уровне экземпляра или БД)
  • Журналы в Windows Event Log или файл

7. Конфигурация SQL Server​

7.1. sp_configure​

  • max server memory (MB)
  • min server memory (MB)
  • max degree of parallelism (MAXDOP)
  • cost threshold for parallelism
  • remote query timeout
  • default trace enabled
  • clr enabled — для CLR-интеграции

Пример:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory', 8192;
RECONFIGURE;

7.2. Database Scoped Configuration (SQL Server 2016+)​

  • ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;
  • LEGACY_CARDINALITY_ESTIMATION = ON
  • PARAMETER_SNIFFING = OFF
  • QUERY_STORE = ON

7.3. Query Store​

  • Хранит планы выполнения и статистику запросов
  • Включается
ALTER DATABASE MyDB SET QUERY_STORE = ON;
  • Полезен для анализа регрессий производительности

8. Производительность и мониторинг​

8.1. Динамические административные представления (DMVs)​

  • sys.dm_exec_requests — активные запросы
  • sys.dm_exec_sessions — сессии
  • sys.dm_os_wait_stats — статистика ожиданий
  • sys.dm_db_index_usage_stats — использование индексов
  • sys.dm_exec_query_stats — статистика по запросам
  • sys.dm_io_virtual_file_stats — I/O по файлам

8.2. Extended Events​

  • Лёгкая замена SQL Profiler
  • Создаются через SSMS или T-SQL
  • События — sql_statement_completed, rpc_completed, wait_info

8.3. Планы выполнения​

  • Actual vs Estimated Execution Plan
  • Операторы — Index Seek, Scan, Nested Loops, Hash Match, Sort
  • Warnings — Missing Index, Implicit Conversion, Spill to TempDB

9. Резервное копирование и восстановление​

9.1. Типы резервных копий​

  • Full — полная копия
  • Differential — изменения с момента последнего Full
  • Transaction Log — только в модели восстановления Full или Bulk-Logged

9.2. Команды​

BACKUP DATABASE MyDB TO DISK = 'C:\Backups\MyDB.bak';
BACKUP LOG MyDB TO DISK = 'C:\Backups\MyDB.trn';
RESTORE DATABASE MyDB FROM DISK = 'C:\Backups\MyDB.bak' WITH REPLACE;

9.3. Модели восстановления​

  • Simple — лог усекается автоматически
  • Full — поддержка point-in-time recovery
  • Bulk-Logged — оптимизация массовых операций

10. SSMS — ключевые возможности​

10.1. Горячие клавиши​

  • Ctrl + E — выполнить запрос
  • Ctrl + L — показать план выполнения
  • Ctrl + R — скрыть/показать результаты
  • Alt + F1 — sp_help для выделенного объекта

10.2. Шаблоны (Template Explorer)​

  • Ctrl + Alt + T — открыть
  • Готовые шаблоны — создание таблицы, процедуры, индекса

10.3. Настройки​

  • Tools → Options → Text Editor → Transact-SQL
    • IntelliSense
    • Auto list members
    • Parameter information
  • Results to Grid / Text / File

10.4. Activity Monitor​

  • Процессы, ожидания, ресурсы, дорогостоящие запросы

11. Агрегатные и оконные функции​

11.1. Стандартные агрегатные функции​

  • COUNT(), COUNT_BIG()
  • SUM(), AVG()
  • MIN(), MAX()
  • STDEV(), VAR()
  • CHECKSUM_AGG()
  • GROUPING(), GROUPING_ID() — для ROLLUP/CUBE

11.2. Оконные функции (SQL Server 2012+)​

Синтаксис:

function OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression [ROWS|RANGE frame_clause]]
)

Ранжирование​

  • ROW_NUMBER()
  • RANK()
  • DENSE_RANK()
  • NTILE(n)

Агрегация по окну​

  • SUM(Sales) OVER (PARTITION BY Region ORDER BY Month)
  • AVG(Price) OVER (ORDER BY Date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

Смещение​

  • LAG(column, offset, default) — предыдущая строка
  • LEAD(column, offset, default) — следующая строка
  • FIRST_VALUE(), LAST_VALUE()

Статистика​

  • PERCENT_RANK()
  • CUME_DIST()
  • PERCENTILE_CONT(), PERCENTILE_DISC()

12. Временные структуры данных​

12.1. Локальные временные таблицы​

  • Префикс: #
  • Жизнь: до конца сессии или явного DROP
  • Хранятся в tempdb
  • Поддерживают индексы, ограничения, статистику
CREATE TABLE #TempUsers (ID INT, Name NVARCHAR(100));
INSERT INTO #TempUsers VALUES (1, 'Alice');

12.2. Глобальные временные таблицы​

  • Префикс: ##
  • Доступны всем сессиям
  • Удаляются, когда последняя сессия завершает работу с ними

12.3. Табличные переменные​

  • Объявляются как DECLARE @t TABLE (...)
  • Не имеют статистики (планы могут быть неточными)
  • Хранятся в памяти при малом объёме, иначе — в tempdb
  • Не поддерживают DDL после объявления

12.4. Табличные выражения​

CTE (Common Table Expression)​

  • Временный именованный результат внутри одного запроса
  • Рекурсивные CTE поддерживают иерархические запросы
WITH EmpCTE AS (
    SELECT ID, Name, ManagerID, 0 AS Level
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ID, e.Name, e.ManagerID, c.Level + 1
    FROM Employees e
    INNER JOIN EmpCTE c ON e.ManagerID = c.ID
)
SELECT * FROM EmpCTE;

Производные таблицы​

  • Подзапрос в FROM
SELECT * FROM (SELECT ...) AS d

13. Транзакции и изоляция​

13.1. Уровни изоляции​

  • READ UNCOMMITTED — разрешает "грязное" чтение
  • READ COMMITTED — по умолчанию; читает только зафиксированные данные
  • REPEATABLE READ — блокирует строки до конца транзакции
  • SERIALIZABLE — блокирует диапазоны, предотвращает фантомные чтения
  • SNAPSHOT — использует версионирование строк в tempdb

Включение:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

13.2. Управление транзакциями​

  • BEGIN TRANSACTION
BEGIN TRANSACTION
  • COMMIT TRANSACTION
COMMIT TRANSACTION
  • ROLLBACK TRANSACTION
ROLLBACK TRANSACTION
  • SAVE TRANSACTION savepoint_name — точка сохранения

13.3. Блокировки и мёртвые блокировки​

  • Типы блокировок — S (shared), X (exclusive), U (update), IX, IS, Sch-S, Sch-M
  • sp_who2, sys.dm_tran_locks — диагностика
  • Приоритет при взаимоблокировках:
SET DEADLOCK_PRIORITY HIGH;   -- текущая сессия "побеждает"
SET DEADLOCK_PRIORITY LOW;
SET DEADLOCK_PRIORITY NORMAL;

14. Расширенные возможности SQL Server​

14.1. Full-Text Search​

  • Индексация текста для семантического поиска
  • Функции — CONTAINS(), FREETEXT(), CONTAINSTABLE(), FREETEXTTABLE()
  • Создание
CREATE FULLTEXT INDEX ON Articles(Content) KEY INDEX PK_Articles

14.2. Service Broker​

  • Встроенная система асинхронной очередной обработки
  • Объекты — MESSAGE TYPE, CONTRACT, QUEUE, SERVICE
  • Используется для триггерных уведомлений, фоновых задач

14.3. CLR Integration​

  • Выполнение управляемого кода (.NET) внутри SQL Server
  • Требует или подписанные сборки
ALTER DATABASE MyDB SET TRUSTWORTHY ON
  • Объекты — скалярные функции, хранимые процедуры, триггеры на C#/VB.NET

14.4. PolyBase (SQL Server 2016+)​

  • Запросы к внешним данным — Hadoop, Azure Blob Storage, Oracle, Teradata
  • Создание внешних источников, форматов, таблиц

14.5. Machine Learning Services (SQL Server 2017+)​

  • Выполнение скриптов Python/R внутри SQL Server
  • Функция: sp_execute_external_script
  • Требует отдельной установки компонента

14.6. Graph Database (SQL Server 2017+)​

  • Таблицы с атрибутами AS NODE и AS EDGE
  • Специальный синтаксис: MATCH (a)-[e]->(b)

15. Администрирование и обслуживание​

15.1. Системные представления​

  • sys.databases — список БД
  • sys.tables, sys.views, sys.procedures
  • sys.columns, sys.indexes, sys.foreign_keys
  • sys.sql_modules — текст определений процедур/функций
  • sys.configurations — параметры сервера

15.2. Хранимые процедуры управления​

  • sp_help — метаданные объекта
  • sp_who, sp_who2 — активные процессы
  • sp_configure — настройка сервера
  • sp_rename — переименование объекта
  • sp_spaceused — использование пространства
  • sp_updatestats — обновление статистики
  • sp_recompile — сброс плана выполнения

15.3. Обслуживание индексов​

  • полная перестройка
ALTER INDEX ... REBUILD
  • дефрагментация
ALTER INDEX ... REORGANIZE
  • Автоматизация через Maintenance Plans или SQL Agent Jobs

15.4. SQL Server Agent​

  • Планировщик заданий
  • Шаги — T-SQL, PowerShell, CmdExec, SSIS
  • Уведомления: почта, события Windows
  • Прокси-учётные записи для безопасного выполнения

16. Развертывание и совместимость​

16.1. Версии SQL Server​

  • Express — бесплатная, ограничена — 10 ГБ БД, 1 сокет, 1.4 ГБ RAM
  • Developer — полная функциональность, только для разработки
  • Standard, Enterprise — для production
  • Web, Business Intelligence — специализированные редакции

16.2. Совместимость между версиями​

  • База данных можно восстановить только на равной или более новой версии
  • Нельзя восстановить резервную копию SQL Server 2022 на SQL Server 2019
  • Уровень совместимости БД: (150 = SQL Server 2019)
ALTER DATABASE MyDB SET COMPATIBILITY_LEVEL = 150;

16.3. Миграция​

  • Generate Scripts в SSMS (с данными или без)
  • Import and Export Wizard (через SSIS)
  • BACPAC — портативный файл схемы и данных (через SqlPackage.exe)
  • Transactional Replication, Always On AG, Log Shipping

17. Безопасность — углублённо​

17.1. Always Encrypted​

  • Шифрование данных на клиенте
  • Ключи: Column Master Key (CMK), Column Encryption Key (CEK)
  • Поддерживается в .NET через SqlParameter

17.2. Dynamic Data Masking​

  • Маскировка данных для неавторизованных пользователей
  • Типы масок — default, partial, random, email
  • Пример:
  ALTER TABLE Users ALTER COLUMN SSN ADD MASKED WITH (FUNCTION = 'default()');

17.3. Row-Level Security (RLS)​

  • Фильтрация строк на уровне запроса
  • Создание функции безопасности и привязка политики
CREATE FUNCTION fn_securitypredicate(@TenantId INT)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN SELECT 1 AS access WHERE @TenantId = USER_ID();

CREATE БЕЗОПАСНОСТЬ POLICY TenantPolicy
ADD FILTER PREDICATE fn_securitypredicate(TenantId) ON dbo.Orders;

17.4. Transparent Data Encryption (TDE)​

  • Шифрование файлов БД на диске
  • Защищает от кражи файлов
  • Требует сертификат базы данных

18. Диагностика и устранение неполадок​

18.1. ERRORLOG​

  • Расположение: Program Files\Microsoft SQL Server\MSSQLXX.MSSQLSERVER\MSSQL\Log\ERRORLOG
  • Просмотр
EXEC xp_readerrorlog

18.2. Default Trace​

  • Включён по умолчанию
  • События — создание/удаление БД, вход в систему, ошибки
  • Путь
SELECT path FROM sys.traces WHERE is_default = 1

18.3. DBCC команды​

  • DBCC CHECKDB — проверка целостности
  • DBCC SQLPERF(LOGSPACE) — использование лога
  • DBCC INPUTBUFFER(spid) — последняя команда сессии
  • DBCC OPENTRAN — активные транзакции
  • DBCC FREEPROCCACHE — очистка кэша планов

19. Работа с JSON​

19.1. Проверка и извлечение​

  • ISJSON(expression) — возвращает 1, если строка корректный JSON
  • JSON_VALUE(json, path) — извлекает скалярное значение
    Пример: JSON_VALUE('{"user": {"name": "Alice"}}', '$.user.name') → 'Alice'
  • JSON_QUERY(json, path) — извлекает объект или массив
    Пример — JSON_QUERY('{"tags" — ["a","b"]}', '$.tags') → ["a","b"]

19.2. Модификация​

  • JSON_MODIFY(json, path, newValue)
    Пример:
  DECLARE @j NVARCHAR(MAX) = '{"name": "Bob"}';
  SET @j = JSON_MODIFY(@j, '$.age', 30);
  -- Результат: {"name": "Bob", "age": 30}

19.3. Преобразование таблицы в JSON​

  • FOR JSON PATH — гибкое формирование структуры
  SELECT ID, Name FROM Users FOR JSON PATH;
  -- [{"ID":1,"Name":"Alice"},{"ID":2,"Name":"Bob"}]
  • FOR JSON AUTO — автоматическая вложенность по JOIN
  • Опции — ROOT('users'), WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES

19.4. Преобразование JSON в таблицу​

  • OPENJSON(json) — табличная функция
  SELECT * FROM OPENJSON('[{"id":1,"name":"Alice"}]')
  WITH (ID INT '$.id', Name NVARCHAR(50) '$.name');

20. Работа с XML​

20.1. Тип данных XML​

  • Может быть нетипизированным или привязанным к XSD-схеме (XML(SCHEMA COLLECTION ...))
  • Поддерживает методы: .value(), .query(), .exist(), .modify(), .nodes()

20.2. Методы XML​

  • .value(xpath, sql_type) — извлекает скаляр
  DECLARE @x XML = '<user><name>Alice</name></user>';
  SELECT @x.value('(/user/name)[1]', 'NVARCHAR(50)');
  • .query(xpath) — возвращает XML-фрагмент
  • .exist(xpath) — проверяет наличие узла (1/0)
  • .modify(xml_dml) — изменяет XML на месте
  SET @x.modify('insert <age>30</age> as last into (/user)[1]');
  • .nodes(xpath) — разворачивает XML в набор строк для JOIN

20.3. FOR XML​

  • FOR XML RAW — каждая строка как элемент <row>
  • FOR XML AUTO — иерархия по именам таблиц
  • FOR XML EXPLICIT — полный контроль через метаданные
  • FOR XML PATH('User') — гибкое формирование с путями
  SELECT ID AS 'ID', Name AS 'Info/FullName'
  FROM Users
  FOR XML PATH('User'), ROOT('Users');

21. Производительность — практические рекомендации​

21.1. Индексация​

  • Избегать избыточных индексов — они замедляют INSERT/UPDATE/DELETE
  • Использовать Included Columns вместо расширения ключа
  • Регулярно обновлять статистику
UPDATE STATISTICS dbo.Users
  • Следить за Key Lookups в планах — часто решаются добавлением колонок в INCLUDE

21.2. Параметризация​

  • Всегда использовать параметризованные запросы (sp_executesql)
  • Избегать конкатенации строк — предотвращает SQL-инъекции и улучшает переиспользование планов

21.3. Антипаттерны​

  • извлекает ненужные данные
SELECT *
  • Функции в WHERE над столбцами: WHERE YEAR(OrderDate) = 2025 → нарушает sargability
    Лучше: WHERE OrderDate >= '20250101' AND OrderDate < '20260101'
  • Курсоры вместо множественных операций
  • Вложенные представления без анализа плана

21.4. TempDB​

  • Размещать на быстрых дисках (SSD/NVMe)
  • Предварительно задавать размер файлов, избегая автоувеличения
  • Количество файлов данных: 1 на CPU core до 8, затем 1 на 4–8 ядер

22. Особенности редакций SQL Server​

22.1. Developer Edition​

  • Полностью совпадает с Enterprise по функционалу
  • Лицензируется бесплатно для разработки и тестирования
  • Не допускается использование в production
  • Идеален для локальной разработки, обучения, демонстраций

22.2. Express Edition​

  • Бесплатен для любых сценариев
  • Ограничения:
    • Максимум 10 ГБ на одну БД (только данные, не журнал)
    • Один сокет процессора
    • До 1.4 ГБ RAM для буферного пула
    • Нет SQL Agent (можно эмулировать через Windows Task Scheduler + sqlcmd)
    • Нет SSIS, SSAS, SSRS
  • Подходит для небольших приложений, встраиваемых решений

22.3. Сравнение возможностей​

ФункцияExpressDeveloperStandardEnterprise
In-Memory OLTP❌✅✅ (ограничено)✅
Columnstore Indexes❌✅✅ (ограничено)✅
Partitioning❌✅✅✅
Always On AG❌✅✅ (2 реплики)✅ (9 реплик)
TDE❌✅✅✅
RLS / DDM✅✅✅✅

23. Автоматизация и командная строка​

23.1. sqlcmd​

  • Утилита командной строки для выполнения T-SQL
  • Пример:
  sqlcmd -S .\SQLEXPRESS -d MyDB -Q "SELECT COUNT(*) FROM Users"
  • Поддержка входных файлов: -i script.sql
  • Переменные: -v TableName="Users"

23.2. PowerShell и SqlServer модуль​

  • Установка: Install-Module -Name SqlServer
  • Командлеты:
    • Invoke-Sqlcmd
    • Get-SqlDatabase
    • Backup-SqlDatabase
    • Restore-SqlDatabase
  • Пример:
  Invoke-Sqlcmd -ServerInstance ".\SQLEXPRESS" -Database "MyDB" -Query "SELECT * FROM Logs"

23.3. BACPAC и DACPAC​

  • DACPAC — схема базы данных (через SSDT)
  • BACPAC — схема + данные
  • Утилита: SqlPackage.exe
  SqlPackage.exe /Action:Export /SourceServer:. /SourceDatabase:MyDB /TargetFile:MyDB.bacpac

24. Проектирование и стиль​

24.1. Именование​

  • Схемы — dbo, reporting, staging
  • Таблицы — Users, OrderItems (PascalCase, множественное число)
  • Колонки — UserID, CreatedDate, IsActive
  • Процедуры: usp_GetActiveUsers, usp_UpdateOrderStatus
  • Избегать зарезервированных слов и пробелов

24.2. Нормализация​

  • 1NF: атомарные значения
  • 2NF: отсутствие частичных зависимостей от составного ключа
  • 3NF: отсутствие транзитивных зависимостей
  • Денормализация допустима ради производительности в аналитических системах

24.3. Шаблоны проектирования​

  • Soft Delete: колонка IsDeleted BIT NOT NULL DEFAULT 0
  • Audit Trail: триггер или CDC для логирования изменений
  • Slowly Changing Dimensions (SCD) — тип 2: новая запись при изменении атрибута
  • Lookup Tables: справочники с кодами и описаниями

25. Распространённые задачи — готовые решения​

25.1. Поиск по всем таблицам​

DECLARE @SearchStr NVARCHAR(100) = 'Alice';
DECLARE @TableName NVARCHAR(256), @ColumnName NVARCHAR(128);

DECLARE cur CURSOR FOR
SELECT t.name, c.name
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.system_type_id IN (167, 175, 231, 239); -- строковые типы

OPEN cur;
FETCH NEXT FROM cur INTO @TableName, @ColumnName;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC('IF EXISTS(SELECT 1 FROM [' + @TableName + '] WHERE [' + @ColumnName + '] LIKE ''%' + @SearchStr + '%'')
          PRINT ''' + @TableName + '.' + @ColumnName + '''');
    FETCH NEXT FROM cur INTO @TableName, @ColumnName;
END
CLOSE cur; DEALLOCATE cur;

25.2. Получение размера таблиц​

SELECT
    t.name AS TableName,
    s.name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
GROUP BY t.name, s.name, p.rows
ORDER BY TotalSpaceKB DESC;

25.3. Поиск блокировок​

SELECT
    blocking.session_id AS BlockingSession,
    blocked.session_id AS BlockedSession,
    blocked.wait_time,
    blocked.wait_type,
    blocked.wait_resource,
    sqltext.text AS BlockedQuery
FROM sys.dm_exec_requests blocked
JOIN sys.dm_exec_sessions blocking
    ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) sqltext;

26. Сравнение T-SQL с другими диалектами SQL​

26.1. Отличия от стандартного SQL​

  • TOP вместо LIMIT (как в MySQL/PostgreSQL)
  • IDENTITY вместо SERIAL или AUTO_INCREMENT
  • GETDATE() вместо CURRENT_TIMESTAMP (хотя последний тоже поддерживается)
  • ISNULL() вместо COALESCE() (но COALESCE тоже есть и является стандартным)
  • + для конкатенации строк (вместо ||)

26.2. Отличия от PostgreSQL​

  • В PostgreSQL — массивы, JSONB, расширенные оконные функции, CTE с модифицирующими запросами (INSERT ... RETURNING)
  • В SQL Server — PIVOT/UNPIVOT, OUTPUT clause, MERGE, интеграция с Windows, SSIS/SSRS

26.3. Отличия от MySQL​

  • MySQL — AUTO_INCREMENT, ENGINE=InnoDB, GROUP BY с неагрегированными колонками (в старых версиях), LIMIT
  • SQL Server — строгая проверка GROUP BY, обязательное указание схемы при необходимости, отсутствие движков хранения

26.4. Совместимость через ANSI-режим​

  • SQL Server поддерживает большинство ANSI SQL-92/99 конструкций
  • Для переносимости: избегать TOP, использовать ROW_NUMBER() + подзапрос вместо LIMIT

27. Миграция с других СУБД​

27.1. С MySQL​

  • Замена AUTO_INCREMENT → IDENTITY
  • DATETIME в MySQL поддерживает микросекунды — в SQL Server использовать DATETIME2(6)
  • VARCHAR без указания длины в MySQL = TEXT — в SQL Server обязательно указывать длину
  • Экранирование: обратные кавычки ` → квадратные скобки [ ] или двойные кавычки (если QUOTED_IDENTIFIER ON)

27.2. С PostgreSQL​

  • Замена SERIAL → IDENTITY
  • JSONB → NVARCHAR(MAX) + ISJSON() или XML для структурированных данных
  • Массивы → таблицы с внешним ключом или STRING_SPLIT() при хранении как строка
  • Функции: переписать PL/pgSQL на T-SQL или CLR

27.3. Инструменты миграции​

  • SQL Server Migration Assistant (SSMA) — официальный инструмент Microsoft
    • Поддерживает Oracle, MySQL, PostgreSQL, Access, Sybase
    • Конвертирует схему, данные, процедуры
  • Azure Data Studio + расширения — для анализа совместимости

28. In-Memory OLTP (Hekaton)​

28.1. Назначение​

  • Высокопроизводительная обработка транзакций в памяти
  • Устранение блокировок и логгирования на уровне строк

28.2. Требования​

  • Таблица должна быть создана с MEMORY_OPTIMIZED = ON
  • Обязательно наличие по крайней мере одного некластеризованного хэш-индекса или диапазонного индекса
  • Все столбцы фиксированной длины или LOB вне строки

28.3. Создание​

CREATE TABLE dbo.MemoryUsers (
    UserID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000),
    Name NVARCHAR(100) NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

28.4. Ограничения​

  • Нет поддержки FOREIGN KEY, CHECK, IDENTITY, TRIGGER
  • Нельзя использовать ALTER TABLE — только DROP и CREATE
  • Максимальный размер базы в памяти ограничен редакцией (Express — 320 МБ)

28.5. Процедуры​

  • Natively compiled stored procedures — компилируются в машинный код
  • Объявляются с
WITH NATIVE_COMPILATION, SCHEMABINDING
  • Только обращение к memory-optimized таблицам

29. Резервное копирование в облако​

29.1. Azure Blob Storage​

  • URL-адрес резервной копии: https://mystorage.blob.core.windows.net/mycontainer/backup.bak
  • Требуется Shared Access Signature (SAS) или Storage Account Key
  • Команда:
  BACKUP DATABASE MyDB
  TO URL = 'https://mystorage.blob.core.windows.net/backups/MyDB.bak'
  WITH CREDENTIAL = 'MyAzureCredential';

29.2. AWS S3​

  • Через промежуточный сервер или PowerShell-скрипты
  • Использование sqlcmd + aws s3 cp после локального бэкапа

29.3. Шифрование резервных копий​

  • BACKUP ... WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = MyCert)
  • Требует предварительно созданного сертификата

30. Продвинутый мониторинг​

30.1. Query Store — анализ регрессий​

  • Включить:
  ALTER DATABASE MyDB SET QUERY_STORE = ON;
  • Просмотр планов:
  SELECT q.query_id, p.plan_id, rs.count_executions, rs.avg_duration
  FROM sys.query_store_query q
  JOIN sys.query_store_plan p ON q.query_id = p.query_id
  JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id;
  • Принудительное использование плана:
  EXEC sp_query_store_force_plan @query_id = 10, @plan_id = 2;

30.2. Extended Events — лёгкий профилинг​

  • Создание сессии:
  CREATE EVENT SESSION [LongQueries] ON SERVER
  ADD EVENT sqlserver.sql_statement_completed(
      ACTION(sqlserver.sql_text)
      WHERE duration > 1000000 -- 1 секунда
  )
  ADD TARGET package0.ring_buffer;
  • Запуск
ALTER EVENT SESSION [LongQueries] ON SERVER STATE = START;

30.3. Мониторинг TempDB​

  • Использование:
  SELECT 
      name,
      size * 8 / 1024 AS SizeMB,
      max_size,
      growth
  FROM tempdb.sys.database_files;
  • Активность:
  SELECT 
      session_id,
      user_objects_alloc_page_count,
      internal_objects_alloc_page_count
  FROM sys.dm_db_session_space_usage
  WHERE user_objects_alloc_page_count > 0;

30.4. Wait Statistics​

  • Ключевые ожидания:
    • PAGEIOLATCH_* — медленный диск
    • LCK_M_* — блокировки
    • CXPACKET — параллелизм (не всегда плохо)
    • WRITELOG — медленная запись журнала
  • Сброс статистики: DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

31. Настройка автозапуска и служб​

31.1. Службы SQL Server​

  • SQL Server (MSSQLSERVER) — ядро СУБД
  • SQL Server Agent (MSSQLSERVER) — планировщик
  • SQL Server Browser — для именованных экземпляров
  • Управление: services.msc или PowerShell (Start-Service, Stop-Service)

31.2. Автозапуск​

  • По умолчанию: Automatic
  • Для Express Edition без Agent — задачи через Windows Task Scheduler:
  sqlcmd -S .\SQLEXPRESS -d MyDB -i "C:\Scripts\DailyJob.sql"

31.3. Запуск от учётной записи​

  • Рекомендуется использовать Managed Service Account (MSA) или gMSA в домене
  • Избегать запуска от Local System в production
Содержание