SQL Server FAQ - Creating a Large Table with Random Data for Indexes

Home >> FAQs/Tutorials >> SQL Server FAQ

By: FYIcenter.com

How To Create a Large Table with Random Data for Index Testing?

If you want to see how index can be used to improve data search performance, you have to build some large tables, which requires large amount of random data. This tutorial exercise helps you to build a large table with pure random data:

USE FyiCenterData;
GO

-- Drop the old table, if needed
DROP TABLE fyi_random;
GO

-- Create a table with primary key
CREATE TABLE fyi_random (
  id INT,
  rand_integer INT,
  rand_number numeric(18,9),
  rand_datetime DATETIME,
  rand_string VARCHAR(80)
);
GO

-- Insert rows with random values
DECLARE @row INT;
DECLARE @string VARCHAR(80), @length INT, @code INT;
SET @row = 0;
WHILE @row < 100000 BEGIN
   SET @row = @row + 1;

   -- Build the random string
   SET @length = ROUND(80*RAND(),0);
   SET @string = '';
   WHILE @length > 0 BEGIN
      SET @length = @length - 1;
      SET @code = ROUND(32*RAND(),0) - 6;
      IF @code BETWEEN 1 AND 26 
         SET @string = @string + CHAR(ASCII('a')+@code-1);
      ELSE
         SET @string = @string + ' ';
   END 

   -- Ready for the record
   SET NOCOUNT ON;
   INSERT INTO fyi_random VALUES (
      @row,
      ROUND(2000000*RAND()-1000000,0),
      ROUND(2000000*RAND()-1000000,9),
      CONVERT(DATETIME, ROUND(60000*RAND()-30000,9)),
      @string
   )
END
PRINT 'Rows inserted: '+CONVERT(VARCHAR(20),@row);
GO
Rows inserted: 100000

SELECT TOP 10 * FROM fyi_random;
GO
id  integer  number             date        string
1   609596   896530.556031080   1891-04-22  rbxddlaayxq x...
2   401483   375107.107911612   1941-10-15  ou pdep rjixd...
3   -350570  103724.712787200   1921-11-01  uwylic paxtlp...
4   -125377  204467.582749559   1931-11-11  xoikcdv gxixb...
5   -222733  -967023.477922098  1934-10-13  vj pqkyj ujbn...
6   377398   263410.255290027   1960-04-02  btstgqqx   dy...
7   -422529  43452.274555387    1911-04-19  qyoxqqo zuvxh...
8   -900104  357566.072355041   1833-10-19  d xyqfotmvbnd...
9   -724738  -396821.456353329  1939-09-27  rx           ...
10  398355   -817318.223094167  1874-03-25  g on oekx bc ...

(Continued on next topic...)