△经常做数据库访问性能测试时,需要用到数据...... 经常做数据库访问性能测试时,需要用到数据量很大的表,自己动手写一段TSQL语句即可。 TSQL_生成表结构: /**//****** 对象: 表 [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 TSQL_生成表数据: /**//*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) |
|小黑屋|最新主题|手机版|微赢网络技术论坛 ( 苏ICP备08020429号 )
GMT+8, 2024-9-30 11:32 , Processed in 0.122604 second(s), 12 queries , Gzip On, MemCache On.
Powered by Discuz! X3.5
© 2001-2023 Discuz! Team.