10 вопросов для собеседования T-SQL разработчиков

Перевод статьи «SQL Server interview questions for experienced developers».

В этой статье рассмотрим 10 вопросов для опытных T-SQL разработчиков.

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

Собеседование опытных кандидатов не может обойтись без обсуждения теоретических вопросов. Из-за этого вопросы формулируются так, чтобы ответ кандидата предполагал полноценное рассуждение на заглавную тему поставленной задачи и демонстрацию знаний в связанных темах. Вне зависимости от лаконичности вопроса следует избегать односложных ответов «да» или «нет».

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

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

Несмотря на то, что для ответа на данные вопросы не требуется писать код, шанс верного ответа на них прямо пропорционален опыту в SQL-разработке. Этот список удобно использовать при первом разговоре с кандидатом по телефону. Если разработчик способен верно ответить на большую часть из этих теоретических вопросов, то можно приглашать его на тестирование навыков написания T-SQL.

От редакции Techrocks: возможно, вам также будет интересна статья «Топ-30 вопросов по SQL на технических собеседованиях«.

Вопросы

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

Способен ли первичный ключ быть некластеризованным индексом? Если да, то зачем это нужно?
Длина ключа для кластеризванного индекса должна быть небольшой. Аргументируйте эту рекомендацию.
Зачем использовать WITH CHECK OPTION при создании представления view?
Объясните разницу между операторами JOIN и APPLY.
Возможно ли вызвать хранимую процедуру из пользовательской функции и наоборот? В чём разница между этими двумя вариантами?
Можно ли в пользовательской функции использовать команды INSERT, UPDATE, и DELETE? Если да, то проиллюстрируйте ответ примерами.
Какие ошибки не отлавливает CATCH? Приведите хотя бы один пример.
В чем заключаются основные отличия между триггерами AFTER и INSTEAD OF?
Какие методы позволяют отследить активные транзакции в текущем подключении? А как узнать количество таких транзакций?
Какие уровни изоляции транзакций предотвращают коллизии (проблема параллельного доступа) и как им это удаётся? Иными словами, каков наивысший уровень изоляции в SQL Server и чем сами уровни отличаются друг от друга?

Ответы

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

1. Способен ли первичный ключ быть некластеризованным индексом? Если да, то зачем это нужно?

Да, способен.

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

Допустим, что одна колонка в таблице очень часто используется в качестве ключевой для джоинов. В некоторых запросах по ней происходит сортировка. Кажется, что именно она является кандидатом на кластеризаованный индекс. Но по логике приложения в ней допустимо хранить NULL’ы и повторяющиеся данные, что противоречит принципу первичного ключа. Вместо этого данная колонка может быть кластеризованным индексом без первичного ключа. Роль первичного ключа может взять на себя другая колонка.

2. Длина ключа для кластеризованного индекса должна быть небольшой. Аргументируйте эту рекомендацию.

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

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

3. Зачем использовать WITH CHECK OPTION при создании представления view?

Если нужно проапдейтить таблицу через представление, то WITH CHECK поможет убедится в том, что изменённые данные доступны через представление.

С этой опцией результат любого INSERT или UPDATE должен соответствовать критериям в секции WHERE внутри представления. Иными словами, WITH CHECK не позволит проапдейтить строку так, что данные их неё исчезнут, или сделать INSERT строки, которая не попадёт в представление.

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

4. Объясните разницу между операторами JOIN и APPLY.

У оператора APPLY есть два варианта — CROSS APPLY и OUTER APPLY.

CROSS APPLY подобно INNER JOIN возвращает строки, соответствующие условию, из левой таблицы в комбинации с соответствующими строками из правой таблицы.

OUTER APPLY возвращает все строки левой таблицы вне зависимости от условий. При этом со строками правой таблицы комбинируются те строки из левой таблицы, которые подошли по условию. Те, что не попали под условие, комбинируются с NULL’ами. Таким образом, OUTER APPLY похож на LEFT JOIN.

Отличие APPLY в том, что он может использовать табличные функции в качестве правой таблицы, чем не может похвастаться JOIN. Также APLLY не требует использования ON. Поэтому APPLY позволяет гораздо проще писать сложную логику запроса.

5. Возможно ли вызвать хранимую процедуру из пользовательской функции и наоборот? В чём разница между этими двумя вариантами?

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

Для вызова хранимки используют EXECUTE или EXEC, а функцию вызывают внутри запроса. Функции, в отличие от хранимых процедур, не способны модифицировать данные. Всё это делает невозможным вызов хранимки через функцию. Зато хранимая процедура способна вызвать другие хранимки. Тот же принцип справедлив и для функций — одна может вызвать другую.

6. Можно ли в пользовательской функции использовать команды INSERT, UPDATE, и DELETE? Если да, то проиллюстрируйте ответ примерами.

Как бы это странно ни звучало — да, можно. Хоть ни локальные, ни глобальные temp-таблицы нельзя применять в пользовательских функциях, вместо них можно использовать табличные переменные.

В пользовательскую функцию можно передать DML-выражения для модификации табличных переменных. Например, данные табличных переменных можно добавлять, редактировать и удалять в таких функциях.

Важно упомянуть, что табличные переменные, как и временные таблицы, хранятся в tempdb, не в памяти. Однако временные таблицы в пользовательских функциях использовать нельзя. И, наконец, изменения данных в табличных переменных в пользовательских функциях не считаются изменениями в базе данных.

Итак, утверждение, что DML-операции невозможны внутри пользовательских функций, неверно. Такие операции невозможны по отношению объектов в БД (таблиц, например), но допустимы для табличных переменных.

7. Какие ошибки не отлавливает CATCH? Приведите хотя бы один пример.

TRY…CATCH позволяет отлавливать ошибки. Если ошибка падает в блоке TRY, то он передает управление блоку CATCH, в котором обычно описано, как реагировать на ошибку. Но некоторые ошибки остаются без внимания конструкции TRY…CATCH.

К ним относятся ошибки с уровнем серьёзности от 10 и ниже (информационные сообщения, возвращающие сведения о состоянии или оповещающие о несерьезных ошибках) и от 20 и выше (указывают на системные проблемы и являются неустранимыми ошибками). Прерванные запросы, оборванные соединения и закрытые сессии тоже не будут регистрироваться TRY…CATCH. Ошибки рекомпиляции (statement-level recompilation errors) и, например, синтаксические ошибки тоже не передаются в CATCH. Яркий тому пример — ошибка о несуществующей таблице.

8. В чем заключаются основные отличия между триггерами AFTER и INSTEAD OF?

Отвечая на этот вопрос, нужно объяснить, что из себя представляет триггер.

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

DML-триггеры (запуск в ответ на INSERT, UPDATE или DELETE)
DDL-триггеры (запуск в ответ на CREATE, ALTER, DROP, GRANT, DENY, REVOKE или UPDATE STATISTICS)
Logon-триггеры (запуск в ответ на подключение к БД)

И AFTER, и INSTEAD OF относятся к первому типу, а значит срабатывают вслед событиям DML.

Триггер AFTER срабатывает строго после выполнения DML-команды. А INSTEAD OF запускает запрос, срабатывающий вместо INSERT, DELETE или UPDATE.

На каждую DML-команду можно назначить множество триггеров AFTER. При этом на каждый INSERT, DELETE или UPDATE можно назначить только один INSTEAD OF.

9. Какие методы позволяют отследить активные транзакции в текущем подключении? Rак узнать количество таких транзакций?

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

XACT_STATE() возвращает три значения:

1 — активные транзакции есть
0 — активных транзакций нет
-1 — активные транзакции есть, но они не зафиксированы

Каждое выражение BEGIN TRANSACTION увеличивает на 1 переменную @@TRANCOUNT, каждый COMMIT уменьшает её на 1 (в новой сессии @@TRANCOUNT = 0). ROLLBACK присваивает переменной значение 0 (кроме ROLLBACK к точке сохранения). Это значит, что при @@TRANCOUNT > 0 в сессии есть активные транзакции. Более того, эта переменная способна идентифицировать вложенные транзакции. Если, например, @@TRANCOUNT = 3, это значит, что в текущем соединении три активных транзакции.

Таким образом, если сравнивать два способа, то @@TRANCOUNT не умеет определять незафиксированные транзакции, а XACT_STATE() не сможет посчитать, сколько активных запросов выполняется, определив только факт их наличия.

10. Какие уровни изоляции транзакций предотвращают коллизии (проблема параллельного доступа) и как им это удаётся? Иными словами, каков наивысший уровень изоляции в SQL Server и чем сами уровни отличаются друг от друга?

В MS SQL Server существует пять уровней изоляции. Причем только два из них справляются с грязным чтением, неповторяемыми операциями чтения и фантомами. По совместительству именно эти уровни являются наивысшими — SERIALIZABLE и SNAPSHOT ISOLATION.

Уровень READ UNCOMMITTED допускает грязное чтение, READ COMMITTED не защищает от повторного чтения в рамках одной транзакции. REPEATABLE READS пропускает фантомное чтение.

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

Оба уровня изоляции обеспечивают защиту по-разному. SERIALIZABLE использует блокировку и тем самым ограничивает дополнительные обращения к данным. SNAPSHOT ISOLATION, согласно названию, хранит версию строк, актуальную на момент выполнения транзакции. Тем самым этот уровень позволяет обращаться к данным другим транзакциям, но увеличивает нагрузку на tempdb.

Итоги

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

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

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

[customscript]techrocks_custom_after_post_html[/customscript]

[customscript]techrocks_custom_script[/customscript]

10 вопросов для T-SQL разработчиков или секреты лаконичного собеседования