# VBForums CodeBank > CodeBank - Other >  SQL Server 2005 - Generate Keys

## CVMichael

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.


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

----------

