MS SQL Server Concepts and Programming Question:

Answer #1
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 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

Answer #2
-- 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

Answer #3
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>