DataTest

USE master
GO
IF EXISTS(SELECT * FROM sys.databases WHERE [name] = 'TempData')
    DROP DATABASE TempData
GO
CREATE DATABASE TempData
ON PRIMARY
(
    NAME = 'TempData_Data',
    FILENAME = 'D:\Cases\S2E97SQL\TempData_Data.mdf',
    SIZE = 3,
    FILEGROWTH = 10%
)
LOG ON
(
    NAME = 'TempData_Log',
    FILENAME = 'D:\Cases\S2E97SQL\TempData_Log.ldf',
    SIZE = 1,
    FILEGROWTH = 10%   
)
GO

use TempData
GO
if exists(SELECT * FROM sysobjects where [name] = 'tempTable')
    DROP TABLE tempTable
GO
CREATE TABLE tempTable
(
    tid INT IDENTITY(1,1),
    tname VARCHAR(30) not null,
    tsex bit not null,
    tage INT,
    ttel VARCHAR(13),   
    taddress VARCHAR(50),
)
GO
CREATE TABLE randTable
(
    rid INT IDENTITY(1,1),
    rname VARCHAR(10)
)
GO

CREATE TABLE randName
(
    rid INT IDENTITY(1,1),
    rname VARCHAR(10)
)
GO

INSERT INTO randTable VALUES('赵')
INSERT INTO randTable VALUES('钱')
INSERT INTO randTable VALUES('孙')
INSERT INTO randTable VALUES('李')
INSERT INTO randTable VALUES('周')
INSERT INTO randTable VALUES('吴')
INSERT INTO randTable VALUES('郑')
INSERT INTO randTable VALUES('王')
INSERT INTO randTable VALUES('冯')
INSERT INTO randTable VALUES('陈')
INSERT INTO randTable VALUES('禇')
INSERT INTO randTable VALUES('卫')
INSERT INTO randTable VALUES('蒋')
INSERT INTO randTable VALUES('沈')
INSERT INTO randTable VALUES('韩')
INSERT INTO randTable VALUES('杨')
INSERT INTO randTable VALUES('朱')
INSERT INTO randTable VALUES('秦')
INSERT INTO randTable VALUES('尤')
INSERT INTO randTable VALUES('许')
INSERT INTO randTable VALUES('何')
INSERT INTO randTable VALUES('吕')
INSERT INTO randTable VALUES('施')
INSERT INTO randTable VALUES('张')
INSERT INTO randTable VALUES('孔')
INSERT INTO randTable VALUES('曹')
INSERT INTO randTable VALUES('严')
INSERT INTO randTable VALUES('华')
INSERT INTO randTable VALUES('金')
INSERT INTO randTable VALUES('魏')
INSERT INTO randTable VALUES('陶')
INSERT INTO randTable VALUES('姜')
INSERT INTO randTable VALUES('戚')
INSERT INTO randTable VALUES('谢')
INSERT INTO randTable VALUES('邹')
INSERT INTO randTable VALUES('喻')
INSERT INTO randTable VALUES('柏')
INSERT INTO randTable VALUES('水')
INSERT INTO randTable VALUES('窦')
INSERT INTO randTable VALUES('章')
INSERT INTO randTable VALUES('云')
INSERT INTO randTable VALUES('苏')
INSERT INTO randTable VALUES('潘')
INSERT INTO randTable VALUES('葛')
INSERT INTO randTable VALUES('奚')
INSERT INTO randTable VALUES('范')
INSERT INTO randTable VALUES('彭')
INSERT INTO randTable VALUES('鲁')
INSERT INTO randTable VALUES('韦')
INSERT INTO randTable VALUES('昌')
INSERT INTO randTable VALUES('马')
INSERT INTO randTable VALUES('苗')
INSERT INTO randTable VALUES('凤')
INSERT INTO randTable VALUES('花')
INSERT INTO randTable VALUES('方')
INSERT INTO randTable VALUES('曾')
INSERT INTO randTable VALUES('皮')
INSERT INTO randTable VALUES('伍')
INSERT INTO randTable VALUES('元')
INSERT INTO randTable VALUES('平')
INSERT INTO randTable VALUES('米')
INSERT INTO randTable VALUES('明')
INSERT INTO randTable VALUES('祝')
INSERT INTO randTable VALUES('谈')
INSERT INTO randTable VALUES('宋')
INSERT INTO randTable VALUES('茅')
INSERT INTO randTable VALUES('汪')
INSERT INTO randTable VALUES('计')
INSERT INTO randTable VALUES('伏')
INSERT INTO randTable VALUES('乐')
INSERT INTO randTable VALUES('于')
INSERT INTO randTable VALUES('时')
INSERT INTO randTable VALUES('汤')
INSERT INTO randTable VALUES('罗')
INSERT INTO randTable VALUES('毕')
INSERT INTO randTable VALUES('史')
INSERT INTO randTable VALUES('唐')
INSERT INTO randTable VALUES('安')
INSERT INTO randTable VALUES('常')
INSERT INTO randTable VALUES('纪')
INSERT INTO randTable VALUES('庞')
INSERT INTO randTable VALUES('成')
INSERT INTO randTable VALUES('各')
INSERT INTO randTable VALUES('邵')
INSERT INTO randTable VALUES('余')
INSERT INTO randTable VALUES('贺')
INSERT INTO randTable VALUES('任')
INSERT INTO randTable VALUES('袁')
INSERT INTO randTable VALUES('汲')
INSERT INTO randTable VALUES('邓')
INSERT INTO randTable VALUES('黄')
INSERT INTO randTable VALUES('万')
INSERT INTO randTable VALUES('卢')
INSERT INTO randTable VALUES('宗')
INSERT INTO randTable VALUES('江')
GO

INSERT INTO randName VALUES('赵')
INSERT INTO randName VALUES('钱')
INSERT INTO randName VALUES('孙')
INSERT INTO randName VALUES('李')
INSERT INTO randName VALUES('周')
INSERT INTO randName VALUES('吴')
INSERT INTO randName VALUES('郑')
INSERT INTO randName VALUES('王')
INSERT INTO randName VALUES('冯')
INSERT INTO randName VALUES('陈')
INSERT INTO randName VALUES('禇')
INSERT INTO randName VALUES('卫')
INSERT INTO randName VALUES('蒋')
INSERT INTO randName VALUES('沈')
INSERT INTO randName VALUES('韩')
INSERT INTO randName VALUES('杨')
INSERT INTO randName VALUES('朱')
INSERT INTO randName VALUES('秦')
INSERT INTO randName VALUES('尤')
INSERT INTO randName VALUES('许')
INSERT INTO randName VALUES('何')
INSERT INTO randName VALUES('吕')
INSERT INTO randName VALUES('施')
INSERT INTO randName VALUES('张')
INSERT INTO randName VALUES('孔')
INSERT INTO randName VALUES('曹')
INSERT INTO randName VALUES('严')
INSERT INTO randName VALUES('华')
INSERT INTO randName VALUES('金')
INSERT INTO randName VALUES('魏')
INSERT INTO randName VALUES('陶')
INSERT INTO randName VALUES('姜')
INSERT INTO randName VALUES('戚')
INSERT INTO randName VALUES('谢')
INSERT INTO randName VALUES('邹')
INSERT INTO randName VALUES('喻')
INSERT INTO randName VALUES('柏')
INSERT INTO randName VALUES('水')
INSERT INTO randName VALUES('窦')
INSERT INTO randName VALUES('章')
INSERT INTO randName VALUES('云')
INSERT INTO randName VALUES('苏')
INSERT INTO randName VALUES('潘')
INSERT INTO randName VALUES('葛')
INSERT INTO randName VALUES('奚')
INSERT INTO randName VALUES('范')
INSERT INTO randName VALUES('彭')
INSERT INTO randName VALUES('鲁')
INSERT INTO randName VALUES('韦')
INSERT INTO randName VALUES('昌')
INSERT INTO randName VALUES('马')
INSERT INTO randName VALUES('苗')
INSERT INTO randName VALUES('凤')
INSERT INTO randName VALUES('花')
INSERT INTO randName VALUES('方')
INSERT INTO randName VALUES('曾')
INSERT INTO randName VALUES('皮')
INSERT INTO randName VALUES('伍')
INSERT INTO randName VALUES('元')
INSERT INTO randName VALUES('平')
INSERT INTO randName VALUES('米')
INSERT INTO randName VALUES('明')
INSERT INTO randName VALUES('祝')
INSERT INTO randName VALUES('谈')
INSERT INTO randName VALUES('宋')
INSERT INTO randName VALUES('茅')
INSERT INTO randName VALUES('汪')
INSERT INTO randName VALUES('计')
INSERT INTO randName VALUES('伏')
INSERT INTO randName VALUES('乐')
INSERT INTO randName VALUES('于')
INSERT INTO randName VALUES('时')
INSERT INTO randName VALUES('汤')
INSERT INTO randName VALUES('罗')
INSERT INTO randName VALUES('毕')
INSERT INTO randName VALUES('史')
INSERT INTO randName VALUES('唐')
INSERT INTO randName VALUES('安')
INSERT INTO randName VALUES('常')
INSERT INTO randName VALUES('纪')
INSERT INTO randName VALUES('庞')
INSERT INTO randName VALUES('成')
INSERT INTO randName VALUES('各')
INSERT INTO randName VALUES('邵')
INSERT INTO randName VALUES('余')
INSERT INTO randName VALUES('贺')
INSERT INTO randName VALUES('任')
INSERT INTO randName VALUES('袁')
INSERT INTO randName VALUES('汲')
INSERT INTO randName VALUES('邓')
INSERT INTO randName VALUES('黄')
INSERT INTO randName VALUES('万')
INSERT INTO randName VALUES('卢')
INSERT INTO randName VALUES('宗')
INSERT INTO randName VALUES('智')
INSERT INTO randName VALUES('琴')
INSERT INTO randName VALUES('波')
INSERT INTO randName VALUES('鹏')
INSERT INTO randName VALUES('伟')
INSERT INTO randName VALUES('俊')
INSERT INTO randName VALUES('鑫')
INSERT INTO randName VALUES('玉')
INSERT INTO randName VALUES('文')
INSERT INTO randName VALUES('学')
INSERT INTO randName VALUES('军')
INSERT INTO randName VALUES('志')
INSERT INTO randName VALUES('江')
GO

SELECT count(*) FROM randTable
SELECT count(*) FROM randName


DECLARE @ifname INT
DECLARE @ilname INT
DECLARE @fname VARCHAR(10)
DECLARE @lname VARCHAR(10)
DECLARE @tel numeric(27,13)
DECLARE @stel VARCHAR(20)
DECLARE @isex INT
DECLARE @i INT
DECLARE @iage INT
SET @i = 1
while(@i <= 1000000)
begin
    SET @ifname = rand() * 95 + 1
    SET @ilname = rand() * 107 + 1
    SET @isex = rand() * 2
    SET @tel = rand()
    SET @stel =  substring(CONVERT(VARCHAR(15),@tel),3,13)
    SET @iage = rand() * 82+ 18
    SELECT @fname = rname FROM randTable where rid = @ifname
    SELECT @lname = rname FROM randName where rid = @ilname
    INSERT INTO tempTable VALUES(@fname + @lname,@isex,@iage,@stel,'')
    SET @i = @i + 1
end
GO

SELECT COUNT(*) FROM tempTable

DECLARE @tbegin DATETIME
SET @tbegin = GETDATE()
SELECT * FROM tempTable
PRINT '查询所花时间为：' + CONVERT(VARCHAR(50),datediff(ms,@tbegin,GETDATE()))
GO

ALTER TABLE tempTable
    add CONSTRAINT pk_tid primary key(tid)
GO

ALTER TABLE tempTable
    DROP CONSTRAINT pk_tid
GO

CREATE CLUSTERED INDEX INDEX_tname
    ON tempTable(tname)
    WITH FILLFACTOR = 30
GO

DECLARE @tbegin DATETIME
SET @tbegin = GETDATE()
SELECT * FROM tempTable
    WITH (INDEX = INDEX_tname)
    ORDER BY tname
PRINT '查询所花时间为：' + CONVERT(VARCHAR(50),datediff(ms,@tbegin,GETDATE()))
GO