Funções para gerar números aleatórios no SQL 2005
Um professor (um dos melhores daqui) que trabalha comigo me fez o seguinte comentário:
- Estava dando aula de SQL 2 e criei alguns exemplos de funções de usuário para mostrar aos alunos e fiz uma que era ridícula: gerar um número aleatório dentro de uma faixa. Só que não funcionou.
Comentei que era porque a função RAND do SQL Server é não determinística. Ele já sabia disso, é óbvio. Nem me deixou terminar a frase.
Conversamos por uns 5 minutos e dei uma idéia: usar o RAND com uma semente, que seria a parte milissegundos da função GETDATE (que retorna a data e hora atual no SQL). Aí ela teria o comportamento de determinística.
Ele disse que tinha feito alguns testes sem sucesso.
Perguntei se ele se incomodaria de eu dar uma fuçada. Ele disse que “sem problema”.
Então, aí vai:
Primeiro criei um database no SQL Server, usando o SSMS (SQL Server Management Studio):
USE MASTER
–
IF EXISTS(SELECT * FROM SYS.DATABASES WHERE NAME = ‘EXEMPLO_FUNCAO_RANDOMICA’)
DROP DATABASE EXEMPLO_FUNCAO_RANDOMICA
–
CREATE DATABASE EXEMPLO_FUNCAO_RANDOMICA
GO
–
USE EXEMPLO_FUNCAO_RANDOMICACriei uma função de usuário que gerasse valores a partir dos milissegundos da data atual, dentro de um intervalo dado:
IF EXISTS(SELECT * FROM SYS.OBJECTS WHERE NAME = ‘UDF_RAND1′)
DROP FUNCTION UDF_RAND1
GO
–
CREATE FUNCTION UDF_RAND1(@MENOR INT, @MAIOR INT)
RETURNS INT
AS
BEGIN
RETURN (DATEPART(MS, GETDATE()) % (@MAIOR - @MENOR + 1) + @MENOR)
END
–
GO
–
SELECT DBO.UDF_RAND1(2, 6)Nesse ponto, pensei em fazer uma CLR funtion (função escrita em alguma linguagem .Net).
Nem!!!
Deve existir uma solução bonita usando somente o SQL Server. Aí pesquisei no nosso amigo Google. Nesse site, encontrei algumas soluções (umas “bonitas” e outras nem tanto).
Mas testei algumas. A primeira usa o OPENQUERY e tem um desempenho sofrível:
IF EXISTS(SELECT * FROM SYS.OBJECTS WHERE NAME = ‘UDF_RAND2′)
DROP FUNCTION UDF_RAND2
GO
–
CREATE FUNCTION UDF_RAND2(@MENOR INT, @MAIOR INT)
RETURNS INT
AS
BEGIN
DECLARE @R INT
SET @R = (SELECT R FROM OPENQUERY(NOTEBOOK,’SELECT CAST(1000 * RAND() AS INT) AS R’))
RETURN @R % (@MAIOR - @MENOR + 1) + @MENOR
END
–
GO
–
SELECT DBO.UDF_RAND2(1, 5)Mais um inconveniente: para essa função ser executada, necessitamos de executar o seguinte comando no banco:
EXEC SP_SERVEROPTION ‘NOTEBOOK’, ‘DATA ACCESS’, ‘TRUE’Uma outra solução, mais simpática, no mesmo site:
IF EXISTS(SELECT * FROM SYS.OBJECTS WHERE NAME = ‘UV_RAND’)
DROP VIEW UV_RAND
GO
–
CREATE VIEW UV_RAND
AS
SELECT CONVERT(INT, 1000 * RAND()) AS VALOR
GO
–
IF EXISTS(SELECT * FROM SYS.OBJECTS WHERE NAME = ‘UDF_RAND3′)
DROP FUNCTION UDF_RAND3
GO
–
CREATE FUNCTION UDF_RAND3 (@MENOR INT, @MAIOR INT)
RETURNS INT
AS
BEGIN
DECLARE @R INT
SET @R = (SELECT VALOR FROM UV_RAND)
RETURN @R % (@MAIOR - @MENOR + 1) + @MENOR
END
GO
–
SELECT DBO.UDF_RAND3(1, 5)Agora, a minha solução, usando o CLR, que é criar uma função usando o .Net 2005.
Abrimos o Visual Studio 2005 e criamos um novo projeto (File - New - Project), do tipo SQL Server Project (Visual C# - Database - SQL Server Project), chamado Rand.
Uma caixa de diálogo vai aparecer e, nela, clique em [Add New Reference…]:
Configure a conexão e clique em [OK]:
No Solution Explorer, adicione uma nova função, clicando com o botão direito no mouse e escolhendo a opção Add - User-Defined Function:
Escolha o nome Funcoes.cs para o arquivo:
Renomeie o método para CLR_RAND e complete-o. Após isso, publique a sua CLR function, usando:
Finalmente, no SSMS, execute o comando abaixo:
SELECT DBO.CLR_RAND(1, 5)Claro que existe a possibilidade de se usar comandos para publicar a função. Mostrarei em outro post.
