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

6 Respostas para “Piores Práticas – Utilizar o prefixo sp_ no nome de uma stored procedure

  1. Fabiano Neves

    Gustavo, estes dias eu estava meditando em relação a essas "boas práticas" , ou melhor, "piores práticas".Eu concordo com você em relação ao que escreveu, inclusive adoto esta prática, minhas procs iniciam com st_… mas nos dias de hoje onde temos SUPER servidores, será que uma simples alteração como essa iria nos causar realmente algum ganho de performance,… Digamos que tenhamos um ganho de 0,5 segundo … será que vale o trabalho… sei não viu… De qq forma… como eu disse… eu estava só "wondering" sobre esse asssunto…

  2. Olá,Quando falamos de boas-praticas, com certeza essa pode não ser uma das primeiras, mas deve ser levada em consideração.Claro que dificilmente uma empresa irá bancar o custo de re-desenvolvimento de um sistema inteiro, se esse for o único ponto falho.Mas quando falamos em ter um bom desempenho, as vezes temos que "tirar leite de pedra", nesse caso…milesegundos podem fazer a diferença se a quantidade de execução for consideralvemente grande.That\’s all folks.

  3. Olá Amigos,Obrigado pelos valiosos comentários. Concordo com as colocações de ambos. Na maioria das vezes depois de feito e de adquirir um super servidore dificilmente alguém irá "rever" essa questão das SPs. Provavelmente elas ficarão encostadas e as alterações virão apenas nos novos projetos. Por mais argumentos que eu postasse, jamais conseguiria provar que evitar o prefixo sp_ resulta em uma grande melhora de desempenho já que isso não é verdade (embora ela deve provocar alguma diferença mínima que seja).A idéia maior é evitar que essas práticas sejam utilizadas principalmente por iniciantes. Acredito que se as práticas ruins forem disseminadas devidamente como ruins, essas práticas tendem a ser desincentivadas e se cada prática ruim tem sua parcela de culpa, eliminar muitas pode trazer um resultado bem satisfatório.Abs,

  4. nossa legal ein? muito mesmo! vlw

  5. E pela nomenclatura ser tão natural sempre achei que fosse boa prática, haha. A mesma coisa vale para funções e seu “fn_”?

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s