SQL Tips & Tricks

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

  • Получение изменившихся данных без триггеров
  • Select vs Set для установления переменных
  • Забытое дополнение для TOP

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

Получение изменений без триггеров

Действительно большинство пользователей MS SQL Server думают, что триггеры это единственное место где можно использовать виртуальные таблицы Inserted и Deleted, с помощью которых можно получить данные об измененных данных в результате выполнения запросов с Insert, Update или Delete. Но, как вы уже наверно догадались, это не так.

Любые изменения в данных, которые проводятся с помощью DML операций могут быть «захвачены» в специальные виртуальные таблицы упомянутые выше. Для этого в запросе надо использовать служебное слово output и перечислять поля которые вы хотите записать в нужную таблицу.

Исходный материал:

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

Update

Delete

 

Insert

 

Select vs. Set

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

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

Представим, что у нас есть таблица со следующими значениями:

  • Foo
  • Bar
  • Baz

Попробуем запустить следующий скрипт:

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

и вы окажетесь не правы, так как ответ будет:

Почему так произошло, отчего нет null во второй строке? Все дело в том, что второй запрос в результате выдает 0 строк, что приводит к тому что переменная никак не была затронута. Результата нет, вывода нет, переменная не трогалась.

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

Исправить ситуацию можно следующим образом:

Когда используется служебное слово SET, то значение переменной всегда переписывается. Если запрос не возвращает никаких данных, то значение переменной обнулится. Таким образом получается, что использование SET безопаснее, чем использование SELECT, если вы планируете использовать проверки переменной на null.

Top … with ties

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

Дополнение with ties позволяет вернуть все подходящие записи, несмотря на ограничение по количеству записей указанное в записи TOP. Может использоваться только для выборки и если применяется сортировка записей.

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

Результат, как и ожидали будет такой

Однако запрос можно переписать более компактно:

 

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

 

Итого

Надеюсь, что эти данные оказались для вас полезными и вы найдете им применение в вашей повседневной работе. Как минимум будет темя для обсуждения с коллегами =)

 

Hard’n’heavy!

 

Оставить комментарий