Home >> FAQs/Tutorials >> SQL Server FAQ

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

By: FYIcenter.com

(Continued from previous topic...)

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...)

  1. What Are Indexes?
  2. How To Create an Index on an Existing Table?
  3. How To View Existing Indexes on an Given Table using SP_HELP?
  4. How To View Existing Indexes on an Given Table using sys.indexes?
  5. How To Drop Existing Indexes?
  6. Is the PRIMARY KEY Column of a Table an Index?
  7. Does the UNIQUE Constraint Create an Index?
  8. What Is the Difference Between Clustered and Non-Clustered Indexes?
  9. How To Create a Clustered Index?
  10. How To Create an Index for Multiple Columns?
  11. How To Create a Large Table with Random Data for Index Testing?
  12. How To Measure Performance of INSERT Statements?
  13. Does Index Slows Down INSERT Statements?
  14. Does Index Speed Up SELECT Statements?
  15. What Happens If You Add a New Index to Large Table?
  16. What Is the Impact on Other User Sessions When Creating Indexes?
  17. What Is Index Fragmentation?
  18. What Causes Index Fragmentation?
  19. How To Defragment Table Indexes?
  20. How To Defragment Indexes with ALTER INDEX ... REORGANIZE?
  21. How To Rebuild Indexes with ALTER INDEX ... REBUILD?
  22. How To Rebuild All Indexes on a Single Table?
  23. How To Recreate an Existing Index?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...