In one of the recent projects, the customer asked to do performance at higher volume of data. So needed to generate a large table to test performance on. Since it is important to test queries on large data sets I thought that I would post how I populated a few data types in my table. To test query results I decided that I needed some values in the table that were not random so I simply salted my random data with known data at irregular intervals that would allow for testing. With a little bit of work I ended up with over 4 million records in a table with different datatype columns. You can take the code below and can expand it to fit your needs. Just change the value for the @Loop variable to control how many records you want to insert into your table.
DECLARE @val AS INT = 1; DECLARE @IntType_start AS INT = -147483648; DECLARE @IntType_end AS INT = 147483647; DECLARE @SmallIntType_start AS smallint = -12768; DECLARE @SmallIntType_end AS smallint = 12767; DECLARE @BigIntType_start AS bigint = -4223372036854775808; DECLARE @BigIntType_end AS bigint = 4223372036854775807; DECLARE @TinyIntType_start AS tinyint = 0; DECLARE @TinyIntType_end AS tinyint = 255; DECLARE @DecimalType_start AS decimal = -10^38 +1; DECLARE @DecimalType_end AS decimal = 10^38 -1; DECLARE @FloatType_start AS float = -1.79E+307; DECLARE @FloatType_end AS float = 1.79E+307; DECLARE @MoneyType_start AS money = -422337203685477.5808; DECLARE @MoneyType_end AS money = 422337203685477.5807; DECLARE @SmallMoneyType_start AS smallmoney = -99748.3648; DECLARE @SmallMoneyType_end AS smallmoney = 99748.3647; DECLARE @RealType_start AS real = -1.40E+38; DECLARE @RealType_end AS real = 1.40E+38; DECLARE @NumericType_start AS numeric = -10^38+1; DECLARE @NumericType_end AS numeric = 10^38-1; --DECLARE @CharType AS char(10) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'; --DECLARE @NCharType AS nchar(20) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*(~'; DECLARE @NVarCharType AS nvarchar(50) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*(~'; DECLARE @NVarCharMaxType AS nvarchar(Max) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*(~'; DECLARE @VarCharType AS varchar(50) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*(~'; DECLARE @VarCharMaxType AS varchar(Max) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*(~'; DECLARE @UniqueidentifierType AS uniqueidentifier = NEWID(); DECLARE @DateType AS date; DECLARE @DateTimeType_start datetime = '1753-1-1 00:00:00'; DECLARE @DateTimeType_end datetime = '9000-12-12 23:59:59'; DECLARE @SmallDateTimeType_start smalldatetime = '1900-1-1 00:00:00';; DECLARE @SmallDateTimeType_end smalldatetime = '2079-06-06 00:00:00'; DECLARE @DateTimeOffsetType As datetimeoffset(7); DECLARE @TimeType AS time = '00:00:00.0000000';
WHILE @val <= 4000000 BEGIN INSERT AllTypeData ( IntType, SmallIntType, BigIntType, TinyIntType, DecimalType, FloatType, MoneyType, SmallMoneyType, RealType, NumericType, CharType, NCharType, NVarCharType, NVarCharMaxType, VarCharType, VarCharMaxType, UniqueidentifierType, BitType, DateType, DateTimeType, SmallDateTimeType, DateTimeOffsetType, TimeType ) SELECT cast(Rand()*(@IntType_End-@IntType_start)+@IntType_start as int), cast(Rand()*(@SmallIntType_End-@SmallIntType_start)+@SmallIntType_start as smallint), cast(Rand()*(@BigIntType_End-@BigIntType_start)+@BigIntType_start as bigint), cast(Rand()*(@TinyIntType_End-@TinyIntType_start)+@TinyIntType_start as tinyint), Rand()*(@DecimalType_End-@DecimalType_start)+@DecimalType_start as decimal, Rand()*(@FloatType_End-@FloatType_start)+@FloatType_start as float, Rand()*(@MoneyType_End-@MoneyType_start)+@MoneyType_start as money, Rand()*(@SmallMoneyType_end-@SmallMoneyType_start)+@SmallMoneyType_start as SmallMoney, Rand()*(@RealType_end-@RealType_start)+@RealType_start as real, Rand()*(@NumericType_end-@NumericType_start)+@NumericType_start as numeric, substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*(~',(abs(checksum(newid())) % 47)+1, 10), substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!@#$%^&*(~',(abs(checksum(newid())) % 47)+1, 20), substring(@NVarCharType,(abs(checksum(newid()))%49)+1,20), substring(@NVarCharMaxType,(abs(checksum(newid()))%49)+1,20), substring(@VarCharType,(abs(checksum(newid()))%49)+1,20), substring(@VarCharMaxType,(abs(checksum(newid()))%49)+1,20), Newid(), CAST(ROUND(RAND(),0) AS BIT), DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0), DATEADD(MILLISECOND, RAND(ABS(CAST(CAST(NewID() AS BINARY(8)) AS INT)))*28800,DATEADD(SECOND, RAND(ABS(CAST(CAST(NewID() AS BINARY(8)) AS INT)))*28800, DATEADD(day, RAND(ABS(CAST(CAST(NewID() AS BINARY(8)) AS INT)))*(CAST((@DateTimeType_end - @DateTimeType_start) AS INT )), @DateTimeType_start))), DATEADD(second, RAND(ABS(CAST(CAST(NewID() AS BINARY(8)) AS INT)))*28800, DATEADD(day, RAND(ABS(CAST(CAST(NewID() AS BINARY(8)) AS INT)))*(CAST((@SmallDateTimeType_end - @SmallDateTimeType_start) AS INT )), @SmallDateTimeType_start)), DATEADD(MILLISECOND, RAND(ABS(CAST(CAST(NewID() AS BINARY(8)) AS INT)))*28800, DATEADD(SECOND, RAND(ABS(CAST(CAST(NewID() AS BINARY(8)) AS INT)))*28800, DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0))), DATEADD(NANOSECOND, RAND(ABS(CAST(CAST(NewID() AS BINARY(8)) AS INT)))*28800, DATEADD(SECOND, RAND(ABS(CAST(CAST(NewID() AS BINARY(8)) AS INT)))*28800, DATEADD(MINUTE, RAND(ABS(CAST(CAST(NewID() AS BINARY(8)) AS INT)))*28800, DATEADD(HH,RAND(ABS(CAST(CAST(NewID() AS BINARY(8)) AS INT)))*28800,cast(@TimeType as time))))); Set @val=@val+1; END
Use Select statement to see whether the data populated select * from AllTypeData