用TSQL建立50萬筆紀錄的大資料表
經常做資料庫效能測試時，需要用到資料量很大的資料表，自己動手寫一段 TSQL 語法即可。TSQL_建立資料表結構：
[code]/**//****** 對象: 表 [dbo].[LargeTable] 腳本日期: 2006-10-26 15:40:27 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LargeTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LargeTable]
GO
/**//****** 對象: 表 [dbo].[LargeTable] 腳本日期: 2006-10-26 15:40:27 ******/
CREATE TABLE [dbo].[LargeTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Content] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
[PublicTime] [datetime] NULL ,
[Author] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[IsTop] [tinyint] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[LargeTable] WITH NOCHECK ADD CONSTRAINT [PK_LargeTable] PRIMARY KEY CLUSTERED ([ID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[LargeTable] ADD CONSTRAINT [DF_LargeTable_IsTop] DEFAULT (0) FOR [IsTop]
GO
exec sp_addextendedproperty N'MS_Description', N'作者', N'user', N'dbo', N'table', N'LargeTable', N'column', N'Author'
GO
exec sp_addextendedproperty N'MS_Description', N'内容', N'user', N'dbo', N'table', N'LargeTable', N'column', N'Content'
GO
exec sp_addextendedproperty N'MS_Description', N'文章表，包含100萬筆紀錄', N'user', N'dbo', N'table', N'LargeTable', N'column', N'ID'
GO
exec sp_addextendedproperty N'MS_Description', N'是否置頂 0.不置頂 1.置頂', N'user', N'dbo', N'table', N'LargeTable', N'column', N'IsTop'
GO
exec sp_addextendedproperty N'MS_Description', N'發佈時間', N'user', N'dbo', N'table', N'LargeTable', N'column', N'PublicTime'
GO
exec sp_addextendedproperty N'MS_Description', N'文章標題', N'user', N'dbo', N'table', N'LargeTable', N'column', N'Title'
GO[/code]
TSQL_產生資料：
[code]/**//*truncate table largetable*/
declare @title nvarchar(100)
declare @content nvarchar(100)
declare @publictime datetime
declare @author nvarchar(10)
declare @istop tinyint
declare @randtime_month tinyint
declare @randtime_day tinyint
declare @randtime_hour tinyint
declare @randtime_minute tinyint
declare @randtime_second tinyint
declare @str varchar(30)
print '開始執行時間：' + cast(getdate() as varchar)
declare @i int, @count int
set @i = 1
set @count = 500000
while @i <= @count
begin
set @randtime_month = rand(@i) * 12
set @randtime_day = rand(@i) * 28
set @randtime_hour = rand(@i) * 24
set @randtime_minute = rand(@i) * 60
set @randtime_second = rand(@i) * 60
set @str = '2006-' + cast(@randtime_month as varchar) + '-' + cast(@randtime_day as varchar) + ' ' + cast(@randtime_hour as varchar) + ':' + cast(@randtime_minute as varchar) + ':' + cast(@randtime_second as varchar)
set @title = '文章標題' + cast(@i as varchar)
set @content = '文章内容' + cast(@i as varchar)
set @publictime = convert(datetime, @str, 120)
set @author = '作者' + cast(@i as varchar)
if @i % 10000 = 0
set @istop = 1
else
set @istop = 0
insert into largetable values(@title, @content, @publictime, @author, @istop)
set @i=@i+1
end
print '執行完畢時間：' + cast(getdate() as varchar)[/code]
頁:
[1]