Olá amiguinhos,
Nesse post vamos responder à pergunta abaixo:
Qual impacto de utilizar o prefixo “sp_” no nome de uma procedure?
A) Nenhum. É um nome como qualquer outro e não tem nenhum impacto.
B) O SQL Server valida primeiro se a procedure existe na database master para depois validar se existe na database em que o script está sendo executado.
C) O SQL Server valida primeiro se a procedure existe na database em que o script está sendo executado para depois validar se existe na database master.
D) Não sei nem chutar essa. Vou marcar D de Deus =)
Antes de continuar a leitura do post, escolha a sua opção e espero que ao final você entenda qual é a alternativa correta. #gogogo =)
Nomenclatura Procedure – Prefixo “sp_”:
Ao desenvolver códigos T-SQL, algumas empresas adotam alguns padrões de nomenclatura para as procedures. Por exemplo:
- spCadastroCliente;
- spCadastro_Cliente;
- sp_CadastroCliente;
- stpCadastroCliente;
- E por aí vai…
Na Documentação Oficial da Microsoft ela recomenda que você evite utilizar o prefixo “sp_” na nomenclatura de procedures, pois esse prefixo é utilizado pelo SQL Server nas procedures de sistema.
Referência: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql?view=sql-server-2017
No post do Aaron Bertrand, ele também mostra que existe uma pequena diferença de performance quando utilizamos o prefixo “sp_”, mas acredito que isso não irá causar muito impacto.
Referência: https://sqlperformance.com/2012/10/t-sql-queries/sp_prefix
Na verdade, o maior problema em utilizar o prefixo “sp_” é a questão da AMBIGUIDADE.
“Como assim Luiz???”
Na demo abaixo vou explicar melhor esse problema. Chega de teoria e vamos colocar a mão na massa!
Demo – Prefixo “sp_”:
Agora vamos fazer uma demo para validar esses conceitos na prática!
Em primeiro lugar, vamos criar uma procedure chamada “sp_teste” somente na database “master”. Ela vai executar um SELECT e retornar o nome da database atual e o texto “Executei na MASTER!!!”.
USE master
GO
CREATE PROC sp_teste
AS
BEGIN
SELECT DB_NAME() AS Nm_Database, 'Executei na MASTER!!!' AS Ds_Observacao
END
GO Depois de criar a procedure, vou executa-la através da database “Traces” e “master”.
USE Traces
GO
EXEC sp_teste
GO
USE master
GO
EXEC sp_teste
GO 
“Uai Luiz, mas você só criou a procedure na database ‘master’! Como ela conseguiu executar através da database ‘Traces’ se ela nem existe lá??? Que bruxaria é essa cara???”
Pois é, o SQL Server primeiro valida se a procedure existe na database atual, no nosso caso é a database Traces. Como ele não encontrou, depois ele valida na database master, como a procedure existe lá ele retornou o resultado. Repare que a coluna “Nm_Database” está com os valores distintos e a coluna “Ds_Observacao” está com os valores iguais.
Agora vou criar uma procedure com o mesmo nome na database Traces que também vai retornar o nome da database atual, mas com o texto “Executei na TRACES!!!”.
USE Traces
GO
CREATE PROC sp_teste
AS
BEGIN
SELECT DB_NAME() AS Nm_Database, 'Executei na TRACES!!!' AS Ds_Observacao
END
GOVamos executar novamente a procedure nas duas databases e comparar o resultado.
USE Traces
GO
EXEC sp_teste
GO
USE master
GO
EXEC sp_teste
GO
Dessa vez o SQL Server encontrou a procedure na database “Traces” e executou e não precisou validar na “master”. Na segunda execução que estava utilizando a database “master”, o SQL Server utilizou a procedure da própria “master”. Repare que dessa vez a coluna “Ds_Observacao” está com os valores distintos.
Ou seja, se existir uma procedure com o mesmo nome na database “master” e na database que o script estiver sendo executado, os resultados podem ser distintos dependendo da database que a procedure estiver sendo executada! Portanto, tome cuidado com isso!
Então, sugiro que você utilize outro padrão de nomenclatura para as procedures e não utilize o prefixo “sp_” para evitar esse tipo de problema!
Apenas para reforçar, vamos fazer mais um teste criando a procedure “spteste” apenas na database “master”. Repare que dessa vez o prefixo é diferente.
USE master
GO
CREATE PROC spteste
AS
BEGIN
SELECT DB_NAME() AS Nm_Database, 'Executei na MASTER!!!' AS Ds_Observacao
END
GOVamos executar essa procedure nas duas databases e ver o que acontece.
USE Traces
GO
EXEC spteste
GO
USE master
GO
EXEC spteste
GOMsg 2812, Level 16, State 62, Line 100
Could not find stored procedure ‘spteste’.
Ao executar na database “Traces” o SQL Server não encontrou a procedure. Como o prefixo é diferente (“spteste” <> “sp_teste”) dessa vez o SQL Server não vai validar na database “master” e retorna um erro, pois ele faz isso apenas quando utilizamos o prefixo “sp_”.
Por fim, vou tentar executar uma procedure que não existe em nenhuma database e o SQL Server irá retornar um erro!
GO
USE Traces
GO
EXEC sp_teste2Msg 2812, Level 16, State 62, Line 120
Could not find stored procedure ‘sp_teste2’.
sp_whoisactive:
Esse mesmo comportamento acontece com a procedure “sp_whoisactive” que é bem útil no dia a dia para monitorar o banco de dados. Você só precisa cria-la na database “master” e depois consegue executar através de qualquer banco de dados.
Eu utilizei essa procedure por muitos anos (e ainda utilizo ela várias vezes por dia xD) e somente agora fui entender o que realmente acontece por debaixo dos panos. =)
EXEC sp_whoisactiveSystem Stored Procedures – sp_spaceused:
Uma outra observação importante é que cada database também possui algumas procedures internas de sistema (System Stored Procedures) que retornam informações que são úteis para a administração do banco de dados.
Vou utilizar como exemplo a procedure “sp_spaceused”. Ela retorna algumas informações sobre o espaço de cada database ou tabela. Repare no print abaixo que ela existe nas duas databases “Teste” e “Traces”.
Com isso, quando nós executamos a procedure, ela vai retornar dados diferentes para cada database.
use Teste
GO
EXEC sp_spaceused
GO
use Traces
GO
EXEC sp_spaceused
Resposta:
Agora podemos responder a nossa pergunta inicial!
Qual impacto de utilizar o prefixo “sp_” no nome de uma procedure?
A) Nenhum. É um nome como qualquer outro e não tem nenhum impacto.
Falso. Como podemos ver, existem impactos sim!
B) O SQL Server valida primeiro se a procedure existe na database master para depois validar se existe na database em que o script está sendo executado.
Falso. O SQL Server não valida a master em um primeiro momento.
C) O SQL Server valida primeiro se a procedure existe na database em que o script está sendo executado para depois validar se existe na database master.
Verdadeiro. Essa é a resposta correta! xD
D) Não sei nem chutar essa. Vou marcar D de Deus =)
Falso. Você não teve sorte no chute dessa vez =)
Download – Scripts:
Segue abaixo os scripts utilizados nesse post:
Espero que tenha gostado e que isso também possa ser útil no seu dia a dia. Até o próximo post!
Me siga no LinkedIn e YouTube para ficar por dentro das novidades.
Abraço,
Luiz Vitor França Lima
Consultor SQL Server
Nunca pensei que um “simples” sp_ pode trazer esse tipo de problema. Valeu Luiz, obrigado por compartilhar esse conhecimento.
Obrigado Rodrigo! Fico feliz que tenha ajudado! O SQL Server tem alguns detalhes mesmo que as vezes passam despercebidos. Fica ligado que de vez em quando eu tento explicar alguns deles =)
Abraço
Luiz Vitor
Parabéns pela postagem. Muito esclarecedora.
Obrigado Magno! Fico feliz em ter ajudado =)
Abraço,
Luiz Vitor