Что такое хранимые процедуры в SQL?
Что проверяют
Понимаете ли вы, что хранимая процедура это код внутри СУБД со своими плюсами и ценой, а не волшебный способ ускорить запросы. Следом почти всегда просят показать вызов из C# и спрашивают, чем процедура отличается от функции.
Как отвечать
Хранимая процедура это именованный набор SQL-инструкций, который хранится на сервере и вызывается по имени: в SQL Server через EXEC, в PostgreSQL через CALL (там процедуры появились в версии 11, раньше были только функции). Процедура принимает входные и OUTPUT-параметры, может возвращать несколько наборов строк и управлять транзакцией.
Чем полезна:
- права: пользователю выдают
EXECUTEна процедуру без прямого доступа к таблицам; - меньше обменов по сети: несколько операций выполняются за один вызов;
- общая логика: одна процедура обслуживает несколько приложений или отчётов.
Вызов из C#: SqlCommand с CommandType.StoredProcedure и параметрами через SqlParameter (пакет Microsoft.Data.SqlClient), в Dapper аргумент commandType: CommandType.StoredProcedure, в EF Core FromSqlInterpolated или ExecuteSqlInterpolated.
Отличие от функции в SQL Server: функция возвращает значение или таблицу, используется внутри SELECT и не может менять данные. Процедура вызывается отдельно и может делать всё.
Цена: логика размазывается между кодом и базой, её сложнее тестировать, версионировать и переносить на другую СУБД. Масштабировать процессор базы дороже, чем добавить ещё один экземпляр сервиса.
Где спотыкаются
Повторяют миф «процедуры быстрее, потому что прекомпилированы». План кэшируется и для обычных параметризованных запросов, так что выигрыш обычно в числе сетевых обменов, а не в компиляции.
Считают, что процедура сама защищает от SQL-инъекций. Если внутри склеивается строка и выполняется через EXEC, дыра остаётся; нужен sp_executesql с параметрами.
Не знают про parameter sniffing: план строится под первые переданные значения и может оказаться плохим для остальных. Лечат OPTION (RECOMPILE) или OPTIMIZE FOR.