RECOMPUTE = 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
|