March 26, 2013 at 8:09 am
nitin_456 (3/25/2013)
So the idea here is to Generate 2 -3 million consecutive unique numbers by using 5 Alpha Numeric digit. Please if you can help around this will be great.
I think that if you are dead set on proceeding with this you should consider the idea that Scott suggested of creating a table of all the keys and pull rows from that as you need them.
Let's consider how horrible it is to use alphanumeric values like this. Not only is it incredibly difficult to generate the values you run the risk of seriously offending people.
Think of all of the very offensive values you can make out of 6 characters starting with E. Or even the values that can happen all over the place. EVL666, E69SEX
Beginning to see how awful this can be?
What is so wrong with using an identity or finding a natural key?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 26, 2013 at 11:09 am
Sean Lange (3/26/2013)
nitin_456 (3/25/2013)
So the idea here is to Generate 2 -3 million consecutive unique numbers by using 5 Alpha Numeric digit. Please if you can help around this will be great.I think that if you are dead set on proceeding with this you should consider the idea that Scott suggested of creating a table of all the keys and pull rows from that as you need them.
Let's consider how horrible it is to use alphanumeric values like this. Not only is it incredibly difficult to generate the values you run the risk of seriously offending people.
Think of all of the very offensive values you can make out of 6 characters starting with E. Or even the values that can happen all over the place. EVL666, E69SEX
Beginning to see how awful this can be?
What is so wrong with using an identity or finding a natural key?
And just in case you do want to proceed with such a table, I'll flesh it out more fully. Naturally you can adjust it as needed.
The pre-gen'd alphacode lookup table contain all allowed alphacodes (or, as the OP stated, at least a few million gen'd in advance). You could even delete "nasty" codes from the list prior to use, but you must be sure not to leave any gaps in the sequence number in the alphacodes table.
Easiest to me then is to have an identity column in the main table which is automatically translated to the corresponding alphacode using the alphacode lookup table.
For example, something like this:
USE tempdb
GO
IF EXISTS(SELECT 1 FROM sys.tables WHERE name = N'alphacode_lookup')
DROP TABLE dbo.alphacode_lookup
IF EXISTS(SELECT 1 FROM sys.tables WHERE name = N'main_table')
DROP TABLE dbo.main_table
IF EXISTS(SELECT 1 FROM sys.objects WHERE name = N'Get_Alphacode')
DROP FUNCTION dbo.Get_Alphacode
GO
CREATE TABLE dbo.alphacode_lookup (
sequence_number int NOT NULL,
alphacode char(6) NOT NULL,
CONSTRAINT alphacode_lookup__PK PRIMARY KEY ( sequence_number )
)
GO
CREATE FUNCTION dbo.Get_Alphacode (
)
RETURNS char(6)
AS
BEGIN
RETURN (
SELECT alphacode
FROM dbo.alphacode_lookup
WHERE
sequence_number = IDENT_CURRENT('main_table')
)
END --FUNCTION
GO
CREATE TABLE dbo.main_table (
ident int IDENTITY(1, 1) NOT NULL,
alphacode char(6) NULL DEFAULT dbo.Get_Alphacode(),
--if desired, alphacode could even be the clustered key for the main table
--(it changes once, but always from NULL (first value) to the last value, so not too awful)
CONSTRAINT main_table__CL UNIQUE CLUSTERED ( alphacode ),
dummy char(1)
)
GO
TRUNCATE TABLE dbo.alphacode_lookup
-- insert just 99 rows to give the flavor of what's happening
;WITH
cteDigits AS (
SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
SELECT [10s].digit * 10 + [1s].digit AS tally
FROM cteDigits [1s]
CROSS JOIN cteDigits [10s]
)
INSERT INTO dbo.alphacode_lookup
SELECT t.tally AS sequence_number, 'EA00' + RIGHT('0' + CAST(t.tally AS varchar(2)), 2) AS alphacode
FROM cteTally t
WHERE
tally BETWEEN 1 AND 99
ORDER BY t.tally
SELECT * FROM dbo.alphacode_lookup
TRUNCATE TABLE dbo.main_table
GO
INSERT INTO dbo.main_table (dummy)
SELECT 'a' AS dummy UNION ALL
SELECT 'b' UNION ALL
SELECT 'c' UNION ALL
SELECT 'd' UNION ALL
SELECT 'e' UNION ALL
SELECT 'f' UNION ALL
SELECT 'g' UNION ALL
SELECT 'h' UNION ALL
SELECT 'i' UNION ALL
SELECT 'j'
ORDER BY dummy
GO 2
SELECT * FROM dbo.main_table
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply