🗄️ Вопросы интервью по SQL

Ответ: Оператор EXCEPT используется для возврата строк из результирующего набора первого запроса, которые отсутствуют в результирующем наборе второго запроса. Эффективно выполняет разность множеств, показывая только данные, уникальные для первого запроса.

SELECT column_list FROM Table1
EXCEPT
SELECT column_list FROM Table2;
                

SELECT ProductID FROM ProductsSold
EXCEPT
SELECT ProductID FROM ProductsReturned;
                

• Найти расхождения между наборами данных
• Проверить что определённые данные существуют в одном наборе, но не в другом

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

Ответ: Constraints обеспечивают правила, которые должны соблюдаться данными, предотвращая невалидные или несогласованные данные:

• NOT NULL: Гарантирует, что столбец не может содержать NULL значения
• UNIQUE: Гарантирует, что все значения в столбце различны
• PRIMARY KEY: Объединяет NOT NULL и UNIQUE, гарантируя уникальную идентификацию
• FOREIGN KEY: Гарантирует ссылочную целостность, требуя чтобы значения совпадали с первичным ключом другой таблицы
• CHECK: Валидирует что значения соответствуют определённому критерию (например, CHECK (Salary > 0))

Автоматически применяя эти правила, constraints поддерживают надёжность и консистентность данных в БД.

• Префикс: # (например, #TempTable)
• Существует только в текущей сессии
• Автоматически удаляется при завершении сессии

• Префикс: ## (например, ##GlobalTempTable)
• Видна всем сессиям
• Удаляется только когда все сессии, ссылающиеся на неё, закрыты

-- Local Temporary Table
CREATE TABLE #LocalTemp (ID INT);

-- Global Temporary Table
CREATE TABLE ##GlobalTemp (ID INT);
                

Ответ: MERGE statement объединяет множественные операции INSERT, UPDATE и DELETE в одну. Используется для синхронизации двух таблиц:

• Вставляет строки, которые не существуют в целевой таблице
• Обновляет строки, которые уже существуют
• Удаляет строки из целевой таблицы на основе условий
• В SQL Server, MERGE может вызвать проблемы конкурентности (race conditions или deadlocks)

MERGE INTO TargetTable T
USING SourceTable S
ON T.ID = S.ID
WHEN MATCHED THEN
    UPDATE SET T.Value = S.Value
WHEN NOT MATCHED THEN
    INSERT (ID, Value) VALUES (S.ID, S.Value);
                

SELECT Column1, MAX(Column2)
FROM TableName
GROUP BY Column1;
                

WITH CTE AS (
    SELECT
        Column1,
        Column2,
        ROW_NUMBER() OVER (
            PARTITION BY Column1
            ORDER BY Column2
        ) AS RowNum
    FROM TableName
)
SELECT *
FROM CTE
WHERE RowNum = 1;
                

Ответ: ACID гарантирует что транзакции БД надежны и поддерживают целостность данных.

• Atomicity (Атомарность): Транзакция выполняется полностью или не выполняется совсем. Если одна операция не удаётся, все изменения откатываются. Пример: передача денег либо успешна либо отменена полностью
• Consistency (Консистентность): БД остаётся валидной, следуя всем правилам и constraints. Пример: количество товара не может быть отрицательным
• Isolation (Изоляция): Множественные транзакции выполняются независимо без влияния друг на друга. Пример: два пользователя не могут забронировать одно место одновременно
• Durability (Долговечность): После коммита, изменения сохранены постоянно, даже при системном сбое. Пример: подтверждённый заказ остаётся сохранённым после перезагрузки сервера

Ответ: Уровни изоляции контролируют как транзакции взаимодействуют и поддерживают консистентность данных.

• Read Uncommitted: Позволяет читать невыполненные данные, что может вызвать грязные чтения
• Read Committed: Читает только выполненные данные, предотвращая грязные чтения
• Repeatable Read: Гарантирует что одинаковые данные остаются неизменными во время транзакции, предотвращая грязные и неповторяемые чтения
• Serializable: Наивысший уровень изоляции, предотвращающий грязные чтения, неповторяемые чтения и фантомные чтения, но может снизить производительность из-за увеличенной блокировки

Ответ: Подсказка WITH (NOLOCK) позволяет запросу читать данные без получения общих блокировок, эффективно читая невыполненные данные.

SELECT *
FROM Orders WITH (NOLOCK);
                

• Улучшает производительность путём снижения конфликтов блокировок, особенно на больших таблицах, часто обновляемых

• Результаты могут быть несогласованны или ненадежны, так как читаемые данные могут измениться или откатиться

Ответ: Deadlock возникает когда две или более транзакции ждут друг друга, чтобы освободить ресурсы.

• Deadlock Detection & Retry: БД обнаруживает deadlock, прерывает одну транзакцию и повторяет её позже
• Reduce Lock Contention: Используйте индексы, оптимизируйте запросы и сокращайте длительность транзакций
• Choose Appropriate Isolation Levels: Выберите уровни изоляции, которые балансируют консистентность и блокировку
• Consistent Resource Ordering: Доступу к ресурсам в одинаковом порядке во всех транзакциях, чтобы избежать циклических ожиданий

DECLARE @retry_count INT = 0;
DECLARE @max_retries INT = 3;

WHILE @retry_count < @max_retries
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
        -- SQL операции
        COMMIT TRANSACTION;
        BREAK;
    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() = 1205  -- Deadlock error
        BEGIN
            SET @retry_count = @retry_count + 1;
            ROLLBACK TRANSACTION;
            WAITFOR DELAY '00:00:00.100';  -- Подождать перед повтором
        END
        ELSE
        BEGIN
            THROW;
        END
    END CATCH
END
                

Ответ: Database Snapshot — это read-only копия БД на определённый момент времени.

• Отчёты на согласованном наборе данных
• Восстановление в определённый момент времени
• Тестирование без влияния на оригинальную БД

CREATE DATABASE MySnapshot ON
(
    NAME = MyDatabase_Data,
    FILENAME = 'C:\Snapshots\MyDatabase_Data.ss'
)
AS SNAPSHOT OF MyDatabase;
                

• Read-only копия БД
• Не требует отдельного резервного копирования
• Использует разреженные файлы для экономии дискового пространства
• Можно использовать для сравнения данных между моментами времени