Random FN and Data Generator

CREATE TABLE jk_RecoveryTest (
ident INT IDENTITY (1, 1)
, cInt INT DEFAULT 0
, cFloat FLOAT DEFAULT 0
, cDatetime DATETIME DEFAULT GETDATE()
, cChar CHAR (4)
, cVarChar VARCHAR (128)
, cVarCharMax VARCHAR (MAX)
, cStart DATETIME DEFAULT GETDATE()
, cEnd DATETIME DEFAULT GETDATE()
, iteration INT DEFAULT 0
, uDate DATETIME DEFAULT GETDATE()
, crDate DATETIME DEFAULT GETDATE()
)

GO

CREATE TABLE jk_RecoveryStats (
cVarChar VARCHAR (1023)
, cStart DATETIME DEFAULT GETDATE()
, cEnd DATETIME DEFAULT GETDATE()
, iteration INT DEFAULT 0
, uDate DATETIME DEFAULT GETDATE()
, crDate DATETIME DEFAULT GETDATE()
)

GO

CREATE VIEW dbo.vRandNumber
AS
SELECT RAND() RandNumber

GO

CREATE FUNCTION RandNumber()
RETURNS float
AS
BEGIN
RETURN (SELECT RandNumber
FROM dbo.vRandNumber)
END

GO

CREATE FUNCTION RandNumberRng(@Min int, @Max int)
RETURNS float
AS
BEGIN
RETURN @Min
+ ( SELECT RandNumber
FROM dbo.vRandNumber)
* (@Max-@Min)
END




TRUNCATE TABLE jk_RecoveryTest

TRUNCATE TABLE jk_RecoveryStats

-- SELECT * FROM jk_RecoveryTest

SET NOCOUNT ON

DECLARE @iter INT = 1
, @outerIter INT = 1
, @stop INT = 10000--0
, @outerStop INT = 10--00 0
, @tDate DATETIME = GETDATE()
, @tString VARCHAR (128) = 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwx'
, @tInt INT = 0

WHILE (@outerIter <= @outerStop)
BEGIN

WHILE (@iter <= @stop)
BEGIN

INSERT jk_RecoveryTest (
cInt
, cFloat
, cDatetime
, cChar
, cVarChar
, cVarCharMax
, cStart
, cEnd
, iteration
)
SELECT
CAST(dbo.RandNumberRng (0, 9) AS INT)
, dbo.RandNumberRng (0, 9)
, DATEADD(dd, dbo.RandNumberRng (0, 28), @tDate)
, LEFT (@tString, dbo.RandNumberRng (0, 4))
, LEFT (@tString, dbo.RandNumberRng (0, 128))
, LEFT (@tString, dbo.RandNumberRng (0, 128))
, @tDate
, @tDate
, @iter

SET @iter += 1
END

--SELECT 'Inserts'
--, DATEDIFF (ss, @tDate, GETDATE())
--, DATEDIFF (ms, @tDate, GETDATE()) % 1000
--SET @tDate = GETDATE()

SET @iter = 1

WHILE (@iter <= @stop)
BEGIN

SELECT @tInt = AVG(cFloat)
-- SELECT AVG(cFloat)
FROM jk_RecoveryTest
WHERE cInt % @iter = 2

SET @iter += 1

END

--SELECT 'Seeks'
--, DATEDIFF (ss, @tDate, GETDATE())
--, DATEDIFF (ms, @tDate, GETDATE()) % 1000

--INSERT jk_RecoveryStats (
-- cVarChar
-- , cStart
-- , cEnd
-- , iteration
-- )
--SELECT
-- 'FULL - Seeks'
-- , @tDate
-- , GETDATE()
-- , @outerIter

--SET @tDate = GETDATE()

SET @iter = 1

WHILE (@iter <= @stop)
BEGIN

UPDATE jk_RecoveryTest
SET cInt = dbo.RandNumberRng (0, @iter)
WHERE ident = @iter

SET @iter += 1

END

--SELECT 'Updates'
--, DATEDIFF (ss, @tDate, GETDATE())
--, DATEDIFF (ms, @tDate, GETDATE()) % 1000

--INSERT jk_RecoveryStats (
-- cVarChar
-- , cStart
-- , cEnd
-- , iteration
-- )
--SELECT
-- 'FULL - Updates'
-- , @tDate
-- , GETDATE()
-- , @outerIter

--SET @tDate = GETDATE()

SET @iter = 1

WHILE (@iter <= @stop)
BEGIN

DELETE
FROM jk_RecoveryTest
WHERE ident = @iter

SET @iter += 1

END

--SELECT 'Deletes'
--, DATEDIFF (ss, @tDate, GETDATE())
--, DATEDIFF (ms, @tDate, GETDATE()) % 1000

INSERT jk_RecoveryStats (
cVarChar
, cStart
, cEnd
, cDiff
, iteration
)
SELECT
-- 'FULL - Deletes'
'FULL'
, @tDate
, GETDATE()
, DATEDIFF (ms, @tDate, GETDATE())
, @outerIter

SET @tDate = GETDATE()

SET @iter = 1

SET @outerIter += 1

END


SELECT cVarChar, AVG(cDiff)
FROM jk_RecoveryStats
GROUP BY cVarChar