Generación de tiempo aleatorio distinto en el intervalo fijo

El problema que tuvo OP mientras usaba solo rand() se debe a su evaluación una vez por consulta.

Desde el documentación:

Si no se especifica seed, el motor de base de datos SQL Server asigna un valor seed al azar. Para un valor especificado seed, el resultado devuelto es siempre el mismo.

Enfoque que se describe a continuación elimina la optimización y suprime este comportamiento, por lo que {[1] } se evalúa una vez por fila :

dateadd( second
       , rand(cast(newid() as varbinary)) * 43200
       , cast('08:00:00' as time) )

• newid() genera valor único de tipo uniqueidentifier;
• el valor se convierte con cast para ser utilizado como semilla en rand([seed]) función para generar un valor pseudo-aleatorio float desde 0 hasta 1, y como seed siempre es único, el valor devuelto también es único .

SQLFiddle

Puedes usar alternativamente:

SELECT DATEADD(s, ABS(CHECKSUM(NewId()) % 43201), CAST('08:00:00' AS Time))

El ABS(CHECKSUM(NewId()) % 43201) genera un número aleatorio entre 0 y 43200. Ver Discusión aquí.

SQL Fiddle

Configuración del esquema de MS SQL Server 2008 :

Consulta 1:

SELECT DATEADD(s, ABS(CHECKSUM(NewId()) % 43201), CAST('08:00:00' AS Time)) AS [RandomTime]
FROM 
( VALUES (1), (2), (3), (4), (5)
) Y(A)
CROSS JOIN
( VALUES (1), (2), (3), (4), (5)
) Z(A)

Resultados:

|    RANDOMTIME    |
|------------------|
| 16:51:58.0000000 |
| 10:42:44.0000000 |
| 14:01:38.0000000 |
| 13:33:51.0000000 |
| 18:00:51.0000000 |
| 11:29:03.0000000 |
| 10:21:14.0000000 |
| 16:38:27.0000000 |
| 09:55:37.0000000 |
| 13:21:13.0000000 |
| 11:29:37.0000000 |
| 10:57:49.0000000 |
| 14:56:42.0000000 |
| 15:33:11.0000000 |
| 18:49:45.0000000 |
| 16:23:28.0000000 |
| 09:00:05.0000000 |
| 09:20:01.0000000 |
| 11:26:23.0000000 |
| 15:26:23.0000000 |
| 10:38:44.0000000 |
| 11:46:30.0000000 |
| 16:00:59.0000000 |
| 09:29:18.0000000 |
| 09:09:19.0000000 |

Hay varios métodos:

• Genere una tabla con números aleatorios por adelantado y utilícela cuando sea necesario. O tomar estos datos de alguna fuente de buena reputación .
• Varias combinaciones que utilizan NEWID función para proporcionar una semilla para RAND. Debe usarse con precaución, porque no hay garantía sobre la distribución de los valores NEWID. Uno de los mejores métodos para hacerlo más o menos uniformemente distribuido es a través de la CHECKSUM: RAND(CHECKSUM(NEWID())). Lo bueno acerca de este método es que la función NEWID está disponible desde SQL Server 2000.
• En lugar de NEWID use, digamos, MD5 de alguna columna como semilla para RAND: RAND(CHECKSUM(HASHBYTES('MD5', CAST(SomeID AS varbinary(4))))) O simplemente número de fila: RAND(CHECKSUM(HASHBYTES('MD5', CAST(ROW_NUMBER() OVER(ORDER BY ...) AS varbinary(4))))). Este método está disponible al menos desde SQL Server 2005. La principal diferencia con el método NEWID es que usted tiene control total sobre la secuencia aleatoria. No puede controlar lo que devuelve NEWID y no puede reiniciar la secuencia aleatoria desde el mismo número de nuevo. Si proporciona los mismos conjuntos de, digamos, números de fila usando PARTITION BY obtendrás los mismos conjuntos de números aleatorios. Puede ser útil en los casos en que necesite usar la misma secuencia de números aleatorios varias veces. Es posible obtener el mismo número aleatorio para dos semillas diferentes. Lo probé para números de fila del 1 al 1.000.000. MD5 de ellos son todos diferentes. CHECKSUM de MD5 resultan en 122 colisiones. RAND de esto CHECKSUM resultan en 246 colisiones. Cuando se probó con números de fila de 1 a 100,000 CHECKSUM tuvo 1 colisión, RAND tuvo 3 colisión.
• Otra posibilidad es simplemente implementar su propia función definida por el usuario en T-SQL que genera un número aleatorio utilizando su algoritmo preferido. En este caso usted tiene el control total de todo. Por lo general, los generadores pseudo aleatorios tienen que almacenar su estado interno entre invocaciones, por lo que puede terminar con una tabla dedicada que almacena estos datos.
• Puede escribir su función definida por el usuario usando CLR. En este caso puede implementar su propio generador, o utilice funciones integradas en. NET, como Random clase , o RNGCryptoServiceProvider clase .
• Por fin, desde SQL Server 2008 hay una función incorporadaCRYPT_GEN_RANDOM.

Describiré el último método en detalle, porque creo que es una muy buena solución para SQL Server 2008 y superiores. CRYPT_GEN_RANDOM se llama para cada fila del conjunto de resultados, a diferencia de RAND, que se llama solo una vez.

CRYPT_GEN_RANDOM (Transact-SQL)

Devuelve un número aleatorio criptográfico generado por la API Crypto (CAPI). La salida es un número hexadecimal del número especificado de byte.

Además, CRYPT_GEN_RANDOM debería proporcionar valores aleatorios mucho mejores que RAND. Mejor en términos de distribución y cripto-fuerza. Ejemplo:

(CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5)

Esto genera 4 bytes aleatorios como varbinary. Tenemos que lanzarlos explícitamente a int primero. A continuación, el resultado se transforma en un número flotante entre 0 y 1.

So, a la consulta original le gustaría esto:

SELECT ID AS [ID]
     , MyFunction.dbo.AddWorkDays(14, S.CREATED_DATE) AS [New Date]
     , CONVERT(VARCHAR, DATEADD(MILLISECOND, 
     CAST(43200000 * (CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5) as int),
     CONVERT(TIME, '08:00')), 114) AS [New Time]
FROM RandomTable

Aquí hay un ejemplo independiente que es fácil de copiar y pegar y probar (usé la consulta de otra respuesta de @Steve Ford):

SELECT DATEADD(millisecond, 
    CAST(43200000 * (CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5) as int), 
    CAST('08:00:00' AS Time)) AS [RandomTime]
FROM 
    ( VALUES (1), (2), (3), (4), (5)
    ) Y(A)
    CROSS JOIN
    ( VALUES (1), (2), (3), (4), (5)
    ) Z(A)

Este es el resultado:

RandomTime
10:58:24.7200000
19:40:06.7220000
11:04:29.0530000
08:57:31.6130000
15:03:14.9470000
09:15:34.9380000
13:46:43.1250000
11:27:00.8940000
14:42:23.6100000
15:07:56.2120000
11:39:09.8830000
08:16:44.3960000
14:23:38.4820000
17:28:31.7440000
16:29:31.4320000
09:09:15.0210000
12:31:09.8370000
11:23:09.8430000
15:35:45.5480000
17:42:49.3390000
08:07:05.4930000
18:17:16.2980000
11:49:08.2010000
10:20:21.7620000
15:56:58.6110000

Adición

Cuando leí la pregunta original no pensé que fuera realmente necesario asegurarse de que todos los números aleatorios generados fueran únicos. Interpreté la palabra "diferente" en la pregunta como un vago opuesto a ver el mismo número en cada fila del resultado que se ve cuando se utiliza un simple SELECT RAND(). Creo que en muchos casos no importa si hay pocos números aleatorios colisionando. En muchos casos, en realidad sería el comportamiento correcto.

Entonces, mi entendimiento es que cuando hay una necesidad de una secuencia de números aleatorios únicos, es en un sentido equivalente a la siguiente tarea. Tenemos un conjunto de algunos valores/filas, por ejemplo, un conjunto de identificadores únicos o todos los 86400 segundos de un día o 2800 filas para un determinado dia. Queremos barajar estos valores / filas. Queremos reorganizar estas filas en un orden aleatorio.

Para barajar el conjunto dado de filas simplemente necesitamos ORDER BY números aleatorios (estos números aleatorios pueden tener una cantidad razonable de colisiones aquí). Los números aleatorios pueden ser generados por cualquier método. Algo como esto:

ROW_NUMBER() OVER ([optional PARTITION BY ...] ORDER BY CRYPT_GEN_RANDOM(4)) 

O literalmente

SELECT ...
FROM ...
ORDER BY CRYPT_GEN_RANDOM(4)

Dependiendo de dónde y cómo se use.

Prueba esto:

 Declare @t table(ID int,CREATED_DATE datetime)
insert into @t values
 (1 ,  '04/26/2014'),
 (2 ,  '04/26/2014'),
 (3 ,  '04/26/2014'),
 (4 ,  '04/26/2014')

 ;WITH CTE AS
 (
   SELECT *,CONVERT(VARCHAR, DATEADD(SECOND, RAND(CAST(NEWID() AS VARBINARY)) * 43200, 
   CAST('08:00:00' AS TIME)),114) AS [New Time] FROM @t WHERE ID=1
   UNION ALL
   SELECT *,CONVERT(VARCHAR, DATEADD(SECOND, RAND(CAST(NEWID() AS VARBINARY)) * 43200, 
   CAST('08:00:00' AS TIME)), 114)  FROM @t WHERE ID>1 AND ID<=5
 )
 SELECT * FROM CTE

Aquí hay otra opción que le da un poco más de control sobre cómo se genera el tiempo. Puede especificar el intervalo entre los tiempos aleatorios. Tampoco hace uso de la función RAND.

DECLARE @StartTime  VARCHAR(10) = '08:00',
        @EndTime    VARCHAR(10) = '20:00',
        @Interval   INT = 5 --(In Seconds)

WITH times AS(
    SELECT CONVERT(TIME, @StartTime) AS t
    UNION ALL
    SELECT DATEADD(SECOND, @Interval, t)
    FROM times
    WHERE t < @EndTime
)

SELECT *, 
(SELECT TOP 1 t FROM times WHERE d.Id > 0 ORDER BY NEWID())
FROM #data d
option (maxrecursion 0)

En una nota al margen:
Si elimina la cláusula WHERE en la subconsulta anterior (WHERE d.Id > 0), se devuelve el mismo valor de tiempo para todas las filas, es decir, el mismo problema que comenzó con

Todos,

Pensé en compartir la respuesta a mi pregunta. No puedo recordar exactamente dónde encontré los detalles, creo que fue a través de uno de los enlaces proporcionados por sgeddes.

Usé la siguiente consulta para obtener un tiempo aleatorio entre las 8 a. m. y las 7:55 p. m. (aproximadamente)

SELECT convert(varchar,CONVERT(varchar, DATEADD(ms, dbo.MyRand(335 ,830) * 86400, 0), 114),114)

La función MyRand está debajo:

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE FUNCTION dbo.myRand(@Min INT, @Max INT) RETURNS decimal(18,15) AS
BEGIN
DECLARE @BinaryFloat BINARY(8)
SELECT @BinaryFloat = CAST(Id AS BINARY) FROM vwGuid

DECLARE
@PartValue TINYINT,
@Mask TINYINT,
@Mantissa FLOAT,
@Exponent SMALLINT,
@Bit TINYINT,
@Ln2 FLOAT,
@BigValue BIGINT,
@RandomNumber FLOAT

SELECT
@Mantissa = 1,
@Bit = 1,
@Ln2 = LOG(2),
@BigValue = CAST(@BinaryFloat AS BIGINT),
@Exponent = (@BigValue & 0x7ff0000000000000) / EXP(52 * @Ln2)

WHILE @Part <= 8
BEGIN
SELECT
@PartValue = CAST(SUBSTRING(@BinaryFloat, @Part, 1) AS TINYINT),
@Mask =

WHILE @Mask > 0
BEGIN
IF @PartValue & @Mask > 0
SET @Mantissa = @Mantissa + EXP(-@Bit * @Ln2)

SELECT
@Mask = @Mask / 2
END
END

SET @RandomNumber = CASE @Exponent WHEN 0 THEN 0 ELSE CAST(@Exponent AS FLOAT) / 2047 END

RETURN CAST((@RandomNumber * (@Max - @Min)) + @Min AS DECIMAL(18,15))

END
GO
END

Espero que esto ayude. No he leído muchas de las respuestas anteriores, así que disculpas si alguien tiene una mejor respuesta , esto es simplemente cómo resolví se.

Gracias