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_NO |