Microsoft SQL Server. Особенности написания запросов на T-SQL

Microsoft SQL Server. Особенности написания запросов на T-SQL

Автор — Sergey Syrovatchenko

Все начиналось довольно обыденно… Зачитывался Рихтером и усиленно штудировал Шилдта. Думал, что буду заниматься разработкой под .NET, но судьба на первом месяце работы распорядилась иначе. Один из сотрудников неожиданно покинул проект и во вновь образовавшуюся дыру докинули свежего людского материала. Именно тогда и началось мое знакомство с SQL Server.

С тех пор прошло чуть меньше 6 лет и вспомнить можно многое…

Про бывшего клиента Джозефа из Англии, который переосмыслил жизнь, за время отпуска в Таиланде, и в моем скайпе стал подписываться Жозефиной. Про веселых соседей по офису, с которыми приходилось сидеть в одной комнате: один страдал от аллергии на свежий воздух, а другой маялся от неразделенной любви к С++ дополняя это аллергией на солнечный свет. Один раз по команде свыше пришлось на время стать Александром отцом двух детей, чтобы изображать из себя обросшего скилами сениора по JS.

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

К чему это было рассказано? Если хотите посвятить свою жизнь работе с базами данных, то первое чему нужно научиться… так это стрессоустойчивости. Второе – это взять на вооружение несколько правил при написании запросов на T-SQL, которые многие из начинающих разработчиков не знают или попросту игнорируют, а потом сидят и ломают голову… почему что-то не работает?

1. Data Types

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

Выполним запрос и посмотрим в чем разница:

В первом случае, типы данных более избыточные, чем могли бы быть. Зачем хранить битовый признак как строку YES/NO? Зачем хранить дату как строку? Зачем BIGINT по таблице с сотрудниками? Чем простой INT не подошел?

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

2. *

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

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

3. Alias

Создадим таблицу:

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

И все будет работать, как мы ожидаем, до тех пор, пока кто-то не захочет переименовать столбец в таблице Sales.UserCurrency:

Выполним запрос и увидим, что возвращается не 1 строка, а все которые есть в Sales.Currency. При построении плана выполнения SQL Server на этапе биндинга посмотрит на столбцы Sales.UserCurrency не найдет там CurrencyCode и подумает что этот столбец относится к таблице Sales.Currency после чего оптимизатор условие CurrencyCode = CurrencyCode отбросит.

Мораль — используйте алиасы:

 

4. Column order

Предположим у нас есть какая-то таблица:

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

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

И данные будут уже вставляться не в те столбцы в которые ожидает разработчик. Поэтому всегда рекомендуется явно указывать столбцы в конструкции INSERT:

Есть еще один интересный пример:

По какому столбцу будет идти сортировка? А все зависит от текущего порядка в таблице. Если кто-то его изменит, то и запрос будет выводить не то что мы ожидаем.

5. NOT IN vs NULL

Бесспорный лидер среди вопросов на собеседовании Junior DB Developer — конструкция NOT IN.

Например, нужно написать пару запросов: вернуть все записи из первой таблицы, которых нет во второй и наоборот. Очень часто начинающие разработчики не заморачиваются и используют IN и NOT IN:

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

При выполнении запроса с NOT IN мы не получим никаких результатов. Неужели какая-то магия вмешалась — IN работает, а NOT IN отказывается. Это первое, что нужно «понять и простить» при работе с SQL Server, который при операции сравнения руководствуется третичной логикой: TRUE, FALSE, UNKNOWN.

При выполнении SQL Server интерпретирует условие IN:

При сравнении любого значения с NULL возвращается UNKNOWN. 1=NULL, NULL=NULL. Результат будет один — UNKNOWN. А поскольку у нас в условии используется оператор AND, то все выражение вернет неопределенное значение и в результате будет пусто.

Написано немного скучно. Но важно понимать, что такая ситуация встречается достаточно часто. Например, раньше столбец был объявлен как NOT NULL, потом какой-то добрый человек разрешил записывать в нее NULL значение. Итог: у клиента перестает работать отчет после того, как в таблицу попадет хотя бы одно NULL значение.

Что делать? Можно явно отбрасывать NULL значения:

Можно использовать EXCEPT:

Если нет желания много думать, то проще использовать NOT EXISTS:

Какой вариант запроса более оптимальный? Предпочтительнее выглядит последний вариант с NOT EXISTS, который генерирует более оптимальный predicate pushdown оператор при доступе к данным из второй таблицы.

Вообще с NULL значениями много приколов. Можно поиграться с такими вот запросами:

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

Еще курьезнее выглядит ситуация с CHECK констрейнтами:

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

Все работает как мы ожидаем:

Но давайте вставим NULL:

Наш CHECK констрейнт не сработал, потому что для записи достаточно условия NOT FALSE, т.е. и TRUE и UNKNOWN подходят за милую душу. Есть несколько вариантов обойти эту особенность поведения: явно объявлять столбец как NOT NULL либо учитывать NULL в ограничении.

6. Date format

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

Скопировали результат, вставили его в запрос как есть и убрали время:

Корректно ли так делать?

Дата задается строковой константой, и в некоторой степени SQL Server позволяет вольности при ее написании:

Все значения практически везде однозначно интерпретируются:

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

Все эти варианты могут привести к неверному толкованию даты:

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

А у клиента, из-за разницы в настройках сервера, вот такой запрос будет приводить к проблемам:

Так в каком же формате задавать константы для дат? Давайте посмотрим на еще один пример:

В зависимости от установленного языка, константы также могут по-разному интерпретироваться:

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

Итого — остается последний вариант. Если хотите, чтобы константы с датами однозначно толковались в системе вне зависимости от настроек и фазы Луны, то указывайте их в формате YYYYMMDD без всяких тильд, кавычек и слешей.

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

В отличии от DATETIME, тип DATE корректно интерпретируется при различных настройках на сервере:

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

7. Date filter

Далее рассмотрим, как фильтровать эффективно данные. Почему-то на DATETIME/DATE столбцы приходится наибольшее число костылей, так что с этого типа данных мы и начнем:

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

Запрос вернет 0. Почему? При построении плана SQL Server пытается преобразовать строковую константу к типу данных столбца, по которому идет фильтрация:

Создадим индекс:

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

Или задать диапазон:

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

Поле PostTime ранее не входило в индекс, и особого эффекта от использования «правильного» подхода при фильтрации мы бы не смогли увидеть. Другое дело, когда нам нужно вывести данные за месяц. Чего только не приходилось видеть:

И опять же, последний вариант более приемлем, чем все остальные:

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

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

8. Сalculation

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

Если взглянуть на планы выполнения, то в первом случае SQL Server приходится выполнить IndexScan:

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

9. Convert implicit

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

В первом случае — предупреждение и IndexScan, во втором — IndexSeek:

Что произошло? Столбец NationalIDNumber имеет тип данных NVARCHAR(15). Константу, по значению которой необходимо отфильтровать данные, мы передаем как INT и в итоге получаем неявное преобразование типов, которое может снижать производительность. Такое очень часто происходит, когда кто-то меняет тип данных на столбце, но при этом запросы остаются прежними.

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

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

И в итоге у нас генерируются не совсем оптимальные запросы:

Решение проблемы достаточно простое — нужно контролировать, чтобы типы данных при сравнении совпадали.

10. LIKE & Suppressed index

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

Мы получим такие логические чтения:

Планы выполнения, по которым быстро можно найти победителя:

Результат является итогом, о чем мы так долго говорили до этого. Если есть индекс, то на нем не должно быть никаких вычислений и преобразований типов, функций и прочего. Только тогда он будет эффективно использоваться SQL Server.

Но что если нужно найти все вхождения подстроки в строку? Это задачка уже явно интереснее:

Но сначала нам нужно узнать много чего занимательного про строки и их свойства.

11. Unicode vs ANSI

Первое, что нужно помнить — строки бывают UNICODE и ANSI. Для первых предусмотрены типы данных NVARCHAR/NCHAR (по 2 байта на символ — увы UTF8 не завезли). Для хранения ANSI строк — VARCHAR/CHAR (1 байт — 1 символ). Есть еще TEXT/NTEXT, но про них лучше забыть изначально (потому что при их использовании можно существенно снизить производительность).

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

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

Если не указывать N перед константой, то SQL Server будет пытаться искать подходящий символ в ANSI кодировке. Если не найдет, то подставит знак вопроса.

12. COLLATE

Вспомнился один очень интересный пример, который любят спрашивать при собеседовании на позицию Middle/Senior DB Developer. Вернет ли данные следующий запрос?

И да… и нет… Тут как повезет. Обычно я так отвечаю.

Почему такой неоднозначный ответ? Во-первых, перед строковым константами не стоит N, поэтому они будут толковаться как ANSI. Второе — очень многое зависит от текущего COLLATE, который является набором правил при сортировки и сравнении строковых данных.

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

Стоит нам поменять COLLATE на какой-нибудь другой:

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

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

Что еще я забыл упомянуть про строки? Еще один хороший вопрос из цикла «давайте проведем собеседование»:

Эти строки равны? И да… и нет… Опять ответил бы я. Если мы хотим однозначного сравнения, то нужно явно указывать COLLATE:

Потому что COLLATE могут быть как регистрозависимыми (CS), так и не учитывать регистр (CI) при сравнении и сортировке строк. Разные COLLATE у клиента и на тестовой базе — это потенциальный источник не только логических ошибок в бизнес-логике.

Еще веселее, когда COLLATE между целевой базой и tempdb не совпадают. Создадим базу с COLLATE, отличным от дефолтного:

При создании таблицы COLLATE наследуется от базы данных. Единственное отличие — для первой временной таблицы, для которой мы явно определяем структуру без указания COLLATE. В этом случае она наследует COLLATE от базы tempdb.

Сейчас остановимся на нашем примере с #t1, потому что если COLLATE не совпадают — это может привести к потенциальным проблемам.

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

Либо SQL Server будет ругаться на невозможность соединения таблиц из-за различающихся COLLATE:

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

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

 

13. BINARY COLLATE

Теперь, когда «ложка дегтя» пройдена, посмотрим, как можно использовать COLLATE с пользой для себя. Помните пример про поиск подстроки в строке?

Данный запрос можно существенно оптимизировать и сократить время его выполнения.

Но для того, чтобы была видна разница, нам нужно сгенерировать большую таблицу:

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

Выполняем фильтрацию:

И можем увидеть результаты выполнения, которые приятно удивят:

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

14. Code style

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

Давайте создадим отдельную базу и таблицу в ней:

и напишем такой запрос:

Работает? Теперь попробуйте поменять COLLATE на какой-нибудь регистрозависимый:

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

Оптимизатор использует правила текущего COLLATE при построении плана выполнения. Точнее, на этапе биндинга, когда производится проверка на существование таблиц, колонок и других объектов и сопоставление каждого объекта синтаксического дерева с реальным объектом системного каталога.

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

Еще интереснее обстоят дела с переменными…

Для них COLLATE наследуются от базы master. Поэтому нужно соблюдать правильный регистр при работе с переменными:

То ошибки скорее всего не будет:

При этом на другом сервере ошибка в регистре может дать о себе знать:

 

15. [var]char

Не секрет, что есть строчные типы данных с фиксированной (CHAR, NCHAR) и переменной длиной (VARCHAR, NVARCHAR):

Если строка имеет фиксированную длину скажем в 20 символов, но в нее записали только 4, то в этом случае SQL Server автоматически добавит 16 пробелов справа (при этом обратите внимание функции LEN и DATALENGTH ведут себя по-разному):

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

Другое дело оператор LIKE:

Пробелы у правого операнда всегда учитываются при сравнении.

16. Data length

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

В чем суть данной проблемы? Явно не указали размерность типа и вместо дробного значения получаем «вроде целое»:

Со строками все еще веселее:

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

При этом поведение преобразовании типов имеет свою особенность: не указали размерность в CAST/CONVERT, то браться будут первые 30 символов.

17. ISNULL vs COALESCE

Что еще потенциально интересного можно показать? Есть две функции: ISNULL и COALESCE. С одной стороны все просто — если первый оператор NULL, то вернуть второй оператор или следующий, если мы говорим про COALESCE. С другой стороны, есть коварное различие между ними.

Что вернут эти функции?

Ответ и вправду не очень очевидный:

Почему? Функция ISNULL преобразует к наименьшему типу из двух операндов. COALESCE преобразует к наибольшему типу. Вот мы и получаем такую радость, над которой я в первый раз очень долго просидел в попытках понять, «что не так».

С точки зрения производительности, ISNULL будет немного быстрее отрабатывать в ряде случае, COALESCE же раскладывается в CASE WHEN оператор о котором поговорим ниже.

18. Math

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

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

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

Что вернет запрос? COUNT(*)/COUNT(1) вернет общее число строк. COUNT по столбцу вернет количество не NULL строк. Если добавить DISTINCT, то количество уникальных значений, которые не NULL.

Интереснее с подсчетом среднего. Операция AVG раскладывается оптимизатором на SUM и COUNT. И тут мы вспомним про пример выше — при подсчете среднего не будут учитываться NULL. Кроме того, если значения целочисленные, то какой будет результат? Целочисленный. Об этом часто забывают.

19. UNION vs UNION ALL

Тут все просто: если мы знаем, что данные не пересекаются, и нас не волнуют дубликаты, то, с точки зрения производительности, предпочтительнее использовать UNION ALL. Если нужно убрать дублирование, то смело используем UNION.

Например, в случае когда дубликатов точно не будет лучше использовать UNION ALL:

Еще важно знать об интересном различии между этими двумя конструкциями: оператор UNION выполняется параллельно, а UNION ALL — последовательно. И это не относится к параллельным планам, просто это такая особенность доступа к данным, которая может помочь при оптимизации.

Предположим, нам нужно вернуть 1 строку, исходя из разного набора условий:

Тогда за счет использования OR в условии у нас будет IndexScan:

Перепишем запрос с использованием UNION ALL:

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

 

20. Re-read

Очень часто доводилось видеть ситуацию, когда данные можно вытащить с помощью одного JOIN при этом в запросе гордилось куча подзапросов:

Ведь чем меньше идет лишних обращений к таблице — тем меньше логических чтений:

 

21. SubQuery

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

Давайте предположим что раньше между таблицами Person.Person и Sales.SalesPersonQuotaHistory была такая связь, получалось что для одного сотрудника существовала максимум одна запись по размеру квоты.

На сервере клиента может быть по-другому и тогда этот запрос приведет к такой ошибке:

Как решаются подобные проблемы?

Добавляется TOP(1) и ORDER BY, и проблема ушла. Однако не все так просто, как может показаться. Использование операции TOP заставляет оптимизатор форсировать использование IndexSeek. К таким же последствиям приводит использованием OUTER/CROSS APPLY вместе с TOP:

При их выполнении будет возникать одна и та же проблема — множественные IndexSeek операции:

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

И посмотрим что изменилось:

 

22. CASE WHEN

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

SQL Server будет раскладывать выражение до такого вида:

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

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

Настроим SQL Profiler на отображение событий SQL:StmtStarting / SP:StmtCompleted (если хочется сделать этого с помощью XEvents: sp_statement_starting / sp_statement_completed).

Выполним запрос:

Функция выполнится 10 раз. Теперь уберем комментарий с условия выше:

Функция выполнится уже 20 раз. Суть в том, что выражение в CASE не обязательно быть функцией. Это может быть какой-то сложный расчет. За счет того, что CASE раскладывается — это может привести к многократному вычислению одних и тех же операторов.

Бороться с этим можно с помощью вложенных запросов:

Функция выполнится 10 раз.

Кроме того, нужно стараться не нагружать CASE оператор дубликатами:

Хоть выражения в CASE и вычисляется последовательно (именно в том порядке как мы написали). В некоторых случаях этот оператор будет выполняться SQL Server с агрегированных функций:

 

23. Scalar func

Специально для любителей ООП — не используйте скалярные функции в запросах на T-SQL, которые оперируют большим числом строк.

Вот пример из жизни, которым я когда-то страдал, когда еще не знал о потенциальных минусах скалярных функций:

Запросы возвращают идентичные данные:

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

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

Во всех ли случаях скалярные функции — это зло? Нет. Можно создать функцию с опцией SCHEMABINDING и не использовать входящих параметров:

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

24. VIEWs

Кто-то любит представления… кто-то нет. Навязывать мнение не использовать вью – себе дороже, но знать про несколько особенностей при работе с ними нужно обязательно.

Создаем тестовую таблицу и вью на основе нее:

Значения возвращаются правильно:

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

Получим тот же результат:

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

чтобы получить правильный результат:

При прямом обращении к таблице подобного прикола не будет.

Есть любители в одном запросе соединить все данные и обернуть это все в одном вью. За примером далеко ходить не будем, и посмотрим на «хороший паттерн» из AdventureWorks:

А теперь вопрос… что если мне нужно получить не всю информацию, а только ее часть? Например, вернуть имя и фамилию работников:

Посмотрим на план выполнения в случае использования вью:

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

Оптимизатор в SQL Server сделали весьма умным и на этапе упрощения дерева операторов, при построении плана выполнения, он умеет отбрасывать неиспользуемые соединения.

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

25. CURSORs

При работе с SQL Server запомните одну истину — не используйте курсоры для итерационной модификации данных. Это не Oracle!

Часто можно встретить такой вот код:

Этот код можно переписать вот так:

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

Курсоры на SQL Server — зло!

26. STRING_CONCAT

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

Я бы конечно мог посоветовать использовать функцию STRING_CONCAT, если бы она была… На дворе 2016 год, а отдельной функции для конкатенации строк, в SQL Server так и не добавили. Нужно же как-то выходить из положения?

Создадим тестовую таблицу:

и начнем с моего «любимца» — конкатенация строк через присваивание значений в переменную:

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

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

На практике, в 90% случаев достаточно использовать XML:

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

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

Его можно сделать менее затратным:

Но сути это кардинально не изменит. Теперь пробуем не использовать метод value:

И такой вариант будет работать хорошо и быстро, если не одно «но». Попробуйте выполнить вот такой запрос:

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

В итоге у нас два варианта: если спецсимволов нет, то использовать вариант запроса без метода value, в противном случае обратить внимание на более ресурсоемкий план с value(‘(./text())[1]’….

27. SQL Injection

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

Сформируется вот такой запрос:

Если перед выполнением дописать к параметру что-то лишнее:

то запрос будет уже такой:

Это и есть атака, известная как sql injection, когда вместе с запросом можно будет выполнить что-то «лишнее». Тут как повезет — лишь бы прав хватило :)

Если в коде запрос формируется с помощью String.Format (или вручную), то это потенциальное место, где может возникнуть проблема с sql injection:

Другое дело, когда используется sp_executesql и параметры:

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

В коде это будет выглядеть так:

Теперь точно нужно сделать паузу, иначе материал рискует стать нечитабельным…

Краткие итоги по 38 страницам текста расположенного выше

Работа с базой данных — это не всегда сырок моцарелла и смузи после 8-часового дня. Есть много аспектов, на которые нужно обращать внимание при написании запросов на T-SQL. Тут я попытался собрать часть граблей, на которые сам в свое время наступал.

Безусловно, это не исчерпывающий список «подводных камней» при работе с SQL Server, но все же я надеюсь, что данный материал будет кому-то полезным. В дальнейшем я постараюсь по мере сил пополнять этот пост новыми примерами.

Если будут вопросы, конструктивные предложения и разумная критика, то все контакты в профиле.

Что осталось за бортом?

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

Кроме того, хотел бегло рассказать про parameter sniffing, но лучше не изобретать велосипед и привести ссылку на отличный пост от Дмитрия Пилюгина: Медленно в приложении, быстро в SSMS.

Запись опубликована в рубрике В помощь администратору с метками . Добавьте в закладки постоянную ссылку.

3 комментария на «Microsoft SQL Server. Особенности написания запросов на T-SQL»

  1. vg_grina говорит:

    Спасибо за статью! Я только сегодня зарегистрировался на сайте и,сл., статью прочитал только сегодня.

  2. Sergeich1984 говорит:

    Очень полезная статья, Спасибо!
    Хочу добавить только по 17. ISNULL vs COALESCE
    Функция ISNULL преобразует к типу первого операнда:

    DECLARE @i int = 7

    DECLARE @a decimal(10,5) = null
    SELECT ISNULL(@a, @i)

    DECLARE @b char(10) = NULL
    SELECT ISNULL(@b, @i), DATALENGTH(ISNULL(@b, 7))

  3. Sergeich1984 говорит:

    Список приоритетов типов:
    https://msdn.microsoft.com/ru-ru/Library/ms190309(v=sql.120).aspx

Добавить комментарий

Войти с помощью: