▷【 Cómo generar datos de prueba aleatorios de SQL Server usando T-SQL 】 (2023)

Deja un comentario / Pruebas, T-SQL

Introducción

En este artículo, hablaremos sobre la generación de valores aleatorios con fines de prueba.

Una vez tuve un cliente con un software que funcionó bien en la demostración con 30 filas, pero después de algunos meses, el software tenía más de un millón de filas y se volvió muy lento. El problema no era SQL Server, el problema era la aplicación, que no estaba diseñada para tablas con millones de filas. El cliente demandó al proveedor de software y se necesitaron abogados para crear una resolución. Si el proveedor hubiera probado el software con millones de filas, este problema nunca habría ocurrido.

Por eso, es muy importante generar datos y probar el software con millones de filas. No siempre es una tarea fácil. En este artículo, le daremos algunos consejos útiles de T-SQL que pueden ayudarlo o al menos inspirarlo en esto. En general, los datos aleatorios son muy útiles para realizar pruebas, aprender sobre la eficiencia de las consultas, demostraciones y más.

En este artículo, enseñaremos cómo generar hasta un millón de filas de datos aleatorios en SQL Server, que incluyen:

combinaciones de nombres de usuario y apellidos
valores enteros
números reales con un rango específico
contraseñas en SQL Server
correos electrónicos
nombres de países

Requisitos

servidor SQL
Estudio de administración de SQL Server (SSMS)
Base de datos Adventure Works 2014 Full y Adventure Works DW 2014

Empezando

1. Genere un millón de nombres y apellidos

En el primer ejemplo, usaremos la tabla DimCustomer de la base de datos AdventureWorksDW mencionada en los requisitos. Esta tabla contiene 18.000 filas. Usaremos una combinación cruzada para generar todas las posibles combinaciones de nombres y apellidos. Con la combinación cruzada puede generar una combinación total de 341,658,256 usuarios para sus pruebas. El siguiente ejemplo muestra cómo crear una combinación de 1 millón de nombres de usuario y apellidos:

se

USE [AdventureWorksDW2014]

GO

--Change 1000000 to the number of your preference for your needs

SELECT TOP 1000000

c1.[FirstName],

c2.[LastName]

FROM [dbo].[DimCustomer] c1

CROSS JOIN

DimCustomer c2

El ejemplo mostrará 1,000,000 filas de nombres y apellidos:

Figura 1. Generando todas las combinaciones posibles entre el nombre y apellido

Si desea generar 34 millones de filas, debe reemplazar esta línea:

SELECT TOP 1000000

Con este:

SELECT TOP 34000000

La consulta genera un producto cartesiano con todas las combinaciones y TOP limita el número de filas.

READ Automatización de pruebas de bases de datos con Visual Studio y Team Foundation Server

2. Genere valores enteros aleatorios

El siguiente ejemplo mostrará cómo crear una tabla de 1000 filas con valores aleatorios de 1 a 100. Usaremos la función RAND para crear valores aleatorios y CHECKSUM (NEWID ()) para generar valores distintos. Usamos la conversión para convertir los valores de real a entero:

with randowvalues

as(

select 1 id, CAST(RAND(CHECKSUM(NEWID()))*100 as int) randomnumber

--select 1 id, RAND(CHECKSUM(NEWID()))*100 randomnumber

unionall

select id + 1, CAST(RAND(CHECKSUM(NEWID()))*100 as int)randomnumber

--select id + 1, RAND(CHECKSUM(NEWID()))*100randomnumber

from randowvalues

where

id < 1000

)

select *

from randowvalues

OPTION(MAXRECURSION )

El código mostrará 100 valores entre 1 y 100:

Figura 2. Valores aleatorios enteros generados en SQL Server

Si desea generar 10000 valores, cambie esta línea:

id <1000

Con este:

id <10000

Si desea generar valores de 1 a 10000, cambie estas líneas:

select 1 id, CAST(RAND(CHECKSUM(NEWID()))*10000 as int) randomnumber

unionall

select id + 1, CAST(RAND(CHECKSUM(NEWID()))*10000 as int)randomnumber

from randowvalues

Si desea generar valores reales en lugar de valores enteros, use estas líneas y reemplace estas líneas del código que se muestra antes:

select 1 id, CAST(RAND(CHECKSUM(NEWID()))*10000 as int) randomnumber

unionall

select id + 1, CAST(RAND(CHECKSUM(NEWID()))*10000 as int)randomnumber

from randowvalues

Y usa estos:

select 1 id, RAND(CHECKSUM(NEWID()))*10000 randomnumber

unionall

select id + 1, RAND(CHECKSUM(NEWID()))*10000randomnumber

from randowvalues

La consulta mostrará números reales del 0 al 100

3. Números reales aleatorios con un rango específico

Otra solicitud típica es proporcionar valores aleatorios con rangos específicos. El siguiente ejemplo mostrará un rango de temperaturas en ° F (realmente prefiero el sistema métrico, pero esta vez haré una excepción).

El cuerpo humano tiene las siguientes fluctuaciones de temperatura: 95 a 105,8 ° F (la temperatura normal es de 97,7 a 99,5 ° F, los valores más altos significan fiebre, hipertermia y valores más bajos de hipotermia).

En este ejemplo, generaremos valores entre 95 y 105,8 ° F:

with randowvalues

as(

--10.8 is the difference between 105.8 minus 95

select 1 id,CAST(RAND(CHECKSUM(NEWID()))*10.8 as real) +95 as randomnumber

unionall

select id + 1,CAST(RAND(CHECKSUM(NEWID()))*10.8 as real)+95 as randomnumber

from randowvalues

where

id < 100

)

select *

from randowvalues

OPTION(MAXRECURSION )

El resultado de la instrucción T-SQL serán valores de 95 a 105.8 ° F:

READ CONFIGURAR QUOTED_IDENTIFIER en SQL Server

Figura 3. Números reales aleatorios del 0 al 100

Si desea números reales del 6 al 10, cambie estas líneas de código:

select 1 id,CAST(RAND(CHECKSUM(NEWID()))*10.8 as real) +95 as randomnumber

unionall

select id + 1,CAST(RAND(CHECKSUM(NEWID()))*10.8 as real)+95 as randomnumber

Con estos:

select 1 id,CAST(RAND(CHECKSUM(NEWID()))*4 as real) +6 as randomnumber

unionall

select id + 1,CAST(RAND(CHECKSUM(NEWID()))*4 as real)+6 as randomnumber

Donde 6 es el valor mínimo y 4 es la diferencia entre 10 y 6.

4. Contraseñas aleatorias en SQL Server

Otra solicitud común es generar contraseñas. Este ejemplo se utiliza para las contraseñas iniciales que el usuario cambiará posteriormente o cuando el usuario olvide la contraseña.

El siguiente ejemplo generará 100 contraseñas:

with randowvalues

as(

select 1 id, CONVERT(varchar(20), CRYPT_GEN_RANDOM(10)) as mypassword

unionall

select id + 1,CONVERT(varchar(20), CRYPT_GEN_RANDOM(10)) as mypassword

from randowvalues

where

id < 100

)

select *

from randowvalues

OPTION(MAXRECURSION )

Los valores mostrados por las declaraciones T-SQL son los siguientes:

Figura 4. Contraseñas aleatorias

Usamos la función CRYPT_GEN_RANDOM para generar contraseñas y luego las convertiremos a varchar. La función devuelve valores hexadecimales y los convertimos a caracteres.

5. Generación de correos electrónicos aleatorios

El siguiente ejemplo generará algunas contraseñas. Usaremos los nombres y apellidos del ejemplo 1 de la tabla DimCustomer para generar correos electrónicos falsos aleatorios en SQL Server. Si tenemos, por ejemplo, un Cliente llamado John Smith, generaremos un correo electrónico que puede ser jsmith@gmail.com, o usaremos una cuenta de Hotmail o Yahoo. Echemos un vistazo al código:

USE [AdventureWorksDW2014]

GO

WITH random

as

(

SELECT TOP 10000

c1.[FirstName],

c2.[LastName],CAST(RAND(CHECKSUM(NEWID()))*3 as int) randomemail

FROM [dbo].[DimCustomer] c1

CROSS JOIN

DimCustomer c2

)

select

Firstname,

Lastname,

email=

CASE

when randomemail = then

lower(left(FirstName,1)+[LastName])+'@hotmail.com'

when randomemail =1 then

lower(left(FirstName,1)+[LastName])+'@gmail.com'

else

lower(left(FirstName,1)+[LastName])+'@yahoo.com'

END

from random

El código extraerá la primera letra del Nombre y concatenará con el apellido y concatenará Hotmail o gmail o yahoo al azar:

READ Aprenda SQL: tipos de datos SQL

Figura 5. Correos electrónicos aleatorios

6. Genere nombres de países al azar

Este último ejemplo mostrará cómo generar nombres de países aleatorios. Usaremos la tabla Person.CounryRegion de la base de datos de adventureworks y agregaremos un id usando la función Row_number:

SELECT ROW_NUMBER() OVER(ORDER BY Name) AS id,

[Name]

FROM [AdventureWorks2016CTP3].[Person].[CountryRegion]

Esta tabla contiene 238 países:

Figura 6. Lista de países en la tabla Person.CountryRegion de adventureworks

Usaremos la lista de números aleatorios del segundo ejemplo para generar valores de 1 a 238 (238 es el número total de países) usaremos una combinación interna para unir los números aleatorios con los países y generar nombres de países al azar:

;with countries

as

(

-- Create a country id and a country name. The countryid will be used to ---join with the random numbers

SELECT ROW_NUMBER() OVER(ORDER BY Name) AS countryid,

[Name]

FROM [AdventureWorks2016CTP3].[Person].[CountryRegion]

),

---Create 1000 random numbers from 1 to 238

randowvalues

as(

select 1 id, CAST(RAND(CHECKSUM(NEWID()))*238 as int) randomnumber

unionall

select id + 1, CAST(RAND(CHECKSUM(NEWID()))*238 as int)randomnumber

from randowvalues

where

id < 1000

)

--- Join countries with random numbers to generate country names randomly

select randomnumber,c.Name

from randowvalues r

inner join countries c

on r.randomnumber=c.countryid

order by id

OPTION(MAXRECURSION )

Las declaraciones T-SQL generarán una lista de países al azar:

Figura 7. Lista de países generada aleatoriamente

Conclusiones

Generar valores aleatorios para realizar pruebas puede resultar complicado. Sin embargo, este artículo puede ser útil para inspirarte a crear tus propios datos. A veces podemos usar tablas existentes para generar más valores. A veces podemos crear los datos desde cero. En este ejemplo, mostramos cómo crear datos usando la función Random.

En este artículo, generamos millones de nombres y apellidos, valores enteros aleatorios, valores reales con rangos específicos, contraseñas aleatorias, correos electrónicos aleatorios con nombres y apellidos y nombres de países aleatorios.