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](