用TSQL建立50萬筆紀錄的大資料表

bestlong

經常做資料庫效能測試時，需要用到資料量很大的資料表，自己動手寫一段 TSQL 語法即可。

TSQL_建立資料表結構：

1. /**//****** 對象: 表 [dbo].[LargeTable]    腳本日期: 2006-10-26 15:40:27 ******/
   
2. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LargeTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   
3. drop table [dbo].[LargeTable]
   
4. GO
   
5. 
   
6. /**//****** 對象: 表 [dbo].[LargeTable]    腳本日期: 2006-10-26 15:40:27 ******/
   
7. CREATE TABLE [dbo].[LargeTable] (
   
8.   [ID] [int] IDENTITY (1, 1) NOT NULL ,
   
9.   [Title] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
   
10.   [Content] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
    
11.   [PublicTime] [datetime] NULL ,
    
12.   [Author] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
    
13.   [IsTop] [tinyint] NULL
    
14. ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
15. GO
    
16. 
    
17. ALTER TABLE [dbo].[LargeTable] WITH NOCHECK ADD CONSTRAINT [PK_LargeTable] PRIMARY KEY  CLUSTERED ([ID])  ON [PRIMARY]
    
18. GO
    
19. 
    
20. ALTER TABLE [dbo].[LargeTable] ADD CONSTRAINT [DF_LargeTable_IsTop] DEFAULT (0) FOR [IsTop]
    
21. GO
    
22. 
    
23. exec sp_addextendedproperty N'MS_Description', N'作者', N'user', N'dbo', N'table', N'LargeTable', N'column', N'Author'
    
24. GO
    
25. exec sp_addextendedproperty N'MS_Description', N'内容', N'user', N'dbo', N'table', N'LargeTable', N'column', N'Content'
    
26. GO
    
27. exec sp_addextendedproperty N'MS_Description', N'文章表，包含100萬筆紀錄', N'user', N'dbo', N'table', N'LargeTable', N'column', N'ID'
    
28. GO
    
29. exec sp_addextendedproperty N'MS_Description', N'是否置頂 0.不置頂 1.置頂', N'user', N'dbo', N'table', N'LargeTable', N'column', N'IsTop'
    
30. GO
    
31. exec sp_addextendedproperty N'MS_Description', N'發佈時間', N'user', N'dbo', N'table', N'LargeTable', N'column', N'PublicTime'
    
32. GO
    
33. exec sp_addextendedproperty N'MS_Description', N'文章標題', N'user', N'dbo', N'table', N'LargeTable', N'column', N'Title'
    
34. GO

複製代碼

TSQL_產生資料：

1. /**//*truncate table largetable*/
   
2. 
   
3. declare @title nvarchar(100)
   
4. declare @content nvarchar(100)
   
5. declare @publictime datetime
   
6. declare @author nvarchar(10)
   
7. declare @istop tinyint
   
8. 
   
9. declare @randtime_month tinyint
   
10. declare @randtime_day tinyint
    
11. declare @randtime_hour tinyint
    
12. declare @randtime_minute tinyint
    
13. declare @randtime_second tinyint
    
14. 
    
15. declare @str varchar(30)
    
16. 
    
17. print '開始執行時間：' + cast(getdate() as varchar)
    
18. declare @i int, @count int
    
19. set @i = 1
    
20. set @count = 500000
    
21. while @i <= @count
    
22. begin
    
23.   set @randtime_month = rand(@i) * 12
    
24.   set @randtime_day   = rand(@i) * 28
    
25.   set @randtime_hour  = rand(@i) * 24
    
26.   set @randtime_minute = rand(@i) * 60
    
27.   set @randtime_second = rand(@i) * 60
    
28.   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)
    
29. 
    
30.   set @title = '文章標題' + cast(@i as varchar)
    
31.   set @content = '文章内容' + cast(@i as varchar)
    
32.   set @publictime = convert(datetime, @str, 120)
    
33.   set @author = '作者' + cast(@i as varchar)
    
34.   if @i % 10000 = 0
    
35.     set @istop = 1
    
36.   else
    
37.     set @istop = 0
    
38. 
    
39.   insert into largetable values(@title, @content, @publictime, @author, @istop)
    
40. 
    
41.   set @i=@i+1
    
42. end
    
43. print '執行完畢時間：' + cast(getdate() as varchar)

複製代碼