Mike C (6/29/2010)
How's this for data? 🙂
CREATE TABLE #Role
(
RoleName VARCHAR(100)
);
GO
INSERT INTO #Role
(
RoleName
)
VALUES ('Accounting'), ('Approver'), ('Developer'), ('International Sales Manager'), ('Marketing'), ('System Administrator'),
('Technical Customer'), ('Technical Director'), ('Training');
GO
CREATE TABLE #UserRole
(
UserID INT NOT NULL,
RoleName VARCHAR(100) NOT NULL,
ProjectID INT NOT NULL,
RoleAssignedDate DATETIME NOT NULL,
PRIMARY KEY
(
UserID,
RoleName,
ProjectID,
RoleAssignedDate
)
);
GO
CREATE TABLE #Numbers
(
Num INT NOT NULL PRIMARY KEY
);
GO
WITH DIGITS
AS
(
SELECT 0 AS Num
UNION ALL
SELECT Num + 1
FROM DIGITS
WHERE Num < 10
)
INSERT INTO #Numbers
(
Num
)
SELECT Num
FROM DIGITS;
GO
WITH NUMBERS
AS
(
SELECT HundredThousand.Num * 100000 + TenThousand.Num * 10000 +
Thousand.Num * 1000 + Hundred.Num * 100 +
Ten.Num + One.Num AS Num
FROM #Numbers HundredThousand
CROSS JOIN #Numbers TenThousand
CROSS JOIN #Numbers Thousand
CROSS JOIN #Numbers Hundred
CROSS JOIN #Numbers Ten
CROSS JOIN #Numbers One
),
RANDOMDATA
AS
(
SELECT Num AS UserID,
RoleName,
ABS(CHECKSUM(NEWID()) % 8000) + 1000 AS ProjectID,
DATEADD(DAY, -ABS(CHECKSUM(NEWID()) % 1000), GETDATE()) AS RoleAssignedDate,
NEWID() AS SortOrder
FROM NUMBERS
CROSS JOIN #Role
)
INSERT INTO #UserRole
(
UserID,
RoleName,
ProjectID,
RoleAssignedDate
)
SELECT TOP(1000000) UserID,
RoleName,
ProjectID,
RoleAssignedDate
FROM RANDOMDATA
ORDER BY SortOrder;
GO
Nice to see someone else ramp it up for a change. Nicely done Michael. 😀
The next thing we need to teach is that returning results to the screen is a performance testing error known as "The Great Equalizer". :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.