Почему мы используем процедуры CLR

Представьте, что вы хотите проверить некоторые поля данных в SQL Server с помощью регулярного выражения. По сей день, даже в SQL Server 2008 R2, это практически невозможно, используя только код T-SQL.

Однако с небольшой помощью хранимой процедуры или хранимой функции CLR это было бы совсем несложно.

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

CLR очень сильна в других областях, таких как обработка строк и дат, вызов внешних служб (WCF, веб-службы).

Таким образом, хранимые процедуры T-SQL и хранимые процедуры CLR являются хорошим дополнением, каждая из которых решает определенный набор задач, с которыми другой не особенно хорош.

Есть несколько вещей, которые нельзя сделать в SQL Server (или, в некоторых случаях, они не выполняются так же хорошо, как в управляемом коде).

• В CLR есть RegEx .
• Вы можете позвонить в веб-службы.
• CLR имеет лучшую производительность (например, если вам приходилось много вычислять для каждой строки)
• Повторное использование кода
• Пишите на том языке, к которому вы привыкли (VB.Net, C # и т. Д.).

На аналогичный вопрос я отправил следующий ответ: Преимущество SQL SERVER CLR. Я добавлю здесь, учитывая, что что-то было упомянуто как минимум в двух ответах: C # / VB.net / etc, являющийся языком, с которым кому-то более комфортно, чем T-SQL, не должен не быть причиной использования SQLCLR через T-SQL. Если кто-то не знает, как чего-то добиться в T-SQL, сначала попросите помощи в поиске решения T-SQL. Если таковой не существует, затем перейдите по маршруту CLR.

Интеграция SQLCLR / CLR в SQL Server - это просто еще один инструмент, помогающий решить определенные (не все) проблемы. Есть несколько вещей, которые он делает лучше, чем то, что можно сделать в чистом T-SQL, а есть некоторые вещи, которые можно сделать только через SQLCLR. Я написал статью для SQL Server Central, Stairway to SQLCLR Level 1: What is SQLCLR? (для чтения статей необходима бесплатная регистрация), в котором рассматривается этот вопрос. Основы (подробности см. В связанной статье):

• Потоковые функции с табличными значениями (sTVF)
• Динамический SQL (в функциях и триггерах - доступ к таблицам inserted и deleted)
• Better Access to External Resources / Replace xp_cmdshell
  • Passing data in is easier
  • Получить несколько столбцов набора результатов проще
  • Никаких внешних зависимостей (например, 7zip.exe)
  • Повышенная безопасность за счет выдачи себя за другое лицо
• Возможность многопоточности
• Обработка ошибок (в функциях)
• Пользовательские агрегаты
• Пользовательские типы
• Изменить состояние (внутри функции и без OPENQUERY / OPENROWSET)
• Выполнение хранимой процедуры (только для чтения; внутри функции и без OPENQUERY / OPENROWSET)
• Performance (note: this is not meaning in all cases, but definitely in some cases depending on the type and complexity of the operation)
  • computations / string-manipulation
  • UDF SQLCLR, если они отмечены знаком DataAccess = None, могут использоваться в планах параллельного выполнения, тогда как UDF T-SQL предотвращают выполнение параллельных планов.
• Может захватывать вывод (т.е. то, что отправляется на вкладку «Сообщения» в SSMS) (например, PRINT и RAISERROR с серьезностью = от 0 до 10) - я забыл упомянуть об этом в статье ;-).

Еще одна вещь, которую следует учитывать, заключается в том, что иногда полезно иметь возможность совместно использовать код между приложением и БД, чтобы БД имела представление об определенной бизнес-логике без необходимости создавать настраиваемые экраны, предназначенные только для внутреннего использования, только для доступа к этому коду приложения. Например, я работал над системой, которая импортировала файлы данных от клиентов и использовала собственный хеш для большинства полей и сохраняла это значение в строке в базе данных. Это позволяло легко пропускать строки при повторном импорте их данных, поскольку приложение будет хэшировать значения из входного файла и сравнивать их с хеш-значением, хранящимся в строке. Если бы они были такими же, тогда мы сразу знали, что ни одно из полей не изменилось, поэтому мы перешли к следующей строке, и это было простое сравнение INT. Но этот алгоритм для выполнения хэша был только в коде приложения, поэтому, будь то для отладки случая клиента или поиска способов переложить некоторую обработку на серверные службы, помечая строки, в которых было хотя бы одно поле, с изменениями (изменения, поступающие из нашего приложения вместо того, чтобы искать изменения в новом файле импорта), я ничего не мог сделать. Это было бы прекрасной возможностью иметь довольно простую часть бизнес-логики в БД, даже если бы не для нормальной обработки; наличие того, что составляет закодированное значение в БД, без возможности понять его значение, значительно затрудняет решение проблем.

Если вы хотите увидеть некоторые из этих возможностей в действии без необходимости писать какой-либо код, можете воспользоваться бесплатной версией SQL # (автором которого я являюсь) имеет функции RegEx, настраиваемые агрегаты (UDA), настраиваемые типы (UDT) и т. д.

Что ж, если хотите:

• выполнять сложные операции с данными и
• хотите быть ближе к данным (т.е. не в ASP.NET или приложении Winforms), и
• вы бы предпочли писать код на C #, чем на SQL.

Это тот случай, когда вы используете процедуру CLR. У меня в голове нет примера, но его можно представить.

-- Редактировать:

Воображаемый может быть преобразованием данных в структуру типа хранилища данных. В этом случае вы можете переформатировать и провести небольшой анализ. Тогда может быть целесообразно сделать это в среде CLR. (Я не предлагаю, чтобы я поступил именно так, но это можно рассмотреть).

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

Вы также можете писать агрегатные функции с процедурами CLR, чего нельзя было сделать в T-SQL.

Конечно, для обработки данных хранимые процедуры T-SQL будут работать лучше, и их легче написать.