Отправь повестку себе и другу,
Получи патроны для выполнения боевых образовательных мероприятий!

+7 499 959-43-99

MS SQL Server разработчик

Guru SQL: подробно об оптимизации, планах, транзакциях и о том, как писать эффективные запросы на данных большого объёма

Обучение сотрудников

О курсе

Процесс обучения

После обучения

Что даст вам этот курс

Во время курса будем подробно разбирать язык запросов и внутренние процессы СУБД, происходящие на всех этапах работы с запросом. Курс позволит понять детали процессов и получить чёткое представление, что делает тот или иной код, где могут возникнуть потенциальные проблемы, как их можно разрешить.

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

Этот курс предназначен для:
— разработчиков с опытом работы с базами на уровне «делал и использовал, но как внутри работает не знаю» (например, через ORM);
— разработчиков с небольшим опытом работы с базами данных и желанием продвинуться дальше.

После обучения вы сможете:
— разрабатывать на SQL;
— проектировать БД и понимать все нюансы;
— анализировать и оптимизировать производительности запросов;
— писать сложные хранимые процедуры, функции и триггеры;
— читать план запроса.

Выпускной проект — создание и оптимизация собственной базы данных (для своей компании/стартапа). Преподаватель поможет с выбором темы и подскажет, как уменьшить scope работ, а у вас появится возможность получить обратную связь и отработать полученные знания на реальном материале.

О курсе и о себе

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

18 работодателей

Много практики и кейсов из реальных проектов

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

Кристина Кучерова

Преподаватель курса

Кристина Кучерова

Преподаватель курса

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

Кристина Кучерова

Сбербанк России

Архитектор модели данных в Сбербанке России. Окончила ЮРГПУ (НПИ) по специальности «Математическое обеспечение и администрирование информационных систем». С 2015 года — аспирант в Санкт-Петербургском политехническом университете им. Петра Великого.

Работала в компании Comepay в качестве DB-тимлида и заместителя тех. директора по архитектуре. Принимала участие в реализации проекта Syncplicity (Distillery, USA), где занималась разработкой БД и оптимизацией производительности.

Участник отраслевых конференций CMG Impact 2016 (San Diego, USA), Zabbix Conf 2017 (Рига, Латвия) и прочих.

Есть опыт преподавания курса «Базы Данных» в Ростовском колледже связи и информатики. Считает, что очень важно учиться именно на кейсах из реального производства.

Кристина
Кучерова

Михаил
Ржевский

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

Кристина Кучерова

Сбербанк России

Михаил Ржевский

IT-специалист, фрилансер, сертифицированный разработчик Dynamics AX и Dynamics CRM с более чем 5-летним опытом (экспертный уровень). Общий опыт работы в IT-индустрии — 15 лет.

Обладает широким спектром профессиональных навыков (MS SQL Server, MySQL, C# , HTML, XML, CSS, Javascript, JQuery, Unit Tests). Участвовал в реализации множества проектов, работал Senior Dynamics CRM. Является Microsoft Certified Trainer.

С 2009 года помимо основной работы занимается преподавательской деятельностью. Автор собственных курсов и специализированных статей на hosting-it.ru. Выпускник МИРЭА (окончил в 1994 году).

Этот курс вам подойдёт, если вы:
— работали с БД и знаете, как они выглядят
— писали или читали простые запросы
— знаете типы данных и основные операторы на базовом уровне
— или являетесь выпускником курса «Реляционные СУБД»

Подробная программа PDF 162 kb

Программа обучения

Модуль 1

Использование БД

Модуль 2

Проектирование БД

Модуль 3

Оптимизация и Администрирование БД

Подробная программа PDF 162 kb

Использование БД

Базы данных и какие они бывают

Что такое базы данных и какие они бывают?
Как появились и где какие виды используются?
О возможностях SQL server и об Azure
О том как будем работать и как развернуть бэкап учебной базы.

Домашние задания: 1

1 Разворачиваем Backup и смотрим какой проект какую СУБД использует

1. Установите программы SQL Server 2016\2017
2. Разверните у себя бэкап базы WideWorldImporters (скриншот с восстановленной БД) WideWorldImporters-Full.bak
3. Поставьте SQL Sentry Plan Explorer
4. Сделайте проект для курса на github, пришлите ссылку на него (тут будем выкладывать скрипты по домашкам)

Опционально
5. Найдите, какую СУБД использует любимый вами проект
Выберите проект, которым вы часто пользуетесь и найдите информацию с помощью какой базы данных он реализован.
Классификация: иерархическая, сетевая, реляционная, объектно-ориентированная, NoSql (если знаете колоночная, key-value, документарная, in-memory, графовая)
Структура ответа:
Название проекта: Проект
Используемые базы данных: БД (реляционная), БД (NoSQL документарная)
Количество серверов БД: 10
Ссылка на источник:

Оператор SELECT и простые фильтры

Работа с БД MS SQL работа с таблицами, язык SQL, оператор SELECT
Простые фильтры в WHERE, объединение условий
Планы запросов.
Виды соединений таблиц.

Домашние задания: 1

1 Запросы SELECT

Напишите выборки для того, чтобы получить:
1. Все товары, в которых в название есть пометка urgent или название начинается с Animal
2. Поставщиков, у которых не было сделано ни одного заказа (потом покажем как это делать через подзапрос, сейчас сделайте через JOIN)
3. Продажи с названием месяца, в котором была продажа, номером квартала, к которому относится продажа, включите также к какой трети года относится дата - каждая треть по 4 месяца, дата забора заказа должна быть задана, с ценой товара более 100$ либо количество единиц товара более 20. Добавьте вариант этого запроса с постраничной выборкой пропустив первую 1000 и отобразив следующие 100 записей. Соритровка должна быть по номеру квартала, трети года, дате продажи.
4. Заказы поставщикам, которые были исполнены за 2014й год с доставкой Road Freight или Post, добавьте название поставщика, имя контактного лица принимавшего заказ
5. 10 последних по дате продаж с именем клиента и именем сотрудника, который оформил заказ.
6. Все ид и имена клиентов и их контактные телефоны, которые покупали товар Chocolate frogs 250g

SQL операторы изменения данных

Операторы INSERT, UPDATE, UPSERT, DELETE, MERGE, Bulk insert

Домашние задания: 1

1 Insert, Update, Merge

1. Довставлять в базу 5 записей используя insert в таблицу Customers или Suppliers
2. удалите 1 запись из Customers, которая была вами добавлена
3. изменить одну запись, из добавленных через UPDATE
4. Написать MERGE, который вставит вставит запись в клиенты, если ее там нет, и изменит если она уже есть
5. Напишите запрос, который выгрузит данные через bcp out и загрузить через bulk insert

Подзапросы и WITH в SQL

Операторы IN, EXISTS, NOT EXISTS, ANY, SOME, ALL
Выборки из подзапросов.
WITH табличные выражения (в том числе рекурсивные).
Анализируем получающиеся планы и сравниваем их.

Домашние задания: 1

1 Подзапросы и CTE

Для всех заданий где возможно, сделайте 2 варианта запросов:
1) через вложенный запрос
2) через WITH (для производных таблиц)

Напишите запросы:
1. Выберите сотрудников, которые являются продажниками, и еще не сделали ни одной продажи.
2. Выберите товары с минимальной ценой (подзапросом), 2 варианта подзапроса.
3. Выберите информацию по клиентам, которые перевели компании 5 максимальных платежей из [Sales].[CustomerTransactions] представьте 3 способа (в том числе с CTE)
4. Выберите города (ид и название), в которые были доставлены товары, входящие в тройку самых дорогих товаров, а также Имя сотрудника, который осуществлял упаковку заказов
5. Объясните, что делает и оптимизируйте запрос:
SELECT
Invoices.InvoiceID,
Invoices.InvoiceDate,
(SELECT People.FullName
FROM Application.People
WHERE People.PersonID = Invoices.SalespersonPersonID
) AS SalesPersonName,
SalesTotals.TotalSumm AS TotalSummByInvoice,
(SELECT SUM(OrderLines.PickedQuantity*OrderLines.UnitPrice)
FROM Sales.OrderLines
WHERE OrderLines.OrderId = (SELECT Orders.OrderId
FROM Sales.Orders
WHERE Orders.PickingCompletedWhen IS NOT NULL
AND Orders.OrderId = Invoices.OrderId)
) AS TotalSummForPickedItems
FROM Sales.Invoices
JOIN
(SELECT InvoiceId, SUM(Quantity*UnitPrice) AS TotalSumm
FROM Sales.InvoiceLines
GROUP BY InvoiceId
HAVING SUM(Quantity*UnitPrice) > 27000) AS SalesTotals
ON Invoices.InvoiceID = SalesTotals.InvoiceID
ORDER BY TotalSumm DESC

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

Опциональная часть:
В материалах к вебинару есть файл HT_reviewBigCTE.sql - прочтите этот запрос и напишите что он должен вернуть и в чем его смысл, можно если есть идеи по улучшению тоже их включить.

GROUP BY и HAVING

Как формируются группы, почему условия из HAVING нельзя написать в WHERE?
Аггрегатные функции. Написание отчетов.

Домашние задания: 1

1 Группировки и агрегатные функции

1. Посчитать среднюю цену товара, общую сумму продажи по месяцам
2. Отобразить все месяцы, где общая сумма продаж превысила 10 000
3. Вывести сумму продаж, дату первой продажи и количество проданного по месяцам, по товарам, продажи которых менее 50 ед в месяц.

Оконные функции в SQL

Windows функции в SQL - ROW_NUMBER, LAG, LEAD, NTILE.
Почему они оконные? Чем выгодны.
Сравним планы с оконными функциями и без них.

Домашние задания: 1

1 Оконные функции

1.Сделать расчет суммы продаж нарастающим итогом по месяцам с 2015 года (в рамках одного месяца он будет одинаковый, нарастать будет в течение времени выборки)
Вывести Ид продажи, название клиента, дату продажи, сумму продажи, сумму нарастающим итогом
Пример
Дата продажи Нарастающий итог по месяцу
2015-01-29 4801725.31
2015-01-30 4801725.31
2015-01-31 4801725.31
2015-02-01 9626342.98
2015-02-02 9626342.98
2015-02-03 9626342.98
Продажи можно взять из таблицы Invoices. Сумму из 2х таблиц, из какой будет удобнее.
Сделать 2 варианта запроса - через windows function и без них. Написать какой быстрее выполняется, сравнить по set statistics time on;
2. Вывести список 2х самых популярного продуктов (по кол-ву проданных) в каждом месяце за 2016й год (по 2 самых популярных продукта в каждом месяце)
3. Функции одним запросом
Посчитайте по таблице товаров, в вывод также должен попасть ид товара, название, брэнд и цена
пронумеруйте записи по названию товара, так чтобы при изменении буквы алфавита нумерация начиналась заново
посчитайте общее количество товаров и выведете полем в этом же запросе
посчитайте общее количество товаров в зависимости от буквы начала называния товара
следующий ид товара на следующей строки (по имени) и включите в выборку
предыдущий ид товара (по имени)
названия товара 2 строки назад, в случае если предыдущей строки нет нужно вывести "No items"
сформируйте 30 групп товаров по полю вес товара на 1 шт
Для этой задачи НЕ нужно писать аналог без аналитических функций
4. По каждому сотруднику выведете последнего клиента, которому сотрудник что-то продал
В результатах должны быть ид и фамилия сотрудника, ид и название клиента, дата продажи, сумму сделки
5. Выберите по каждому клиенту 2 самых дорогих товара, которые он покупал
В результатах должно быть ид клиета, его название, ид товара, цена, дата покупки

Опционально можно сделать вариант запросов для заданий 2,4,5 без использования windows function и сравнить скорость как в задании 1.

Bonus из предыдущей темы
Напишите запрос, который выбирает 10 клиентов, которые сделали больше 30 заказов и последний заказ был не позднее апреля 2016.

Временные таблицы и табличные переменные

Разобраться что есть что, в чем разница, когда что использовать.

25 февраля, 20:00 — 21:30

Лектор: Михаил Ржевский

Домашние задания: 1

1 Сравниваем временные таблицы и табличные переменные

Напишите запрос с временной таблицей и перепишите его с табличной переменной. Сравните планы.

Операторы CROSS APPLY, PIVOT, CUBE

Использование CROSS APPLY, PIVOT и UNPIVOT, CUBE, ROLLUP

28 февраля, 20:00 — 21:30

Домашние задания: 1

1 Pivot и Cross Apply

Напишите по 1 запросу с использованием:
PIVOT
UNPIVOT
CROSS APPLY

Итоговое занятие по SELECT

Порядок выполнение, сортировки.
Как читать SELECT в несколько экранов, примеры.
Что такое SQL injections и как их избежать?

4 марта, 20:00 — 21:30

Операторы DDL

Create Table, alter table
Представления и материализованные представления.
Как менять БД под нагрузкой?

7 марта, 20:00 — 21:30

Домашние задания: 1

1 Используем DDL

Создание таблиц и представлений для своего проекта.

Хранимые процедуры и фукнции

Создание хранимых процедур и функций, триггеры, циклы, курсоры

11 марта, 20:00 — 21:30

Лектор: Михаил Ржевский

Домашние задания: 1

1 SP и function

1) Создать 1 функцию и 1 хранимую процедуру
2) Cоздать одинаковую функцию и хранимую процедуру, посмотреть в чем разница в производительности и почему

Уровни изоляции транзакций

Работа с транзациями и обработка ошибок в SQL.
Deadlock и и что с ними делать, флаги трассировки.
Как читать лог?

14 марта, 20:00 — 21:30

Домашние задания: 1

1 Смотрим разницу с разным уровнем транзакции

1) Прописываем для каких процедур какой уровень изоляции нужен.
2) Пишем запрос в транзакции где есть выборка, вставка\добавление\удаление данных и параллельно запускаем выборку данных в разных уровнях изоляции, нужно предоставить мини отчет, что на каком уровне было видно со скриншотами и ваши выводы (1-2 предложение)
3) Пишем паралелльно в 2х окнах добавление данных в одну таблицу с разным уровнем изоляции, изменение данных в одной таблице, изменение одной и той же строки. Что в итоге получилось, что нового узнали.

Динамический SQL

Обсудим разницу между Exec и sp_executesql.
Примеры динамического sql, когда его стоит использовать.
Процедуры типа "Kitchen sink".

18 марта, 20:00 — 21:30

Домашние задания: 1

1 Процедура вида Kitchen sink

Переписываем одну и ту же процедуру с множеством входных параметров по поиску в заказах на динамический SQL.
Сравниваем планы запроса.

Выборки из xml и json полей

Разберем варианты выборок из xml и json полей.
Напишем пару примеров выборок из разного xml'а.

21 марта, 20:00 — 21:30

Лектор: Павел Стрекалов

Домашние задания: 1

1 Работа с разными видами xml

Тренеруемся писать запросы из xml для разных видов xml'а - сравниваем варианты.

Создание и использование CLR

Что такое CLR? Зачем это и как использовать?

25 марта, 20:00 — 21:30

Лектор: Павел Стрекалов

Домашние задания: 1

1 Создаем CLR

Добавляем в SQL server через CLR функцию, которой лично вам не хватает.
Делимся впечатлением и опытом с коллегами

Проектирование БД

Как сделать схему БД.
Средства проектирования, нормализация.

28 марта, 20:00 — 21:30

Домашние задания: 1

1 Проект

Смотрим на схему, которая получилась в создании таблиц через DDL
Строим схему в use-case средстве, генерируем SQL код для создания.

Индексы

Виды индексов. Плюсы и минусы индексов.
Зачем они нужны?
Как их создавать?

1 апреля, 20:00 — 21:30

Домашние задания: 1

1 Какие индексы вам нужны

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

Подходы к проектированию БД

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

4 апреля, 20:00 — 21:30

Домашние задания: 1

1 Смотрим, что можно изменить

Смотрим на свои решения в проекте и перепроектируем.

Проектируем БД для OLTP нагрузки. Почему важно разделить OLTP и OLAP.

Best practices для OLTP.

8 апреля, 20:00 — 21:30

Домашние задания: 1

1 Маркируем, что у вас OLTP, что OLAP

Маркируем, что у вас OLTP, что OLAP по всем объектам,
Оцениваем насколько сложно их будет разнести по разным базам и разным серверам - пишем небольшой план, где какие таблицы останутся и нужно ли будет их дублировать.

Проектирование хранилищ данных и OLAP систем

Разобрать основные паттерны проектирования хранилищ данных, обсудить возможные проблемы и то как их можно избежать.

11 апреля, 20:00 — 21:30

Семинар по созданным проектам БД

Обсуждаем созданные проекты.

15 апреля, 20:00 — 21:30

Оптимизация и Администрирование БД

Планы запросов, а теперь подробнее.

Подробный анализ планов запросов, чтение статистик, использование планов из кэша SQL Server.

18 апреля, 20:00 — 21:30

Домашние задания: 1

1 Анализ плана

Анализ плана запроса и написание рекомендаций, что можно поправить в индексах\запросе исходя из плана.

DMV в SQL Server

Что такое DMV? Как их можно использовать?
Обсудим самые популярные из них.

22 апреля, 20:00 — 21:30

Домашние задания: 1

1 DMV

Написание 3 запросов с использованием dmv
Анализ по использованию индексов
Анализ индексов рекомендованных к созданию
Анализ самых больших таблиц базы данных

Оптимизация работы базы данных

Оптимизация работы базы данных: анализ узких мест и работа с ними Activity Monitor. Профилирование запросов и extended events

25 апреля, 20:00 — 21:30

Лектор: Михаил Ржевский

Популярные Hint'ы и подсказки оптимизатору

Изучаем популярные виды Hint'ов
За и против их использования.

29 апреля, 20:00 — 21:30

Семинар: Оптимизация сложных запросов

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

2 мая, 20:00 — 21:30

Еще раз об индексах

Как работает обновление статистики?
Как найти недостающие индексы и неиспользуемые?
Как можно менять индексы на работающей системе?
Rebuild и reorg индексов в системе.

6 мая, 20:00 — 21:30

Репликация данных, Job'ы и SQL Agent

Виды репликации в SQL Server, возможности настройки, как выбрать нужный тип репликации? Как настроить репликацию. Что нужно мониторить по репликации?

9 мая, 20:00 — 21:30

Очереди в MS SQL Server

Зачем нужны очереди?
Какие очереди в SQL Server бывают?
Создадим и настроим очередь.

13 мая, 20:00 — 21:30

Домашние задания: 1

1 Создание очереди

Создание очереди в БД для фоновой обработки задачи в БД.

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

Немного про backup, restore, виды backup'ов.

16 мая, 20:00 — 21:30

Лектор: Михаил Ржевский

Секционирование таблиц

Секционирование таблиц. Когда нужно его делать?
Пример секционирование со sliding window.

20 мая, 20:00 — 21:30

Домашние задания: 1

1 Секционирование таблицы

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

Безопасность в SQL Server

Немного про пользователей и политики безопасности в SQL Server.

23 мая, 20:00 — 21:30

Лектор: Павел Стрекалов

Обсуждение проектов. Подводим итоги курса.

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

27 мая, 20:00 — 21:30

Домашние задания: 1

1 Защита проектов

Небольшое описание вашего проекта, презентация ваших решений, функциональности и фишек.
Обязательно нужно включить на какую нагрузку вы рассчитываете и какие решения приняли, чтобы ее выдержать.

Выпускной проект

Выпускной проект — создание и оптимизация собственной базы данных (для своей компании/стартапа). Преподаватель поможет с выбором темы и подскажет, как уменьшить scope работ, а у вас появится возможность получить обратную связь и отработать полученные знания на реальном материале.

Подглядеть

Андрей Сандригайло, руководитель группы полевых и сменных инженеров

Кристина Кучерова

Видеоматериалы по теме

28 января в 20:00

После обучения вы

Сможете наконец сделать так, чтобы в базе всё считалось и не падало:
— перепишете свои запросы так, чтобы они работали в 5 раз быстрее
— удалите ненужные индексы и добавите нужные
— научитесь применять изменения к базе без простоя

Ваш сертификат

otus.ru

Константин Константинопольский

успешно закончил курс
«MS SQL Server разработчик»

Успешных заданий:
16 из 16

Проектная работа:
Распределённая система сетевого мониторинга

Виталий Чибриков
Генеральный директор

№ 0001

otus.ru

Константин Константинопольский

успешно закончил курс
«MS SQL Server разработчик»

Успешных заданий:
16 из 16