Практический курс по Microsoft SQL Server (MSSQL): основы и работа с данными

Введение в MSSQL и установка SQL Server и SSMS

Что такое MSSQL и из чего он состоит

Microsoft SQL Server (MSSQL) — это система управления реляционными базами данных (СУБД). Она хранит данные в таблицах, помогает безопасно управлять доступом и позволяет получать данные с помощью языка SQL.

В реальной работе под MSSQL обычно подразумевают сразу несколько компонентов:

SQL Server Database Engine — основной серверный движок: хранит данные, выполняет запросы, управляет транзакциями и безопасностью.

SQL Server Management Studio (SSMS) — графическая программа для администрирования и работы с запросами.

SQL Server Configuration Manager — утилита для настройки сетевых протоколов и служб SQL Server (на Windows).

!Как связаны SSMS, движок SQL Server и базы данных

Где применяется SQL Server

Бизнес-приложения (учет, CRM/ERP, банковские системы)

Аналитика и отчеты (витрины данных, BI-сценарии)

Интеграции (обмен данными между системами)

Хранение данных для сайтов и сервисов (особенно в экосистеме Microsoft)

Что мы будем делать в курсе

Чтобы уверенно работать с данными, в курсе мы последовательно пройдем путь:

Установка и проверка окружения (эта статья).

Основы SQL: выборка данных SELECT, фильтрация, сортировка.

Изменение данных: INSERT, UPDATE, DELETE.

Проектирование таблиц: типы данных, ключи, связи.

Соединения таблиц: JOIN.

Агрегации: GROUP BY, агрегатные функции.

Индексы и базовая производительность.

Пользователи, роли, права доступа.

Резервные копии и базовые операции администрирования.

Выбор редакции SQL Server для обучения

Для обучения лучше всего подходит бесплатная редакция Developer: функционально она близка к Enterprise (много возможностей), но лицензия разрешает только разработку и тестирование, не продакшен.

| Редакция | Стоимость | Подходит для обучения | Ключевая идея | |---|---:|---:|---| | Developer | Бесплатно | Да | Максимум возможностей для разработки/тестов | | Express | Бесплатно | Да (с ограничениями) | Упрощенная редакция с лимитами | | Standard / Enterprise | Платно | Обычно нет | Продакшен-лицензии и функции уровня бизнеса |

Официальная загрузка SQL Server: Загрузки Microsoft SQL Server

Что такое экземпляр (instance)

При установке SQL Server вы создаете экземпляр — отдельную установку SQL Server на компьютере со своими настройками.

Default instance — экземпляр по умолчанию. Обычно подключение выглядит проще.

Named instance — экземпляр с именем, например DESKTOP-PC\SQLEXPRESS.

Для первого шага чаще всего удобно оставить default instance, если вы ставите SQL Server впервые на этот компьютер.

Подготовка к установке

Системные требования

Перед установкой проверьте поддерживаемую ОС и общие требования:

Требования к оборудованию и ПО для SQL Server

Важно про права и режимы

Для установки на Windows обычно нужны права администратора.

Также заранее решите, какой режим аутентификации использовать:

Windows Authentication — вход в SQL Server с учетной записью Windows. Это безопаснее и проще для старта.

SQL Server Authentication (Mixed Mode) — дополнительно разрешает логины SQL Server (например, sa). Нужно, если вы хотите подключаться не только через Windows-учетки.

Для обучения чаще выбирают Mixed Mode, но важно задать сильный пароль для sa.

Установка SQL Server (Windows)

Ниже — практичный вариант установки SQL Server Developer на локальный компьютер.

Шаг 1. Скачайте установщик

Откройте страницу Загрузки Microsoft SQL Server.

В блоке Developer нажмите Download now.

Шаг 2. Запустите установку

Запустите скачанный установщик.

Выберите вариант установки:

1. Basic — проще и быстрее, минимум настроек. 2. Custom — позволяет выбрать компоненты и важные параметры.

Для обучения полезнее Custom, чтобы понимать, что именно вы настраиваете.

Шаг 3. Выберите компоненты

Минимально достаточно:

Database Engine Services (обязательно)

Остальные компоненты (например, Analysis Services) для старта не нужны.

Шаг 4. Настройте экземпляр

Выберите Default instance (если это первая установка).

Либо задайте имя для Named instance, если хотите несколько независимых установок.

Шаг 5. Настройте аутентификацию и администратора

В разделе аутентификации выберите один из вариантов:

1. Windows Authentication. 2. Mixed Mode.

Если выбран Mixed Mode, задайте пароль для sa.

Добавьте текущего пользователя Windows в SQL Server administrators.

Шаг 6. Завершите установку

Нажмите Install.

Дождитесь завершения.

Запомните имя сервера/экземпляра (оно понадобится в SSMS).

Официальная инструкция: Установка SQL Server из Installation Wizard (Windows)

Установка SSMS

SSMS ставится отдельно от SQL Server.

Откройте страницу Скачивание SQL Server Management Studio (SSMS).

Скачайте последнюю версию.

Установите как обычную программу.

Первое подключение в SSMS и проверка работы

Подключение

Откройте SSMS.

В окне подключения:

1. Server type: Database Engine. 2. Server name: 1. для default instance часто подходит имя компьютера или localhost. 2. для named instance — ИМЯ_КОМПЬЮТЕРА\ИМЯ_ЭКЗЕМПЛЯРА. 3. Authentication: 1. Windows Authentication (если выбрали Windows-only). 2. SQL Server Authentication (если включили Mixed Mode).

Нажмите Connect.

Проверочный запрос

Откройте новое окно запроса и выполните:

SELECT — команда выборки.

@@VERSION — системная функция, возвращает информацию о версии SQL Server.

Если вы видите результат, значит сервер установлен и запросы выполняются.

Создание первой базы данных (минимальная практика)

В SSMS выполните:

CREATE DATABASE — создает базу данных.

sys.databases — системный каталог со списком баз.

WHERE — фильтрует строки по условию.

Если запрос вернул строку CourseDB, база создана.

Частые проблемы и быстрые решения

Не получается подключиться к серверу

Проверьте по шагам:

Установлена ли служба SQL Server и запущена ли она:

1. Откройте SQL Server Configuration Manager. 2. Найдите службу вида SQL Server (MSSQLSERVER) или SQL Server (ИмяЭкземпляра). 3. Статус должен быть Running.

Верно ли указано имя сервера/экземпляра:

1. Для named instance не забудьте \.

Верно ли выбран режим аутентификации:

1. Если включен только Windows Authentication, логин/пароль SQL Server не сработают.

Нужны сетевые подключения (не только локально)

Для удаленных подключений обычно требуется:

Включить протокол TCP/IP в SQL Server Configuration Manager.

Убедиться, что порт доступен (часто используется 1433).

На старте курса можно работать локально; к сетевым подключениям вернемся, когда появится практическая необходимость.

Что запомнить из этой статьи

MSSQL — это SQL Server Database Engine (сервер), а SSMS — удобный инструмент для подключения и работы.

Для обучения лучше всего ставить SQL Server Developer и SSMS.

После установки важно уметь:

- подключиться в SSMS, - выполнить простой запрос, - создать базу данных.

В следующей статье начнем писать базовые запросы SELECT и разберем, как читать результаты и работать с простыми выборками.

Основы SQL: SELECT, WHERE, ORDER BY, JOIN

В прошлой статье вы установили SQL Server и SSMS, проверили работу запросов и создали базу CourseDB. Теперь переходим к главному навыку работы с данными: получать нужные строки и столбцы с помощью SELECT, отбирать их через WHERE, упорядочивать через ORDER BY и объединять данные из нескольких таблиц через JOIN.

В этой статье мы будем писать запросы в стиле T-SQL (диалект SQL в Microsoft SQL Server).

Подготовим учебные данные

Чтобы практиковаться, создадим небольшой набор таблиц с клиентами и заказами. Это временная учебная схема: глубже проектирование таблиц, типы данных и ключи мы разберем в следующих темах курса.

Выберите базу данных:

Создайте таблицы:

Заполните данные:

SELECT: выбираем данные

SELECT возвращает табличный результат: строки и столбцы.

Минимальный SELECT

SELECT перечисляет столбцы, которые вы хотите увидеть.

FROM указывает таблицу-источник.

Выражения и псевдонимы столбцов

Вы можете вычислять значения прямо в запросе и задавать читаемые имена результату.

AS AmountWithTax задает имя вычисленного столбца.

DISTINCT: убрать дубликаты

DISTINCT оставляет только уникальные значения по указанным столбцам.

TOP: ограничить количество строк

TOP полезен при первичной проверке данных.

> Важно: без ORDER BY запрос не обязан возвращать строки в каком-то стабильном порядке, даже если вам кажется, что порядок повторяется.

Официальная документация: SELECT (Transact-SQL)

WHERE: фильтруем строки

WHERE отбирает строки по условию.

Простые сравнения

AND, OR, NOT и скобки

Если условия смешиваются, используйте скобки, чтобы явно задать логику:

IN, BETWEEN, LIKE

IN удобно вместо множества OR.

BETWEEN включает обе границы диапазона.

LIKE использует шаблон, где % означает любую последовательность символов.

NULL: проверяется через IS NULL

NULL означает нет значения. Сравнения вида = NULL не работают.

Официальная документация: WHERE (Transact-SQL)

ORDER BY: сортируем результат

ORDER BY задает порядок строк в результате.

По умолчанию сортировка по возрастанию, то есть ASC.

Сортировка по убыванию и по нескольким полям

Сначала сортирует по Amount по убыванию.

Если суммы одинаковые, сортирует по OrderID по возрастанию.

Официальная документация: ORDER BY (Transact-SQL)

JOIN: объединяем таблицы

JOIN нужен, когда данные логически распределены по нескольким таблицам, и вы хотите получить единый результат.

В нашем наборе данных:

dbo.Customers хранит клиентов

dbo.Orders хранит заказы

связь идет через CustomerID

!Связь клиентов и заказов: один клиент может иметь много заказов

INNER JOIN: только совпавшие строки

INNER JOIN вернет только тех клиентов, у которых есть заказы (и только сами заказы).

LEFT JOIN: все строки слева + совпадения справа

Если вы хотите увидеть всех клиентов, даже без заказов:

Для клиентов без заказов поля o.OrderID, o.OrderDate, o.Amount будут NULL.

Частая ошибка с LEFT JOIN: фильтр не там

Задача: показать всех клиентов и только их оплаченные заказы, при этом клиентов без оплаченных заказов тоже оставить.

Неправильно:

Почему плохо: условие в WHERE отфильтрует строки, где o.Status равен NULL, и LEFT JOIN фактически превратится в INNER JOIN.

Правильно переносить условие в ON:

Как читать JOIN в запросе

Практичная последовательность чтения:

FROM и JOIN определяют, какие таблицы участвуют и как они связываются.

ON описывает, по какому условию строки считаются совпавшими.

WHERE фильтрует готовый набор строк после соединений.

SELECT определяет, что именно выводить.

ORDER BY определяет, в каком порядке показать результат.

Официальная документация: JOIN (Transact-SQL)

Практические рекомендации для повседневной работы

Не злоупотребляйте SELECT *.

Всегда добавляйте ORDER BY, если вам важен порядок строк.

Начинайте запрос с простого SELECT из одной таблицы, затем добавляйте WHERE, и только потом JOIN.

Используйте понятные псевдонимы таблиц AS c, AS o, особенно в запросах с несколькими таблицами.

Что запомнить из этой статьи

SELECT ... FROM ... выбирает столбцы из таблицы.

WHERE фильтрует строки по условиям.

ORDER BY задает порядок строк в результате.

JOIN объединяет таблицы по логической связи:

- INNER JOIN возвращает только совпавшие строки - LEFT JOIN сохраняет все строки из левой таблицы

В LEFT JOIN фильтры по правой таблице часто нужно писать в ON, а не в WHERE.

Изменение данных: INSERT, UPDATE, DELETE, MERGE

В прошлой статье вы научились читать данные с помощью SELECT, фильтровать их через WHERE, сортировать через ORDER BY и объединять таблицы через JOIN. Теперь переходим к следующему ключевому навыку: изменять данные.

В SQL Server изменения выполняются командами:

INSERT — добавить строки

UPDATE — изменить существующие строки

DELETE — удалить строки

MERGE — объединить логику вставки и обновления (и при необходимости удаления)

Все эти команды относятся к DML (Data Manipulation Language) и выполняются внутри транзакций.

Подготовка: используем учебную базу и таблицы

Мы продолжим работать с таблицами из прошлой статьи dbo.Customers и dbo.Orders в базе CourseDB.

> Практическое правило: перед любым изменением сначала напишите SELECT с тем же WHERE, чтобы точно понимать, какие строки будут затронуты.

Транзакции: как безопасно менять данные

Транзакция — это способ выполнить несколько изменений как одну логическую операцию: либо применятся все изменения, либо ни одно.

!Диаграмма показывает, что транзакция либо фиксирует все изменения, либо откатывает их

Минимальный шаблон:

Если вы не уверены — используйте ROLLBACK вместо COMMIT:

Более практичный шаблон с обработкой ошибок:

TRY...CATCH позволяет перехватывать ошибки.

ROLLBACK откатывает изменения.

SET XACT_ABORT ON заставляет SQL Server автоматически прерывать транзакцию при многих типах ошибок.

Документация: BEGIN TRANSACTION (Transact-SQL), TRY...CATCH (Transact-SQL), SET XACT_ABORT (Transact-SQL)

INSERT: добавляем новые строки

Вставка одной строки

Список столбцов в INSERT INTO (...) — хорошая привычка: запрос устойчивее к изменениям структуры таблицы.

Документация: INSERT (Transact-SQL)

Вставка нескольких строк

INSERT из результата SELECT

Это один из самых частых приемов в реальной работе: вставлять данные пачкой из запроса.

Создадим таблицу для архива заказов:

Перенесем в архив отмененные заказы:

OUTPUT: увидеть, что именно вставили

Иногда важно получить вставленные значения (например, для логирования):

inserted — виртуальная таблица со строками, которые были вставлены.

Документация: OUTPUT Clause (Transact-SQL)

UPDATE: изменяем существующие строки

Базовый UPDATE

Без WHERE команда обновит все строки таблицы.

Документация: UPDATE (Transact-SQL)

Обновление нескольких столбцов

UPDATE с JOIN: обновление по данным из другой таблицы

JOIN, который вы изучили для чтения данных, так же важен и при изменении.

Пример: пометим все заказы клиентов из Москвы как приоритетные (для примера добавим столбец в Orders).

Теперь обновим заказы клиентов из Москвы:

В T-SQL удобно писать UPDATE o ... FROM ... JOIN ....

Обновляется именно та таблица, которая указана после UPDATE (здесь это o).

OUTPUT в UPDATE: что было до и что стало

deleted — виртуальная таблица со старыми версиями строк.

inserted — виртуальная таблица с новыми версиями строк.

DELETE: удаляем строки

Базовый DELETE

Документация: DELETE (Transact-SQL)

DELETE с JOIN: удаление по условиям из другой таблицы

Например, удалить все заказы клиентов из Самары:

Чем DELETE отличается от TRUNCATE

Иногда путают команды, потому что обе уменьшают данные.

| Команда | Что делает | Можно указать WHERE | Типичный сценарий | |---|---|---|---| | DELETE | удаляет строки | да | выборочное удаление | | TRUNCATE TABLE | быстро очищает всю таблицу | нет | полная очистка staging-таблицы |

Если вы учитесь, лучше сначала закрепить DELETE. TRUNCATE TABLE используйте только когда точно нужно удалить все строки.

Документация: TRUNCATE TABLE (Transact-SQL)

Частая практика: мягкое удаление

В бизнес-системах строки часто не удаляют физически, а помечают как неактуальные (например, IsDeleted = 1). Это упрощает аудит и восстановление.

MERGE: вставка и обновление в одной команде

MERGE помогает реализовать сценарий синхронизации: есть источник данных и есть целевая таблица, и вы хотите:

обновить совпавшие строки

вставить отсутствующие

иногда удалить лишние

Документация: MERGE (Transact-SQL)

Пример: синхронизируем справочник клиентов

Создадим таблицу-источник (как будто пришла выгрузка из внешней системы):

Теперь применим импорт к основной таблице:

``sql MERGE dbo.Customers AS tgt USING dbo.CustomersImport AS src ON src.CustomerID = tgt.CustomerID WHEN MATCHED THEN UPDATE SET tgt.FullName = src.FullName, tgt.City = src.City WHEN NOT MATCHED BY TARGET THEN INSERT (CustomerID, FullName, City) VALUES (src.CustomerID, src.FullName, src.City) OUTPUT action показывает, что произошло с каждой строкой: INSERT, UPDATE (и DELETE, если добавляете ветку удаления).

Важное замечание про MERGE

В реальных проектах к MERGE относятся осторожно: исторически вокруг него было много редких, но неприятных ошибок и гонок при конкурентной записи. Если вы используете MERGE для upsert в высоконагруженных сценариях, часто выбирают альтернативу на базе UPDATE + INSERT (с транзакцией и подходящими блокировками).

Для учебных задач и для понимания идеи синхронизации MERGE полезен, но в продакшене стоит применять его осознанно и тестировать на конкуренцию.

Практические правила безопасности для INSERT/UPDATE/DELETE

Всегда начинайте с SELECT и тем же WHERE, чтобы увидеть набор строк.

Если изменение рискованное, выполняйте его внутри BEGIN TRAN и сначала делайте ROLLBACK.

Не делайте массовый UPDATE или DELETE без WHERE.

Используйте OUTPUT, если нужно проверить, что именно изменилось.

Помните, что JOIN в UPDATE/DELETE влияет на набор затронутых строк так же сильно, как и в SELECT.

Что запомнить из этой статьи

INSERT добавляет строки, в том числе пачкой и через INSERT ... SELECT.

UPDATE меняет строки; без WHERE меняет всю таблицу.

DELETE удаляет строки; без WHERE удаляет все строки таблицы.

OUTPUT помогает увидеть измененные данные прямо во время операции.

MERGE удобен для синхронизации данных, но требует осторожности в реальных конкурентных сценариях.

Транзакции (BEGIN TRAN / COMMIT / ROLLBACK) — основной инструмент безопасных изменений.

Проектирование базы: типы данных, ключи, связи, ограничения

В прошлых статьях вы научились читать данные (SELECT, JOIN) и менять их (INSERT, UPDATE, DELETE, MERGE). Чтобы эти операции были надежными и предсказуемыми, базу нужно правильно спроектировать: выбрать подходящие типы данных, определить ключи, связи между таблицами и ограничения, которые защищают данные от ошибок.

Что такое проектирование базы данных

Проектирование в контексте MSSQL обычно включает:

выбор таблиц и их столбцов

выбор типов данных для каждого столбца

определение ключей (как однозначно идентифицировать строку)

настройку связей между таблицами (например, клиент и его заказы)

добавление ограничений (constraints), чтобы в таблицы не попадали некорректные значения

В итоге база начинает защищать данные сама, а не только за счет аккуратности разработчика.

Типы данных в SQL Server

Тип данных определяет, как SQL Server хранит значение, какие операции доступны и какие значения считаются корректными.

Официальная справка: Типы данных (Transact-SQL)

Основные группы типов данных

| Группа | Частые типы | Когда использовать | |---|---|---| | Целые числа | tinyint, smallint, int, bigint | счетчики, идентификаторы, количества | | Точные числа | decimal(p,s), numeric(p,s) | деньги, суммы, измерения, где важна точность | | Приближенные числа | float, real | научные расчеты, телеметрия, где допустима погрешность | | Строки (Unicode) | nchar, nvarchar | текст на русском и в целом любой многозначный алфавит | | Строки (не Unicode) | char, varchar | текст строго в одной кодировке и без национальных символов | | Дата и время | date, datetime2, time, datetimeoffset | даты заказов, события, временные метки | | Логический | bit | да/нет, флаги | | Уникальные идентификаторы | uniqueidentifier | GUID, распределенные системы | | Двоичные данные | varbinary | файлы, хеши, бинарные payload |

Практические правила выбора типов

Для текста в учебных и бизнес-сценариях почти всегда выбирайте nvarchar, чтобы корректно хранить русские имена и города.

Для денег и сумм используйте decimal(p,s), например decimal(10,2).

Для даты заказа часто достаточно date, а для точной временной метки лучше datetime2.

Для идентификаторов чаще всего используют int.

NULL и NOT NULL

NULL означает значение отсутствует (не равно нулю и не равно пустой строке).

NOT NULL заставляет всегда хранить значение

NULL разрешает отсутствие значения

Практическое правило: делайте столбец NOT NULL, если бизнес-смысл требует, чтобы значение всегда было (например, OrderDate, Amount, имя клиента).

Ключи: как однозначно идентифицировать строку

Ключ помогает однозначно найти строку и предотвратить дубли.

PRIMARY KEY

PRIMARY KEY (первичный ключ) гарантирует:

уникальность значения ключа

запрет NULL в ключевом столбце

В SQL Server первичный ключ обычно реализуется как ограничение, и под него создается индекс.

Документация: PRIMARY KEY (Transact-SQL)

Естественный ключ и суррогатный ключ

Естественный ключ берется из предметной области, например номер паспорта или ИНН.

Суррогатный ключ создается искусственно, обычно это int IDENTITY.

Для обучения и для многих бизнес-таблиц удобно использовать суррогатный ключ.

IDENTITY

IDENTITY автоматически генерирует последовательные значения при INSERT.

Документация: IDENTITY (свойство) (Transact-SQL)

Пример:

UNIQUE

UNIQUE гарантирует уникальность значения в столбце или наборе столбцов, но это не первичный ключ.

Типичный пример: email, который должен быть уникальным.

Документация: UNIQUE (Transact-SQL)

Связи между таблицами

Связи описывают, как строки одной таблицы соотносятся со строками другой.

!ER-схема связи один-ко-многим между клиентами и заказами

Связь один-ко-многим

Это самый частый случай: один клиент может иметь много заказов.

Реализуется через FOREIGN KEY в таблице заказов.

Документация: FOREIGN KEY (Transact-SQL)

Пример:

Что дает FOREIGN KEY:

нельзя вставить заказ на несуществующего клиента

нельзя случайно удалить клиента, если на него ссылаются заказы (если не настроены специальные правила)

Связь многие-ко-многим

Пример: заказ и товары. Один заказ содержит много товаров, и один товар встречается во многих заказах.

В реляционных базах это делается через таблицу-связку.

!Схема многие-ко-многим через таблицу OrderItems

Пример структуры:

Здесь первичный ключ составной: пара (OrderID, ProductID) не может повторяться.

Ограничения: как база защищает данные

Ограничения (constraints) проверяют правила на уровне базы. Это особенно важно, потому что данные могут вставляться из разных приложений и скриптов.

Документация: UNIQUE и CHECK constraints (Transact-SQL)

NOT NULL

Запрещает отсутствие значения.

DEFAULT

Задает значение по умолчанию, если при INSERT не передали столбец.

Практический смысл: если приложение забыло передать дату, база подставит текущую.

CHECK

Проверяет логическое условие.

Пример ограничения для статуса:

UNIQUE

Гарантирует, что значение не повторяется.

Важно: UNIQUE обычно допускает NULL (и то, как именно ведут себя множественные NULL, зависит от конкретных правил и версий, но как практику чаще делают email NOT NULL, если он обязателен).

FOREIGN KEY и правила при удалении

По умолчанию SQL Server запретит удалить строку-родителя, если на нее ссылаются строки-дети.

Иногда поведение меняют:

ON DELETE CASCADE удаляет дочерние строки автоматически

ON DELETE SET NULL ставит NULL в дочернем столбце (если он допускает NULL)

Пример с каскадным удалением:

Практическое правило: каскады удобны, но опасны при ошибках в приложении. В учебных примерах их полезно понимать, а в реальных проектах включать осознанно.

Улучшаем учебную схему из прошлых статей

Ранее мы создавали таблицы Customers и Orders с ручным заполнением CustomerID и без явного FOREIGN KEY. Улучшим модель, добавив типовые элементы проектирования.

Если вы уже создали таблицы из прошлой статьи, для чистоты эксперимента проще пересоздать их (в учебной базе это нормально).

Теперь попробуйте вставлять данные, как раньше, но уже без ручного задания идентификаторов:

И проверьте связь через JOIN, который вы уже изучили:

Практические рекомендации по стилю проектирования

Давайте ограничениям понятные имена: PK_..., FK_..., CK_..., DF_..., UQ_....

Храните даты как date или datetime2, а не как строки.

Не храните деньги в float.

Добавляйте FOREIGN KEY, если в данных есть логическая ссылка на другую таблицу.

Добавляйте CHECK, если есть явное правило допустимых значений.

Что запомнить из этой статьи

Тип данных влияет на корректность хранения и удобство запросов.

PRIMARY KEY однозначно идентифицирует строку.

FOREIGN KEY обеспечивает ссылочную целостность между таблицами.

Связь многие-ко-многим делается через таблицу-связку.

Ограничения NOT NULL, DEFAULT, CHECK, UNIQUE, FOREIGN KEY защищают данные от ошибок еще на уровне базы.

Агрегации и аналитика: GROUP BY, HAVING, оконные функции

В прошлых темах вы научились читать данные (SELECT, WHERE, JOIN), изменять их (INSERT, UPDATE, DELETE) и проектировать таблицы с ключами и ограничениями. Теперь переходим к следующему практическому уровню: анализ данных прямо в SQL Server.

В реальных задачах вас почти всегда интересуют не только строки поштучно, но и итоги:

сколько заказов у клиента

какая сумма продаж по городам

кто входит в топ по выручке

как посчитать накопительный итог по датам

Для этого в T-SQL используются:

агрегатные функции (COUNT, SUM, AVG, MIN, MAX)

группировка GROUP BY

фильтрация групп HAVING

оконные функции OVER (PARTITION BY ... ORDER BY ...)

Подготовка учебных данных

Мы продолжим использовать учебную базу CourseDB и таблицы dbo.Customers, dbo.Orders, которые вы создавали и улучшали в теме про проектирование.

Если данных мало, добавьте несколько заказов для практики:

Агрегатные функции

Агрегатная функция берет набор строк и возвращает одно значение.

Самые используемые:

COUNT(*) считает строки

COUNT(столбец) считает не-NULL значения в столбце

SUM(столбец) сумма

AVG(столбец) среднее

MIN(столбец), MAX(столбец) минимум и максимум

Документация: Агрегатные функции (Transact-SQL)

Пример без группировки: итог по всем заказам

Это один из самых частых приемов: получить одну строку итога.

Важная разница: COUNT(*) и COUNT(столбец)

COUNT(*) считает все строки, независимо от NULL

COUNT(o.Amount) не считает строки, где Amount IS NULL

В нашей модели Amount обычно NOT NULL, но в реальных таблицах разница критична.

GROUP BY: итоги по группам

GROUP BY превращает строки в группы по указанным столбцам, а агрегатные функции считают итоги внутри каждой группы.

Документация: GROUP BY (Transact-SQL)

Продажи по статусам

Здесь каждая строка результата соответствует одному статусу.

Продажи по клиентам

Чтобы вывести имя клиента, добавим JOIN.

Обратите внимание на правило:

если столбец выводится в SELECT и при этом не обернут в агрегатную функцию, он должен быть в GROUP BY

Типичная ошибка: столбец в SELECT, но не в GROUP BY

Неправильно:

Почему: City не агрегирован и не входит в GROUP BY. SQL Server не может однозначно выбрать, какой город показать, если теоретически у одной группы может быть несколько значений (даже если в ваших данных сейчас это не так).

Правильно:

Группировка по нескольким полям

Например, суммы по городам и статусам:

HAVING: фильтрация групп

WHERE фильтрует строки до группировки, а HAVING фильтрует группы после группировки.

Документация: HAVING (Transact-SQL)

Оставить только клиентов, у которых сумма оплаченных заказов больше 1000

Здесь важно разделение ответственности:

WHERE o.Status = N'Оплачен' убирает неоплаченные строки до группировки

HAVING SUM(o.Amount) > 1000 оставляет только группы, где итог превышает порог

Частая ошибка: пытаться фильтровать агрегат в WHERE

Неправильно:

Так нельзя, потому что WHERE не работает с агрегатами: в момент WHERE суммы еще не посчитаны.

Оконные функции: аналитика без схлопывания строк

GROUP BY схлопывает строки: на выходе становится меньше строк, чем было.

Оконные функции делают другое: они считают показатели по окну строк, но при этом сохраняют исходную детализацию.

!Схема показывает разницу между группировкой и оконными функциями

Базовый синтаксис:

Документация: OVER Clause (Transact-SQL)

SUM(...) OVER(PARTITION BY ...): сумма по клиенту в каждой строке

Что вы получаете:

все заказы остаются построчно

появляется столбец TotalByCustomer, одинаковый для всех заказов одного клиента

Это часто удобнее, чем GROUP BY, когда нужны и детали, и итог.

Накопительный итог: SUM(...) OVER(ORDER BY ...)

Задача: для каждого клиента посчитать накопительную сумму по датам.

Что означает рамка:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW означает: считать сумму от первой строки в разделе клиента до текущей строки включительно

Важно:

ORDER BY внутри OVER задает порядок расчета, а не порядок вывода результата

для порядка вывода используйте обычный ORDER BY в конце запроса

ROW_NUMBER, RANK, DENSE_RANK: нумерация и ранжирование

Документация:

ROW_NUMBER (Transact-SQL)

RANK (Transact-SQL)

DENSE_RANK (Transact-SQL)

Пример: пронумеровать заказы каждого клиента по дате.

Разница между функциями:

ROW_NUMBER всегда дает 1,2,3... без пропусков

RANK при одинаковых значениях дает одинаковый ранг, но потом делает пропуск

DENSE_RANK тоже дает одинаковый ранг при равенстве, но без пропусков

TOP-N в каждой группе: найти самый дорогой заказ на клиента

Это классическая задача, где оконные функции особенно удобны.

Идея:

внутри каждого клиента сортируем заказы по сумме (убывание)

нумеруем

берем rn = 1

LAG и LEAD: сравнение с предыдущей или следующей строкой

Документация:

LAG (Transact-SQL)

LEAD (Transact-SQL)

Пример: разница между текущим заказом и предыдущим заказом клиента.

Если предыдущего заказа нет, LAG вернет NULL, и разница тоже станет NULL. Это нормальное поведение.

Как выбрать: GROUP BY или оконная функция

| Нужно получить | Обычно лучше | Почему | |---|---|---| | Итоговые строки по группам (по одному ряду на клиента/город/статус) | GROUP BY | уменьшается число строк, получается компактная сводка | | И детали, и итоги одновременно (в каждой строке заказа показать сумму по клиенту) | оконные функции | не теряете детализацию | | Топ-1 или топ-N внутри каждой группы | оконные функции + ROW_NUMBER | типовой и надежный паттерн | | Фильтрация по агрегату (например, сумма > 1000) | HAVING | фильтрация делается после группировки |

Практические советы

Всегда уточняйте, что вы хотите получить: сводку или детальные строки с аналитическими колонками.

Не путайте WHERE и HAVING:

- WHERE для строк - HAVING для групп

Для оконных функций почти всегда нужен устойчивый порядок внутри группы: добавляйте в ORDER BY уникализатор вроде OrderID, чтобы не было непредсказуемых совпадений.

Если вы используете JOIN перед агрегацией, следите, чтобы соединение не умножало строки (иначе суммы могут неожиданно вырасти).

Что запомнить из этой статьи

Агрегатные функции считают итог по набору строк.

GROUP BY строит итоги по группам и уменьшает количество строк результата.

HAVING фильтрует группы, а WHERE фильтрует строки.

Оконные функции (OVER) позволяют считать суммы, ранги и накопительные итоги, не теряя детализацию.

ROW_NUMBER помогает решать задачу топ-N в каждой группе, а LAG и LEAD — сравнивать строки внутри группы.

Индексы и производительность: планы выполнения и оптимизация запросов

В предыдущих темах вы научились получать данные (SELECT, JOIN), менять их (INSERT, UPDATE, DELETE) и делать аналитику (GROUP BY, оконные функции). Дальше почти всегда возникает практический вопрос: почему запрос работает медленно и что с этим делать.

В SQL Server производительность чаще всего упирается в то, сколько данных нужно прочитать и какой способ чтения выберет оптимизатор. Индексы и планы выполнения дают вам два главных инструмента:

индексы уменьшают объем чтения и ускоряют поиск

планы выполнения показывают, как именно SQL Server выполняет запрос и где тратится время

Откуда берется “медленно” в SQL Server

Даже на локальной машине запрос может быть медленным, если:

таблица большая, а SQL Server читает слишком много страниц данных

фильтр написан так, что индекс не может быть использован эффективно

нет подходящего индекса под WHERE, JOIN, ORDER BY, GROUP BY

план выполнения выбирает неудачный алгоритм соединения или сортировки

Практическое правило: сначала смотрим план и статистику чтений, и только потом меняем индексы или переписываем запрос.

Что такое индекс

Индекс в SQL Server чаще всего реализован как B-дерево: структура, которая ускоряет поиск значений по ключу.

индекс хранит ключи в отсортированном виде

по ключу можно быстро найти нужные строки

чем точнее запрос “попадает” в индекс, тем меньше данных нужно читать

!Как индекс (B-дерево) ускоряет поиск по ключу

Heap и индексированная таблица

Таблица без кластерного индекса называется heap.

строки в heap не имеют логического порядка

поиск без подходящего некластерного индекса часто превращается в чтение “почти всего”

На практике в рабочих системах большинство таблиц имеют кластерный индекс.

Кластерный индекс

Кластерный индекс определяет физический порядок хранения данных в таблице (логически упорядочивает строки на уровне страниц).

Ключевые свойства:

у таблицы может быть только один кластерный индекс

листовой уровень кластерного индекса и есть “данные таблицы”

запросы по диапазону ключа кластерного индекса (например, по дате) часто выполняются очень эффективно

Часто PRIMARY KEY создается как кластерный индекс (но это настраивается).

Документация: Создание кластерных индексов

Как выбрать ключ кластерного индекса

Обычно хороший ключ:

достаточно уникальный (или почти уникальный)

не слишком широкий (короткие типы, например int, обычно лучше)

не меняется часто (частые изменения ключа вызывают дорогие перемещения)

Некластерный индекс

Некластерный индекс хранит отсортированные ключи и “указатель” на строку данных.

на одной таблице может быть много некластерных индексов

он ускоряет WHERE, JOIN, иногда ORDER BY и GROUP BY

Документация: Создание некластерных индексов

Seek и Scan

В плане выполнения вы часто увидите два базовых способа доступа к данным:

Index Seek (поиск по индексу) — читает небольшой диапазон, “точечно”

Index Scan или Table Scan — читает много страниц, часто большую часть объекта

Scan не всегда плох: если нужно прочитать 80% таблицы, поиск по индексу может быть даже хуже из-за большого числа случайных чтений. Но в типичных OLTP-запросах (точечные выборки) Seek обычно предпочтительнее.

Composite index и порядок ключей

Индекс может быть составным: (A, B, C).

Порядок столбцов критичен:

индекс отлично работает для условий по A, или по A и B, или по A и B и C

для условия только по B (без A) он часто менее полезен

Практическое правило: первым ставьте столбец, который чаще всего используется в фильтре и сильнее всего сужает выборку.

INCLUDE и покрывающий индекс

Некластерный индекс может “покрывать” запрос, если он содержит:

ключевые столбцы для фильтра/соединения

и дополнительно включает нужные для вывода столбцы через INCLUDE

Это уменьшает потребность SQL Server обращаться к данным таблицы для “дочитывания”.

Документация: CREATE INDEX (Transact-SQL)

Key Lookup и почему он бывает дорогим

Ситуация:

SQL Server находит подходящие строки через некластерный индекс

но нужных столбцов нет в индексе

тогда выполняется Key Lookup: для каждой найденной строки “дочитываются” недостающие столбцы из кластерного индекса (или heap)

Если найденных строк мало, Key Lookup может быть нормальным. Если строк много, это превращается в тысячи дополнительных обращений.

!Почему Key Lookup может резко замедлять запрос

Фильтрованные индексы

Фильтрованный индекс — индекс не по всей таблице, а только по части строк (WHERE ...).

Полезно, если:

есть “редкий” статус, по которому часто ищут

большинство строк не подходят под этот статус

Документация: Создание фильтрованных индексов

Цена индексов

Индексы ускоряют чтение, но имеют стоимость:

INSERT, UPDATE, DELETE становятся дороже (нужно поддерживать индексы)

растет размер базы

появляется необходимость обслуживания (например, обновление статистики)

Практическое правило: индексы создают под реальные запросы, а не “на всякий случай”.

Статистика и оптимизатор

SQL Server выбирает план выполнения с помощью оптимизатора. Чтобы оценить, сколько строк вернет условие, оптимизатор опирается на статистику.

Если статистика устарела, план может стать хуже.

Полезные источники:

Статистика

Обновление статистики

Планы выполнения: как читать и где смотреть

План выполнения показывает операторы и их порядок: чтение данных, соединения, сортировки, агрегации.

Estimated и Actual

В SSMS есть два основных варианта:

Estimated Execution Plan — оценка без выполнения запроса

Actual Execution Plan — план после выполнения, с фактическими метриками

Документация: Планы выполнения

Как включить план в SSMS

Откройте окно запроса.

Для фактического плана включите Include Actual Execution Plan.

Выполните запрос и откройте вкладку плана.

На что смотреть в плане в первую очередь

операторы Index Scan и Table Scan на больших таблицах

Key Lookup, особенно если он “много раз”

Sort (сортировка), особенно если она занимает заметную долю стоимости

типы соединений:

- Nested Loops обычно хорош для маленького набора слева - Hash Match часто используется на больших наборах, но требует памяти

Измеряем запрос: STATISTICS IO и TIME

Для практической диагностики включайте статистику:

STATISTICS IO показывает чтения (логические чтения страниц особенно важны)

STATISTICS TIME показывает время CPU и общее время выполнения

Документация:

SET STATISTICS IO

SET STATISTICS TIME

Практика на нашей учебной базе

Ниже — безопасный учебный сценарий: создадим больше заказов, посмотрим план, добавим индекс, сравним.

Подготовка данных

Если у вас в dbo.Customers меньше 50 клиентов, добавьте клиентов (упрощенно):

Запрос, который часто встречается в реальности

Например: найти оплаченные заказы конкретного клиента за период, отсортировать по дате.

Если подходящего индекса нет, вы часто увидите Index Scan (или скан кластерного индекса) и высокие логические чтения.

Создаем индекс под запрос

Логика:

фильтруем по CustomerID, Status, OrderDate

сортируем по OrderDate, OrderID

выводим Amount, Status, OrderDate, OrderID

Пример индекса:

После создания индекса повторите запрос со статистикой и планом.

Что вы обычно получите:

вместо Scan появится Seek по индексу

уменьшатся логические чтения

сортировка может исчезнуть (если порядок индекса совпал с ORDER BY и оптимизатор смог это использовать)

> Практика: индекс не обязан полностью совпадать с ORDER BY, но порядок ключей индекса часто помогает убрать Sort и ускоряет запрос.

Что делать, если появился Key Lookup

Если вы в запросе добавите еще столбцы (например, IsPriority или что-то еще) и они не входят в индекс, может появиться Key Lookup.

Варианты:

добавить нужные столбцы в INCLUDE (если это разумно)

пересмотреть, какие столбцы реально нужны в SELECT

иногда выгоднее другой индекс или другой запрос

Как писать запросы так, чтобы индексы работали

Делайте условия “дружелюбными” к индексу

Частая ошибка: применять функцию к столбцу в WHERE.

Плохо:

Обычно лучше (диапазон):

Причина: когда выражение стоит “слева” от сравнения и меняет столбец, оптимизатору сложнее использовать поиск по индексу.

Следите за неявными преобразованиями типов

Если сравнивать int со строкой или date со строкой в нестандартном формате, SQL Server может делать неявные преобразования, и это ухудшает план.

Практика:

сравнивайте совместимые типы

даты задавайте в безопасном формате YYYY-MM-DD

Не выбирайте лишние столбцы

SELECT * увеличивает:

чтения

объем передаваемых данных

шанс получить Key Lookup

Минимальный чек-лист оптимизации

Если запрос медленный, действуйте так:

Включите фактический план выполнения в SSMS и посмотрите, где “тяжелые” операторы.

Включите SET STATISTICS IO, TIME и сравните чтения и время до и после изменений.

Проверьте, есть ли подходящий индекс под условия WHERE и связи JOIN.

Проверьте, нет ли Key Lookup на больших выборках.

Проверьте, не мешают ли индексу выражения над столбцами и неявные преобразования.

Что запомнить из этой статьи

Индексы ускоряют чтение, но замедляют изменения данных и занимают место.

Кластерный индекс задает “основной” порядок данных таблицы; некластерные индексы ускоряют конкретные фильтры и соединения.

Index Seek обычно быстрее Scan, но не всегда.

Key Lookup может стать узким местом, если выполняется много раз.

Планы выполнения и SET STATISTICS IO/TIME — базовые инструменты диагностики.

Хороший индекс почти всегда связан с реальными запросами: WHERE + JOIN + ORDER BY.

Основы администрирования: пользователи, права, бэкапы, восстановление

В прошлых статьях вы научились писать запросы, проектировать таблицы, анализировать данные и смотреть на производительность через индексы и планы выполнения. Но в реальной работе с SQL Server почти всегда есть еще две обязанности: безопасный доступ к данным и гарантия, что данные можно восстановить.

В этой статье разберем базовый минимум администрирования, который нужен разработчику и аналитику:

как устроены пользователи и права (кто и что может делать)

как выдавать права безопасно и предсказуемо

как работают резервные копии и модели восстановления

как восстановить базу (в том числе до конкретного момента времени)

Модель безопасности SQL Server: логины, пользователи, роли

В SQL Server важно различать доступ на уровне сервера и на уровне конкретной базы.

Логин (login) — сущность на уровне экземпляра SQL Server: позволяет подключиться к серверу.

Пользователь (user) — сущность внутри конкретной базы данных: определяет, что можно делать в этой базе.

Обычно (но не всегда) логин сопоставляется пользователю в базе.

!Как логин на сервере связан с пользователем и правами в базе

Документация:

Principals (Database Engine)

Permissions (Database Engine)

Аутентификация: Windows и SQL

Есть два основных способа аутентификации:

Windows Authentication — вход через учетку Windows/AD. Обычно предпочтительнее в корпоративных средах.

SQL Server Authentication — вход по логину и паролю SQL Server (например, для внешних интеграций или когда нет домена).

Практика для обучения:

для локальной разработки часто удобно Windows Authentication

для отработки сценариев прав полезно уметь создавать SQL-логины и выдавать им минимальные права

Документация: Choose an authentication mode

Минимальная практика: создать логин и пользователя

Ниже — типовой сценарий: создать логин на сервере и пользователя в базе CourseDB.

> Выполняйте такие команды под администраторской учеткой (например, вашей Windows-учеткой, которая в sysadmin на учебной машине).

Создаем SQL-логин

Документация: CREATE LOGIN

Создаем пользователя в базе и сопоставляем с логином

Документация: CREATE USER

Права доступа: GRANT, DENY, REVOKE

Права бывают на разных уровнях:

сервер (например, создание баз)

база (например, создание таблиц)

схема/объект (например, SELECT по конкретной таблице)

Три базовые команды управления правами:

GRANT — выдать право

DENY — явно запретить (обычно сильнее, чем GRANT)

REVOKE — убрать ранее выданное GRANT или DENY

Документация: GRANT, DENY, REVOKE

Даем пользователю право только читать конкретные таблицы

Допустим, вы хотите, чтобы пользователь мог читать dbo.Customers и dbo.Orders, но не мог менять данные.

Если попытаться выполнить INSERT/UPDATE/DELETE под этим пользователем, будет ошибка прав.

Проверяем, кто выдал права и какие они

Полезные представления:

Документация: sys.database_permissions, sys.database_principals

Роли: проще управлять правами

Выдавать права напрямую каждому пользователю можно, но неудобно. В реальных системах почти всегда используют роли:

добавляете пользователя в роль

выдаете права роли

Встроенные роли базы

Часто используемые встроенные роли базы данных:

| Роль | Для чего | Риск | |---|---|---| | db_datareader | читать все таблицы и представления | может увидеть лишние данные | | db_datawriter | писать во все таблицы | может повредить данные | | db_ddladmin | выполнять DDL (CREATE/ALTER/DROP) | может менять схему базы | | db_owner | полный контроль в базе | почти как администратор базы |

Документация: Database-level roles

Пользовательская роль с минимальными правами

Создадим роль только для чтения двух таблиц.

Документация: CREATE ROLE, ALTER ROLE

Принцип наименьших привилегий и типовые ошибки

Принцип наименьших привилегий означает: выдавайте только те права, которые нужны для задачи, и не больше.

Типовые ошибки:

выдавать db_owner “чтобы работало”

выдавать db_datareader и потом удивляться утечкам чувствительных данных

создавать общих пользователей без аудита и без понятной роли

Практичный подход:

Создать роль под задачу (например, ReportingReadOnly).

Выдать права роли на нужные объекты.

Добавлять пользователей в роль.

Резервные копии: какие бывают и зачем модели восстановления

Бэкап — это ваша возможность восстановить данные после ошибок, сбоев диска, случайного DELETE, проблемного деплоя.

В SQL Server есть три основных вида резервных копий:

Full — полная копия базы

Differential — изменения с момента последнего полного бэкапа

Log — бэкап журнала транзакций (позволяет восстановление до точки во времени)

!Как связаны Full, Differential и Log бэкапы и в каком порядке их восстанавливают

Документация: Backup and restore of SQL Server databases

Модели восстановления: SIMPLE и FULL

Модель восстановления влияет на то, можно ли делать бэкапы журнала транзакций и восстанавливать базу до конкретного момента.

SIMPLE

- журнал транзакций “обрезается” автоматически - бэкап лога не используется для восстановления до точки во времени - проще для небольших учебных баз

FULL

- можно делать log-бэкапы - можно восстановиться до точки во времени (если есть цепочка log-бэкапов) - стандарт для многих продакшен-систем

Проверить модель:

Сменить модель (учебный пример):

Документация: Recovery models

Практика: сделать бэкап базы

Полный бэкап

Что здесь важно:

INIT перезаписывает файл (в реальности часто используют более аккуратные стратегии, но для учебной машины это удобно)

COMPRESSION уменьшает размер (если доступно в вашей редакции)

CHECKSUM добавляет проверки целостности

STATS показывает прогресс

Документация: BACKUP DATABASE

Бэкап лога (только при FULL)

Документация: BACKUP LOG

Проверить, что бэкап читается

RESTORE VERIFYONLY не восстанавливает базу, но проверяет, что бэкап корректно читается.

Документация: RESTORE VERIFYONLY

Восстановление: базовый сценарий

Самый простой сценарий: восстановить базу из полного бэкапа.

Важно:

восстановление перезапишет данные базы (если вы так укажете)

в реальности восстановление обычно делают на отдельном сервере или в отдельную базу для проверки

Восстановление из полного бэкапа

Пояснение ключевых опций:

REPLACE разрешает перезаписать существующую базу

RECOVERY переводит базу в рабочее состояние (подключает пользователей)

Документация: RESTORE DATABASE

Восстановление до точки во времени: общий смысл

В продакшене часто важно “откатиться” к моменту до ошибки (например, до случайного массового DELETE). Для этого нужна модель FULL и цепочка log-бэкапов.

Логика такая:

Восстанавливаем полный бэкап в режиме ожидания логов (NORECOVERY).

При необходимости накатываем последний differential тоже в NORECOVERY.

Накатываем log-бэкапы по порядку.

Последний log-бэкап можно восстановить с STOPAT, чтобы остановиться на нужном времени.

Завершаем восстановление RECOVERY.

Учебный пример шаблона (времена и файлы для примера):

Документация:

Restore a database to a point in time

RESTORE LOG

Практичные правила для учебы и для работы

Минимальный набор правил по безопасности

Создавайте роли под задачи, а не раздавайте права напрямую.

Начинайте с минимальных прав: обычно это SELECT на нужные объекты.

Не используйте db_owner для повседневной работы.

Разделяйте “админские” действия и “рабочие” действия (разные учетные записи).

Минимальный набор правил по бэкапам

Полный бэкап — основа любого восстановления.

Без регулярных log-бэкапов в FULL модель восстановления “не дает” восстановление до точки во времени.

Регулярно проверяйте, что бэкапы читаются (RESTORE VERIFYONLY) и что вы умеете восстанавливать.

Что запомнить из этой статьи

Доступ в SQL Server разделяется на логин на сервере и пользователя в базе.

Права выдаются через GRANT, запреты — через DENY, отмена — REVOKE.

Управлять правами удобнее через роли.

Бэкапы бывают полные, дифференциальные и лога транзакций.

Для восстановления до точки во времени нужна модель FULL и цепочка log-бэкапов.

В следующей практике курса вы сможете объединить знания: создать роль под аналитика, выдать ей безопасные права на таблицы и настроить регулярный бэкап учебной базы, чтобы спокойно экспериментировать с UPDATE и DELETE.