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