Results 1 to 1 of 1

Thread: SQL Server 2005 - Generate Keys

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    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
  •  



Click Here to Expand Forum to Full Width