Существует множество мнений относительно использования/не использования префикса sp_ для хранимых процедур. В пользу не использования обычно приводят аргумент, что sp_ это префикс для системных хранимых процедур и может возникнуть путаница. А в пользу использования, что путаница не возникнет, т.к. какие могут быть системные хранимые процедуры в пользовательской базе данных. Заблуждение и первых, и вторых в том, что использование/не использование sp_, это не вопрос стиля именования. Это вопрос производительности.
Главная проблема в том, что использование префикса sp_ в базах отличных от master приводит к следующей проблеме. Если оптимизатор встречает хранимую процедуру с префиксом sp_ от сначала проверяет, а нет ли такой системной, и если ее находит, то игнорирует вашу, а берет системную. Это приводит к двум проблемам:
- Лишняя проверка требует дополнительных затрат
- Существует довольно большая вероятность, что ваша план для такой хранимой процедуры не будет кэшироваться.
Рассмотрим более подробно:
Типы системных объектов
Системные объекты поставляться с установкой SQL Server, располагаются в системных базах данных, но могут быть доступны из любых баз данных. В текущих версиях все системные обыекты помечены как поставляемые Microsoft, но в будущих версиях все может измениться.
Их всего 4:
- System-Stored-Procedure — имя начинается на sp_ и может быть как обычной хранимой процедурой, так и расширенной (исполняемой вне SQL Server) хранимой процедурой
- System-Extended-Stored-Procedure — имя начинается на xp_, могут быть как обычными, так и расширенными хранимыми процедурами. Доступны только в базе данных master и/или схеме sys в других базах.
- System View — системные представления, живут в схемах INFORMATION_SCHEMA и sys
- System Function — системные функции (скалярные или табличные), которые видимы в любой базе данных, рекомендовано (странный комментарий для системного объекта) называть их с префиксом fn_
Метод распознавания
И так, почему нужен специальный метод распознавания.
Основная причина в том, что системные объекты должны быть видимыми из пользовательской базы данных, но при этом физически хранятся в системной базе, и не копируются в пользовательскую базу при ее создании. Достаточно логичный подход, т.к. в противном случае, не возможно контролировать эти самые системные объекты. То есть нельзя защитить их от пользователя, а также возникают проблемы с обновлением сервера баз данных, которые иногда затрагивают и системные объекты.
Стоит также упомянуть, что полное имя объекта базы данных состоит из 4-х частей:
[server.] [database.] [schema.] entity
При указании не полного имени, осуществляется распознавание нужного объекта. Самое простое и понятное правило состоит в том, что сервер и база данных всегда берутся текущими, а схема выбирается, как схема по умолчанию для пользователя. В SQL Sever 2000, имя такой схемы совпадает с именем пользователя, но если объект не найдет, то дополнительно идет проверка в схеме dbo. В 2005 имя схемы по умолчанию может быть любым и задается при помощи DEFAULT SCHEMA при создании или обновлении пользователя, если DEFAULT SCHEMA не была задана, то она равна dbo (а не имени пользователя, как было в предыдущих версиях).
Но у каждого правила есть исключения, т.к. не хорошо заставлять писать пользователя master.dbo.sp_who, а не просто sp_who.
Для распознования системных объектов используются следующие правила.
-
Anydb.dbo.sp_* распознается, как master.dbo.sp_, но выполняется в контексте anydb.
То есть, если мы выполним следующий код:
use master go create procedure sp_my_test as PRINT 'Execute in master' go use demo go exec sp_my_test go
То получим ответ «Execute in master», а не ошибку, что хранимой процедуры sp_my_test в базе данных demo нет. Тоже самое будет, если я напишу явно:
use demo go exec demo.dbo.sp_my_test go
Но, что будет, если имена будет конфликтовать, то есть, я создам одну и ту же хранимую процедуру в разных базах данных:
use master go create procedure sp_my_test as PRINT 'Execute in master' go use demo go create procedure sp_my_test as PRINT 'Execute in demo' go use demo go exec sp_my_test go
То получу результат: Execute in demo. То есть SQL Server, взял хранимаю процедуру из правильной (текущей баз данных). Но так можно доиграться, и до случайной перегрузки системных процедур.
use demo go create procedure sp_who as PRINT 'Fake sp_who' go exec sp_who exec demo.dbo.sp_who
В результате, мы получит список работающих в данный момент пользователей в двух экзмеплярах. То есть, если имя вашей sp_ хранимой процедуру совпало с системной, то ее вызвать не получиться.
Системные табличные функции нужно вызывать с префиксом ::, а скалярные без указания схемы.
- Из этого в частности следуют, что пользовательскую функцию нужно вызвать с указание схемы.
create function my_fn() returns int begin RETURN 1 end go select my_fn()
Результат:
Msg 195, Level 15, State 10, Line 1
‘my_fn’ is not a recognized built-in function name.
-
Системные представления из INFORMATION_SCHEMA или sys нужно вызывать с указаним схемы.
При этом они размещаются в базе данных master, но компиляции выполняется в каждой конкретной базе данных.
- На все остальные системные объекты, поставляемые компанией Microsoft, распространяются обычные правила распознавание имен.