T-SQL语句创建索引

	SET NOCOUNT ON
	-- Configure users
	IF (OBJECT_ID('dbo.users') IS NOT NULL)
	BEGIN
	DROP TABLE dbo.[users]
	END
	GO
	SET ANSI_PADDING ON
	GO
	CREATE TABLE [dbo].[users](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[user_name] [varchar](15) NOT NULL,
	CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
	(
	[id] ASC
	)WITH (FILLFACTOR = 100, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
	GO
	DECLARE @UserCount int
	DECLARE @TotalUserCount int
	SET @TotalUserCount = 500000
	SET @UserCount = 0
	WHILE (@UserCount < @TotalUserCount)
	BEGIN
	SET @UserCount = @UserCount + 1
	INSERT INTO dbo.[users](user_name)
	VALUES('DiggUser'+CONVERT(char(18), @UserCount))
	END
	-- Configure friends
	IF (OBJECT_ID('dbo.friends') IS NOT NULL)
	BEGIN
	DROP TABLE dbo.[friends]
	END
	GO
	CREATE TABLE [dbo].[friends](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[user_id] [int] NOT NULL,
	[user_name] [varchar](15) NOT NULL,
	[friend_id] [int] NOT NULL,
	[friend_name] [varchar](15) NOT NULL,
	[mutual] [bit] NOT NULL,
	[date_created] [datetime] NOT NULL,
	CONSTRAINT [PK_friends] PRIMARY KEY CLUSTERED
	(
	[id] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
	GO
	-- Setup friend relationships. Power law.
	DECLARE @UserCount int
	DECLARE @TotalUserCount int
	SELECT @TotalUserCount = MAX(id) FROM users
	DECLARE @FriendCount int
	DECLARE @FriendList table(friend_id int)
	DECLARE @FriendDate datetime
	SET @FriendDate = GETUTCDATE()
	TRUNCATE TABLE friends
	DECLARE @Base float
	SET @Base = (@TotalUserCount + 1000) / 1000
	-- Setup friend relationships. Power law.
	SET @UserCount = 0
	WHILE (@UserCount < @TotalUserCount)
	BEGIN
	SET @UserCount = @UserCount + 1
	SET @FriendCount = POWER(100, RAND())
	DELETE FROM @FriendList
	WHILE (@FriendCount > 0)
	BEGIN
	INSERT INTO @FriendList(friend_id)
	VALUES(CONVERT(int, POWER(@Base, RAND())*1000)-1000)
	SET @FriendCount = @FriendCount - 1
	END
	INSERT INTO dbo.friends(user_id, user_name, friend_id, friend_name, mutual, date_created)
	SELECT DISTINCT base.id, base.user_name, friend.id, friend.user_name, 0, @FriendDate
	FROM
	@FriendList f
	JOIN dbo.users base ON base.id = @UserCount
	JOIN dbo.users friend ON friend.id = f.friend_id
	END
	GO
	CREATE UNIQUE NONCLUSTERED INDEX [IX_User_To_Friend] ON [dbo].[friends]
	(
	[user_id] ASC,
	[friend_id] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	GO
	CREATE NONCLUSTERED INDEX [IX_Friend] ON [dbo].[friends]
	(
	[friend_id] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	GO
	-- Diggs
	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	IF (OBJECT_ID('dbo.diggs') IS NOT NULL)
	BEGIN
	DROP TABLE dbo.[diggs]
	END
	GO
	CREATE TABLE [dbo].[diggs](
	[id] [int] NOT NULL IDENTITY(1,1),
	[item_id] [int] NOT NULL,
	[user_id] [int] NOT NULL,
	[digdate] [datetime] NOT NULL,
	CONSTRAINT [PK_diggs] PRIMARY KEY CLUSTERED
	(
	[id] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
	GO
	SET NOCOUNT ON
	GO
	-- Create ~five hundred million diggs.
	DECLARE @ItemCount int
	DECLARE @TotalItemCount int
	DECLARE @TotalUserCount int
	DECLARE @Diggs int
	DECLARE @UserDiggs table(user_id int, digdate datetime)
	DECLARE @BaseDate datetime
	SET @BaseDate = '2007-01-01'
	SELECT @TotalUserCount = MAX(id) FROM users
	SET @ItemCount = 1
	SET @TotalItemCount = 500000
	WHILE (@ItemCount < @TotalItemCount)
	BEGIN
	SET @Diggs = RAND() * 2000.0
	DELETE FROM @UserDiggs
	WHILE (@Diggs > 0)
	BEGIN
	INSERT INTO @UserDiggs(user_id, digdate)
	VALUES(RAND()*@TotalUserCount+1, DATEADD(minute, RAND()*2102400, @BaseDate))
	SET @Diggs = @Diggs - 1
	END
	INSERT INTO diggs(item_id, user_id, digdate)
	SELECT
	@ItemCount, user_id, MAX(digdate)
	FROM
	@UserDiggs
	GROUP BY
	user_id
	SET @ItemCount = @ItemCount + 1
	END
	GO
	CREATE NONCLUSTERED INDEX [IX_diggs_item] ON [dbo].[diggs]
	(
	[item_id] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	GO
	CREATE NONCLUSTERED INDEX [IX_diggs_user] ON [dbo].[diggs]
	(
	[user_id] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	GO