-
Jan 24th, 2009, 01:48 PM
#1
SQL Server 2005 - Generate Keys
On my comptuer, this code generates 1,000,000 keys in 1:30 minutes
There are many ways to generate keys, this code generates by alternating between alpha characters and numeric characters.
Code:
-- =============================================
-- Author: <Michael Ciurescu>
-- Create date: <20090124>
-- Description: <Generate Keys in dbo.NewKeys table>
-- =============================================
/*
-- Our NewKeys table:
CREATE TABLE [dbo].[NewKeys](
[KeyID] INT IDENTITY(1,1) NOT NULL,
[Key] VARCHAR(50) NOT NULL,
CONSTRAINT [PK_NewKeys] PRIMARY KEY NONCLUSTERED ([KeyID] ASC)
)
CREATE UNIQUE CLUSTERED INDEX [IN_NewKeys_Key] ON [dbo].[NewKeys] ([Key] ASC)
*/
CREATE PROCEDURE [dbo].[spx_CreateKeys]
@TotalKeys INT
, @KeyLength INT
, @Recursive INT = 10
AS
BEGIN
SET NOCOUNT ON;
/*
spx_CreateKeys 1000000, 7, 10
SELECT TOP 10000 * FROM dbo.NewKeys ORDER BY KeyID
SELECT TOP 10000 * FROM dbo.NewKeys ORDER BY KeyID DESC
ALTER INDEX ALL ON dbo.NewKeys REBUILD
TRUNCATE TABLE dbo.NewKeys
*/
DECLARE @i INT
, @q INT
, @Key VARCHAR(50)
, @AlphaNum VARCHAR(40)
, @NumAlpha VARCHAR(40)
, @TotalInserted INT
, @StartTime DATETIME
, @EndTime DATETIME
, @RecordsPerSec FLOAT
, @TotalDeleted INT
-- create our temp table where our keys will be inserted first
DECLARE @TmpKeys TABLE (ID INT PRIMARY KEY IDENTITY(1,1), [Key] VARCHAR(50))
--DECLARE @DelKeys TABLE (ID INT PRIMARY KEY, [Key] VARCHAR(50))
SET @StartTime = GetDate()
SET @AlphaNum = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
SET @NumAlpha = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
SET @i = 1
WHILE @i <= @TotalKeys BEGIN
-- depending on your requirments, this section you should change
SET @q = 0
SET @Key = ''
WHILE @q <= @KeyLength BEGIN
SET @Key = @Key + SUBSTRING(@AlphaNum, CONVERT(INT, 26 * RAND(CHECKSUM(NEWID())) + 1), 1)
+ SUBSTRING(@NumAlpha, CONVERT(INT, 10 * RAND(CHECKSUM(NEWID())) + 1), 1)
SET @q = @q + 2
END
INSERT INTO @TmpKeys
SELECT CASE WHEN CONVERT(INT, 2 * RAND(CHECKSUM(NEWID()))) = 0 THEN
LEFT(@Key, @KeyLength) ELSE SUBSTRING(@Key, 2, @KeyLength) END
SET @i = @i + 1
END
-- Delete duplicate keys from the temp table
DELETE t
-- OUTPUT DELETED.ID, DELETED.[Key]
-- INTO @DelKeys
FROM @TmpKeys AS t
INNER JOIN (
SELECT MIN(ID) AS MinID, [Key]
FROM @TmpKeys
GROUP BY [Key]
HAVING Count(*) > 1
) AS d ON t.[Key] = d.[Key]
WHERE d.MinID <> t.ID
-- Insert only new keys into dbo.NewKeys table (no duplicates)
INSERT INTO dbo.NewKeys ([Key])
SELECT t.[Key]
FROM @TmpKeys AS t
LEFT JOIN dbo.NewKeys AS n ON t.[Key] = n.[Key]
WHERE n.[Key] IS NULL
ORDER BY t.ID
SET @TotalInserted = @@ROWCOUNT
-- calculate speed
SET @EndTime = GetDate()
SET @RecordsPerSec = CASE WHEN DATEDIFF(ms, @StartTime, @EndTime) > 0 THEN
1000.0 * @TotalInserted / DATEDIFF(ms, @StartTime, @EndTime) ELSE NULL END
SET @TotalDeleted = @TotalKeys - @TotalInserted
SET @Recursive = @Recursive - 1
-- execute again if we have less keys than we should (i.e. if there were duplicates)
IF ISNULL(@Recursive, 0) > 0 AND @TotalDeleted > 0
EXEC dbo.spx_CreateKeys @TotalDeleted, @KeyLength, @Recursive
-- diplay info on what was done
SELECT @TotalInserted AS [Records Inserted]
, @TotalDeleted AS [Total Deleted]
, @RecordsPerSec AS [Keys / Second]
, DATEDIFF(ms, @StartTime, @EndTime) / 1000.0 AS [Duration]
END
Last edited by CVMichael; Jan 24th, 2009 at 11:14 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|