Являются ли хранимые процедуры CLR предпочтительными над хранимыми процедурами TSQL в SQL 2005+?

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

Однако недавно мне пришлось отлаживать некоторые очень плохо написанные TSQL хранимые процедуры. Как обычно, я обнаружил множество проблем из-за того, что первоначальный разработчик разработчика не имел реального опыта TSQL, они были сосредоточены на ASP.NET / C #.

Таким образом, использование процедур CLR, во-первых, обеспечит гораздо более знакомый набор инструментов для этого типа разработчиков, а во-вторых, средства отладки и тестирования более мощные (например, Visual Studio вместо SQL Management Studio).

Мне было бы очень интересно услышать ваш опыт, поскольку, похоже, это не простой выбор.

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

Хранимые процедуры CLR не предназначены для замены запросов на основе набора. Если вам нужно запросить базу данных, вам все равно придется поместить SQL в ваш код CLR, как если бы он был встроен в обычный код. Это будет пустой тратой усилий.

Хранимые процедуры CLR предназначены для двух основных целей: 1) взаимодействие с ОС, например чтение из файла или отбрасывание сообщения в MSMQ, и 2) выполнение сложных вычислений, особенно когда у вас уже есть код, написанный на языке .NET, для сделайте расчет.

Хостинг CLR в SQL Server предназначен для предоставления разработчикам баз данных более гибких опций в том, как они стремились выполнять задачи. Как и многие другие, SQL отлично подходит для операций и модификаций наборов данных . Любой, кто сделал обширную крупную разработку приложений со сложными правилами бизнеса / домена, скорее всего скажет вам – попытка принудительного применения некоторых из этих правил с использованием чистого SQL (несколько раз в одном макропроцессовом) может стать поистине кошмаром.

Есть только определенные задачи, которые лучше обрабатываются в процедурной или OO-моде. Имея выбор использования .NET-кода для разбивки последовательности логики, операции запроса могут быть легче читать и отлаживать. Воспользовавшись хранимыми процедурами CLR, я могу сказать, что вы перешагиваете с помощью отладчика, что облегчает отслеживание того, что происходит на уровне базы данных.

Только один пример, мы часто используем хранимые процедуры CLR здесь как «шлюз» для динамических поисковых запросов. Произнесите запрос поиска, который может содержать до 30 различных параметров поиска. Пользователи, очевидно, не используют все 30 из них, поэтому переданная структура данных будет иметь 30 параметров, но в основном DBNULL. Клиентская сторона не имеет возможности генерировать динамическую инструкцию по очевидным соображениям безопасности. Получающееся в результате динамическое заявление создается внутренне, не опасаясь внешних «дополнительных».

В общем, вы используете CLR, если у вас есть что-то, что не нужно связывать с базой данных. Итак, скажем, вы разбираете или декодируете значение. Это проще сделать в CLR, а затем вернуть значение.

Попытка выполнить запрос compelx в CLR – это просто не выход.

Кстати, это тоже не изменилось в 2008 году.

Я считаю, что эти два не эквивалентны … подходят друг к другу.
Предполагается, что интеграция с CLR должна прекратить «расширенные хранимые процедуры». У нас есть некоторые из них на нашем рабочем месте … по существу, блоки обработки / логики над SQL-данными, которые были слишком сложными / невозможными для выполнения с помощью обычных хранимых процедур SQL / T SQL. Поэтому они написали это как расширенные хранимые процедуры в C ++ DLL, которые можно вызвать аналогичным образом. Теперь они были постепенно прекращены, а интеграция CLR – замена

  • DB Хранимые процедуры: если это можно сделать в T SQL Stored procs, сделайте это.
  • CLR Хранимые процедуры: если логика слишком сложна или утомительна для выполнения через T SQL … если ее что-то, что займет меньше строк кода CLR для ее устранения (манипуляции с строкой, сложная / пользовательская сортировка или фильтрация и т. Д.), Используют это подход.

Помимо доступа к файловой системе (где CLR procs имеет очень выраженное преимущество), я бы использовал процедуры T-SQL. Если у вас есть особенно сложные вычисления, вы можете поместить эту часть в функцию CLR и вызвать ее из своего proc (udf – это то место, где я обнаружил, что интеграция CLR действительно светит). Затем вы получаете преимущества интеграции CLR для этой части своей задачи, но сохраняете как можно больше своей сохраненной логики proc в БД.

Учитывая то, что вы сказали, я предпочел бы, чтобы вы получили должным образом подготовленные девелоперы в t-SQl и базы данных, а не позволяли им создавать значительно больший урон производительности, позволяя им выполнять задачи t-sql в CLR. Разработчики, которые не понимают базы данных, используют это как оправдание, чтобы не делать то, что лучше для производительности базы данных, потому что они хотят воспринимать то, что они считают более легким маршрутом.

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

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

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

На странице электронной документации по SQL Server по этому предмету перечислены следующие преимущества:

  • Лучшая модель программирования. Языки .NET Framework во многом богаче Transact-SQL, предлагая конструкции и возможности, ранее недоступные разработчикам SQL Server. Разработчики могут также использовать возможности библиотеки .NET Framework, которая предоставляет обширный набор классов, которые могут быть использованы для быстрого и эффективного решения проблем программирования.

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

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

  • Оптимизированная разработка через стандартизованную среду. Разработка баз данных интегрирована в будущие выпуски среды разработки Microsoft Visual Studio .NET. Разработчики используют те же инструменты для разработки и отладки объектов и сценариев баз данных, которые используются для написания компонентов и служб среднего уровня или клиентского уровня .NET Framework.

  • Потенциал для повышения производительности и масштабируемости. Во многих ситуациях модели компиляции и исполнения языка .NET Framework обеспечивают улучшенную производительность по сравнению с Transact-SQL.

Мы столкнулись с ситуацией с функцией CLR, которая была вызвана тысячами раз в обычном SQL-процессе. Это был процесс импорта данных из другой системы. Функция достоверно подтвердила данные и обработала нули.

Если мы выполнили операцию в TSQL, процесс завершился примерно через 15 секунд. Если мы использовали функцию CLR, процесс завершился через 20 – 40 минут. Функция CLR выглядела более изящной, но, насколько мы могли судить, для каждого использования функции CLR был начальный удар. Поэтому, если у вас есть большая операция, выполненная с использованием одной функции CLR, это нормально, так как время запуска мало по сравнению с временем выполнения операции. Или, если вы вызываете функцию CLR на небольшое количество раз, общее время запуска для всех вызовов функции будет небольшим. Но будьте осторожны с петлями.

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

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

  • Заменять и расширять базовые запросы, не запросы и скалярные SQL-функции.
    A) Отчеты об ошибках и предупреждения могут быть интегрированы на основе определенных требований. B) Легко определить уровни отладки. C) Включить более простой способ взаимодействия с иностранными SQL-серверами
  • Перемещение устаревшего кода в управляемую среду.

Я отправил следующий ответ на аналогичный вопрос: Преимущество 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: Что такое SQLCLR? (бесплатную регистрацию требуется для чтения статей), в которой рассматривается этот вопрос. Основы (подробнее см. Связанную статью):

  • Потоковые табличные функции (sTVF)
  • Динамический SQL (внутри функций)
  • Улучшенный доступ к внешним ресурсам / Замена xp_cmdshell
    • Передача данных проще
    • Легче получить несколько столбцов набора результатов
    • Нет внешних зависимостей (например, 7zip.exe)
    • Лучшая безопасность через олицетворение
  • Возможность многопоточной
  • Обработка ошибок (внутри функций)
  • Пользовательские агрегаты
  • Пользовательские типы
  • Изменить состояние (в пределах функции и без OPENQUERY / OPENROWSET )
  • Выполнение хранимой процедуры (только для чтения, внутри функции и без OPENQUERY / OPENROWSET )
  • Производительность ( примечание: это не имеет значения во всех случаях, но определенно в некоторых случаях в зависимости от типа и сложности операции)
  • Может записывать вывод (т. RAISERROR что отправлено на вкладку «Сообщения» в SSMS) (например, PRINT и RAISERROR с серьезностью = от 0 до 10). Я забыл упомянуть об этом в статье ;-).

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

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

  • Создание метода таблицы для определенного пользователем типа (например, «узлы» в типе данных XML)
  • Сбой CLR при первом использовании из-за неиспользуемой переменной
  • Какая версия .NET Framework поддерживает SQL Server?
  • Как выполнить откат хранимой процедуры CLR SQL, когда пользователь отменяет запрос?
  • Чтение данных файла из пользовательского действия MSI для удаленной установки CLR
  • каковы возможные проблемы с интеграцией CLR в Sqlserver
  • СУБД CLR-функции
  • Как параметры передаются из SQL Server в хранимую процедуру на основе CLR?
  • Извлечение сборки .NET из SQL Server 2005
  • Медленные агрегаты CLR SQL Server
  • Как обновить, чтобы обновить C # CLR-код (сборку) в SQL Server?
  • Interesting Posts

    SQL Server: проблема с объявлением переменной в динамическом SQL

    Ошибка обработки ошибок ограничения базы данных

    Получить счет в соответствии с последней датой в SQL Server

    SQL Выберите XML-узел, основанный на значении атрибута узла-брата

    Экспорт базы данных с одного SQL Server на другой SQL Server

    как отключить autonumber в sql?

    Обновление столбца на основе первого появления текста в другом столбце

    Subsonic 3, SimpleRepository, SQL Server: как найти строки с нулевым полем?

    Не удается восстановить базу данных с помощью .bak-файла из-за другой версии SQL-сервера

    Ошибка представления SQL Server 2008 с аргументом case

    Сбой SQL Server с арифметическим переполнением

    Генерировать отчет SSRS исключительно с пользовательского ввода

    Объединение последовательных строк на основе столбца типа

    ALTER TABLE my_table ADD COLUMN column_name VARCHAR (50) ПОСЛЕ col_name не поддерживается в SQL Server 2000

    Запрос SQL Server с несколькими фильтрами

    Давайте будем гением компьютера.