How To Create a Large Table with Random Data for Index Testing in MS SQL Server?
Submitted by: AdministratorIf 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 GlobalGuideLineDatabase;
GO
-- Drop the old table, if needed
DROP TABLE ggl_random;
GO
-- Create a table with primary key
CREATE TABLE ggl_random (
id INT,
rand_integer INT,
rand_number numeric(18,9),
rand_datetime DATETIME,
rand_string VARCHAR(80)
);
GO
Submitted by: Administrator
USE GlobalGuideLineDatabase;
GO
-- Drop the old table, if needed
DROP TABLE ggl_random;
GO
-- Create a table with primary key
CREATE TABLE ggl_random (
id INT,
rand_integer INT,
rand_number numeric(18,9),
rand_datetime DATETIME,
rand_string VARCHAR(80)
);
GO
Submitted by: Administrator
-- Insert rows with random values
DECLARE @row INT;
DECLARE @string VARCHAR(80), @length INT, @code INT;
SET @row = 0;
<pre>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 ggl_random VALUES (
@row,
ROUND(2000000*RAND()-1000000,0),
ROUND(2000000*RAND()-1000000,9),
CONVERT(DATETIME, ROUND(60000*RAND()-30000,9)),
@string
)
<pre>END
PRINT 'Rows inserted: '+CONVERT(VARCHAR(20),@row);
GO
Rows inserted: 100000
Submitted by: Administrator
DECLARE @row INT;
DECLARE @string VARCHAR(80), @length INT, @code INT;
SET @row = 0;
<pre>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 ggl_random VALUES (
@row,
ROUND(2000000*RAND()-1000000,0),
ROUND(2000000*RAND()-1000000,9),
CONVERT(DATETIME, ROUND(60000*RAND()-30000,9)),
@string
)
<pre>END
PRINT 'Rows inserted: '+CONVERT(VARCHAR(20),@row);
GO
Rows inserted: 100000
Submitted by: Administrator
SELECT TOP 10 * FROM ggl_random;
GO
<pre>id integer number date string
1 609596 896530.556031080 1891-04-22 rggldlaayxq x...
2 401483 375107.107911612 1941-10-15 ou pdep rjixd...
3 -350570 103724.712787200 1921-11-01 uwgglc paxtlp...
4 -125377 204467.582749559 1931-11-11 xoikcdv ggglb...
5 -222733 -967023.477922098 1934-10-13 vj pqkyj ujbn...
6 377398 263410.255290027 1960-04-02 btgglqqx dy...
7 -422529 43452.274555387 1911-04-19 gglxqqo zuvxh...
8 -900104 357566.072355041 1833-10-19 d xyqgglvbnd...
9 -724738 -396821.456353329 1939-09-27 ggl ...
10 398355 -817318.223094167 1874-03-25 g on gglx bc ...</pre>
Submitted by: Administrator
GO
<pre>id integer number date string
1 609596 896530.556031080 1891-04-22 rggldlaayxq x...
2 401483 375107.107911612 1941-10-15 ou pdep rjixd...
3 -350570 103724.712787200 1921-11-01 uwgglc paxtlp...
4 -125377 204467.582749559 1931-11-11 xoikcdv ggglb...
5 -222733 -967023.477922098 1934-10-13 vj pqkyj ujbn...
6 377398 263410.255290027 1960-04-02 btgglqqx dy...
7 -422529 43452.274555387 1911-04-19 gglxqqo zuvxh...
8 -900104 357566.072355041 1833-10-19 d xyqgglvbnd...
9 -724738 -396821.456353329 1939-09-27 ggl ...
10 398355 -817318.223094167 1874-03-25 g on gglx bc ...</pre>
Submitted by: Administrator
Read Online MS SQL Server Job Interview Questions And Answers
Top MS SQL Server Questions
☺ | What Happens If NULL Values Are Involved in Bitwise Operations? |
☺ | PHP MSSQL - How To Display a Past Time in Days, Hours and Minutes? |
☺ | How to download Microsoft SQL Server 2005 Express Edition? |
☺ | Can You Roll Back the DDL Statement in a Trigger? |
☺ | What Is a Subquery in a SELECT Query Statement in MS SQL Server? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Sybase Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |