Piores Práticas – Utilizar o prefixo sp_ no nome de uma stored procedure

Oi pessoal,

Hoje falarei de uma prática muito comum. A utilização do prefixo sp_ no nome de uma stored procedure. Essa é sabidamente uma péssima prática presente inclusive na documentação do produto (Books OnLine). Basta procurar pelo comando CREATE PROCEDURE nessa documentação e a mensagem é bem clara:

We strongly recommend that you not use the prefix sp_ in the procedure name. This prefix is used by SQL Server to designate system stored procedures. For more information, see Creating Stored Procedures (Database Engine).

Talvez uma messagem em inglês não seja suficientemente clara, mas o Books OnLine do SQL Server 2008 tem a tradução em português do comando CREATE PROCEDURE e a messagem continua muito clara:

Recomendamos fortemente que você não use o prefixo sp_ no nome de procedimento. Este prefixo é usado pelo SQL Server para designar procedimentos armazenados de sistema. Para obter mais informações, consulte Criando procedimentos armazenados (Mecanismos de Banco de Dados).

Basta navegar pelo google digitando "sql server stored procedures sp_" (sem as aspas) e aparecerão dezenas de links informando para que não sejam criadas stored procedures com o prefixo sp. Se existe tal recomendação por que então é tão comum a presença de stored procedures que se iniciam com o prefixo sp_ ?

Há duas razões muito triviais para que stored procedures ainda sejam criadas com o prefixo sp_. A primeira é que sp é um acrônimo perfeito para Stored Procedures. Da mesma forma que utilizamos acrônimos como tb e tbl para tabelas, vw ou viw para views, fn para functions, etc é muito natural utilizar sp_ para designar stored procedures. Outra forte razão para vermos stored procedures com o prefixo sp_ é que o SQL Server dispõe de várias procedures internas com o prefixo sp_ (ex: sp_help, sp_databases, sp_attach_db, etc). Juntando essas duas razões com o desconhecimento dos malefícios desse prefixo, é natural (principalmente entre iniciantes) construir stored procedures com o prefixo sp_ (ex: sp_cliente, sp_notas, sp_atualiza, etc).

E qual é a razão pela qual esse prefixo é desaconselhável ? Por que não usar um acrônimo tão natural para designar uma stored procedure ? E se há malefícios por que o SQL Server inicia suas procedure com sp_ ?

A razão é bem simples. O prefixo sp_ é reservado para que o SQL Server saiba que aquela procedure é inicialmente utilizada por ele e que se trata de uma procedure interna. Assim, toda vez que o SQL Server encontra uma procedure com o prefixo sp_ ele entende que essa é uma procedure própria do produto e que ela lhe concede algumas características especiais.

Quando utilizamos a procedure sp_helptext podemos ter informações sobre o código de um objeto como stored procedure, function, etc. Esse comando funcionará em qualquer banco de dados. É tentador pensar que essa procedure existe em qualquer banco de dados, mas a verdade é que essa procedure não existe em nenhum banco de dados criados por nós. Basta fazer uma simples consulta para perceber que não existe a procedure sp_helptext em nosso banco de dados.

SELECT * FROM SYS.OBJECTS WHERE NAME = ‘sp_helptext’

Esse comando não retornará nenhum resultado, pois, essa stored procedure não existe em nenhum banco de dados. Na verdade até existe, mas não é tão notório assim. Se o SQL Server for o 2000, essa procedure estará no banco de dados MASTER. Se for 2005 ou superior, essa stored procedure estará no banco de dados Resource. Independente de estar no MASTER ou no Resource o fato é que essa procedure está em um local reservado a procedures de sistema. É essa característica que faz com que uma stored procedure que não exista em nenhum banco possa ser executada e ainda retorna o resultado correto. Esse mesmo fenômeno ocorre com outros objetos como as views Information_Schema.

Isso significa que toda vez que o SQL Server encontrar uma stored procedure com o prefixo sp_, ele irá supor que se trata de uma procedure de sistema e irá procurar por ela no local onde normalmente ela deveria estar. Isso pode ser devidamente comprovado. Utilizemos um script:

— Muda o contexto do banco de dados
USE MASTER;

— Cria uma procedure com o prefixo sp_
CREATE PROCEDURE sp_retorna_nome_banco
AS
PRINT DB_NAME()

Se qualquer banco de dados for utilizado, basta executar a sp_retorna_nome_banco e a mesma irá retornar o nome do banco selecionado, ainda que você não tenha criada a stored procedure em outros bancos de dados. Isso ocorreu porque essa procedure tem o prefixo sp_ e por isso o SQL Server deduziu que se tratava de uma stored procedure de sistema que pode ser chamada a partir de qualquer banco de dados.

Essa pode parecer uma vantagem, mas esse efeito possui conseqüências. Como a procedure utiliza o prefixo sp_ (caracterizando-se como uma procedure de sistema), sempre que ela for executada, o SQL Server irá procurar se essa procedure existe nos bancos MASTER e RESOURCE e depois no banco de dados de onde ela foi chamada. O que aconteceria se no MASTER houvesse uma procedure com o prefixo sp_ e em um banco de dados houvesse uma procedure com o mesmo nome ? E se existisse uma procedure chamada sp_retorna_nome_banco em uma base SQL Server chamada BD ? Nesse caso, por padrão, a procedure que existisse no banco seria utilizada ao invés da sp_retorna_nome_banco no MASTER.

O fato é independente de haver conflitos de nomes, stored procedures com o prefixo sp_ sempre irão fazer uma visita aos bancos de dados de sistema para posteriormente irem no banco de dados de origem e executarem. Stored Procedures de negócio como sp_cliente, sp_notas e sp_atualiza deverão existir no banco de dados de negócio mas não no MASTER ou no Resource. Como possuem esse prefixo, toda vez que forem executadas, o SQL Server irá verificar se existem nos bancos de sistemas e não encontrando-as irá procurar no banco de dados de origem. Isso levará a uma ida desnecessária no banco de dados MASTER sempre que a procedure for chamada.

Qual é a razão plausível para utilizar uma stored procedure que irá primeiro em um banco de sistema, procurar por algo que não existe, voltar ao banco original e executar ? Seria muito mais eficiente se ela simplesmente executasse sem todos esses passos desnecessários. Não há razão do ponto de vista de desempenho em utilizar stored procedures com o prefixo sp_ já que elas degeneram o desempenho. A recomendação é que não utilizem stored procedures com o prefixo sp_ em hipótese nenhuma. Ainda que o efeito da sp_retorna_nome_banco possa ser interessante, não há garantia desse efeito em edições posteriores.

[ ]s,

Gustavo

Publicidade