Dicas T-SQL – Qual impacto de utilizar o prefixo “sp_” no nome de uma procedure?

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

USE master

CREATE PROC sp_teste

BEGIN

SELECT DB_NAME() AS Nm_Database, 'Executei na MASTER!!!' AS Ds_Observacao

END

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

USE Traces

EXEC sp_teste

USE master

EXEC sp_teste

“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
GO

USE Traces

CREATE PROC sp_teste

BEGIN

SELECT DB_NAME() AS Nm_Database, 'Executei na TRACES!!!' AS Ds_Observacao

END

Vamos 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

USE Traces

EXEC sp_teste

USE master

EXEC sp_teste

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
GO

USE master

CREATE PROC spteste

BEGIN

SELECT DB_NAME() AS Nm_Database, 'Executei na MASTER!!!' AS Ds_Observacao

END

Vamos executar essa procedure nas duas databases e ver o que acontece.

USE Traces
GO
EXEC spteste
GO

USE master
GO
EXEC spteste
GO

USE Traces

EXEC spteste

USE master

EXEC spteste

Msg 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_teste2

USE Traces

EXEC sp_teste2

Msg 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_whoisactive

1	EXEC sp_whoisactive

System 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.

Referência: https://docs.microsoft.com/pt-br/sql/relational-databases/system-stored-procedures/system-stored-procedures-transact-sql?view=sql-server-ver15

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

use Teste

EXEC sp_spaceused

use Traces

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:

https://github.com/luizvitorf/SQLServer/blob/master/Scripts/T-SQL/Dicas%20T-SQL%20-%20Qual%20impacto%20de%20utilizar%20o%20prefixo%20sp_%20no%20nome%20de%20uma%20procedure.sql

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

4 comentários em “Dicas T-SQL – Qual impacto de utilizar o prefixo “sp_” no nome de uma procedure?”

Rodrigo Martin 23 de junho de 2020 Responder

Nunca pensei que um “simples” sp_ pode trazer esse tipo de problema. Valeu Luiz, obrigado por compartilhar esse conhecimento.
- Luiz Lima Autor do post24 de junho de 2020 Responder
  
  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
Magno R. Santos 25 de julho de 2020 Responder

Parabéns pela postagem. Muito esclarecedora.
- Luiz Lima Autor do post25 de julho de 2020 Responder
  
  Obrigado Magno! Fico feliz em ter ajudado =)
  
  Abraço,
  Luiz Vitor

Nomenclatura Procedure – Prefixo “sp_”:

Demo – Prefixo “sp_”:

sp_whoisactive:

System Stored Procedures – sp_spaceused:

Resposta:

Download – Scripts:

Compartilhe isso:

4 comentários em “Dicas T-SQL – Qual impacto de utilizar o prefixo “sp_” no nome de uma procedure?”

Deixe uma respostaCancelar resposta