生成50万条记录的大数据表的tsql语句_数据库技巧

经常做数据库访问性能测试时，需要用到数据量很大的表，自己动手写一段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, NIsUserTable) = 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 NMS_Description, N作者, Nuser, Ndbo, Ntable, NLargeTable, Ncolumn, NAuthor
GO
exec sp_addextendedproperty NMS_Description, N内容, Nuser, Ndbo, Ntable, NLargeTable, Ncolumn, NContent
GO
exec sp_addextendedproperty NMS_Description, N文章表，包含100万条记录, Nuser, Ndbo, Ntable, NLargeTable, Ncolumn, NID
GO
exec sp_addextendedproperty NMS_Description, N是否置顶 0.不置顶 1.置顶, Nuser, Ndbo, Ntable, NLargeTable, Ncolumn, NIsTop
GO
exec sp_addextendedproperty NMS_Description, N发布时间, Nuser, Ndbo, Ntable, NLargeTable, Ncolumn, NPublicTime
GO
exec sp_addextendedproperty NMS_Description, N文章标题, Nuser, Ndbo, Ntable, NLargeTable, Ncolumn, NTitle

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)

http://www.cnblogs.com/jiny-z/archive/2006/10/26/540801.html