January 30, 2012 at 5:37 am
Hi,
I am new to SQL Server.So if anyone has got a solution i would be thankful.
My requirement : want to generate a 16 digit unique numbers while inserting records.
Am able to set the start value but not able to specify the max value.
Any IDEAs.??????
January 30, 2012 at 6:02 am
Setting aside why you'd need a 16 digit number (that's an awfully large number), the literal way of doing what you're asking is a combination of IDENTITY and a check constraint, e.g. :
CREATE TABLE #test (ID BIGINT IDENTITY(1000000000000000,1) NOT NULL UNIQUE CHECK (ID BETWEEN 1000000000000000 AND 9999999999999999))
This seems like a waste of space though as you can't be dealing with that many rows. Might be better to have a standard IDENTITY column definied as INT (starting at 1) and then a computed column that adds 1000000000000000 to it to provide this field.
January 30, 2012 at 6:05 am
hey thanks for your reply..ill try it out.
January 30, 2012 at 6:10 am
First thing is what kind of data does this 16 digit number represent , is it an order reference number of a transaction id , or a really long phone number . Does it have to be a number or can it be a text value.
Does it have to be sequential or are you looking for a unique but random number. Can the numbers in the sequence be reused after a point in time ?
The way you answer this will help make sure your using the right kind of column data type and date format.
You can always use the identity column with Bigint like in thre previous post however the important question you need to ask is " does this accurately represent the data i want stored in this column"
January 30, 2012 at 6:20 am
hi jayant,
(First thing is what kind of data does this 16 digit number represent , is it an order reference number of a transaction id , or a really long phone number . Does it have to be a number or can it be a text value.
Does it have to be sequential or are you looking for a unique but random number. Can the numbers in the sequence be reused after a point in time ?)
To answer your question , The 16 digit number is for a purpose like the atm card number.To be more precise it would be a membership id.
It will be fully a number and cannot contain text values.
It need not be sequential.It can be random but unique.No the numbers are not to be reused later..
Hope i have given you a clear picture of the requirement.
January 30, 2012 at 6:31 am
Since your using the 16 digit number for credit card or atm card purposes you should know that there is a mod check for generating a 16 digit card number .
This means that any random 16 digit card number will not suffice as a valid atm card.
If your familiar with the mod check requirements then you can write a function to generate the number for you.
I am not sure if a credit card can start with a zero in which case you might want to change the data type from bigint to char(16) , howevere I think there are also banks which issues 14 digit card numbers as well.
January 30, 2012 at 7:05 am
There are predefined algorithms for generating & validating credit / atm cards. IIRC, Each financial institute has only one predefined & unique algorithm for their use. Sequential number even if 16 digit numbers doesn’t guarantee a valid credit card number.
It’s a very sensitive topic. Please discuss with your customer about it.
p.s. membership id for this purpose is useless OR you need to provide more information on your business case & data.
January 30, 2012 at 9:22 am
Dev (1/30/2012)
There are predefined algorithms for generating & validating credit / atm cards. IIRC, Each financial institute has only one predefined & unique algorithm for their use.
Actually that is not true. Each credit card company has their own starting digit(s) but the validation of the card is called Luhn. It is actually quite simple. http://en.wikipedia.org/wiki/Luhn_algorithm
Since you are not really trying to generate numbers that are going to be used other than member numbers you can probably just generate a number that is 16 numbers in length.
You could do something like this.
select cast(cast(RAND()*10 as int) as CHAR(1))
+ cast(cast(RAND()*10 as int) as CHAR(1))
+ cast(cast(RAND()*10 as int) as CHAR(1))
+ cast(cast(RAND()*10 as int) as CHAR(1))
+ cast(cast(RAND()*10 as int) as CHAR(1))
+ cast(cast(RAND()*10 as int) as CHAR(1))
+ cast(cast(RAND()*10 as int) as CHAR(1))
+ cast(cast(RAND()*10 as int) as CHAR(1))
+ cast(cast(RAND()*10 as int) as CHAR(1))
+ cast(cast(RAND()*10 as int) as CHAR(1))
+ cast(cast(RAND()*10 as int) as CHAR(1))
+ cast(cast(RAND()*10 as int) as CHAR(1))
+ cast(cast(RAND()*10 as int) as CHAR(1))
+ cast(cast(RAND()*10 as int) as CHAR(1))
+ cast(cast(RAND()*10 as int) as CHAR(1))
+ cast(cast(RAND()*10 as int) as CHAR(1))
Probably not the most efficient but should be pretty quick. The only thing is you would want to validate that this number does not already exist in your table.
_______________________________________________________________
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/
January 31, 2012 at 7:26 am
Sean, do you really think the credit card validation criteria would be this simple & available to public to hack / mimic? Luhn algorithm is just a part of validation and IMO there are many more secretes which each company maintains.
Jeffrey, please understand the seriousness of the issue and don’t even think about sharing any data & actual (or pseudo) code here. You might be in big trouble as it’s a breach of NDA (Non-Disclosure Agreement).
January 31, 2012 at 7:34 am
Well surely the companies have some additional proprietary logic for them to generate but they do have to pass Luhn. This is why those numbers are generated by the company.
This whole credit card discussion is way off topic of the OP anyway. He only wants to generate 16 digit numbers.
To be more precise it would be a membership id.
The OP just wants to generate a member number for his health club or whatever, it has nothing to do with actual credit/debit cards or their usage.
_______________________________________________________________
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/
January 31, 2012 at 7:43 am
I'd just like to point out that some of the examples of numbers are not really stored as numbers.
Telephone and credit card numbers are strings of set length, so Char or NChar
If you need an auto incremented number with prefixed zeros, I'd use an Identity and Computed column combination.
January 31, 2012 at 7:48 am
Now it’s either a big mistake on my end or over-cautiousness because I work on Financial Projects.
When I read the membership id (16 digits) I interpreted it as ‘Membership Provider’ (details below) which uses 16 bytes unique identifier GUID for ID columns. And thus I said membership ids won't help OP in such cases.
Membership Providers
http://msdn.microsoft.com/en-us/library/aa478949.aspx
MembershipProvider Class
http://msdn.microsoft.com/en-us/library/system.web.security.membershipprovider.aspx
uniqueidentifier (Transact-SQL)
January 31, 2012 at 7:52 am
I didn't think it was anything other just a 16 digit number but of course I could be wrong. I think he is just wanting this number to put on a membership card. I can certainly understand the jump when it is close to stuff you deal with everyday.
_______________________________________________________________
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/
January 31, 2012 at 7:54 am
Dev (1/31/2012)
Now it’s either a big mistake on my end or over-cautiousness because I work on Financial Projects.When I read the membership id (16 digits) I interpreted it as ‘Membership Provider’ (details below) which uses 16 bytes unique identifier GUID for ID columns. And thus I said membership ids won't help OP in such cases.
Membership Providers
http://msdn.microsoft.com/en-us/library/aa478949.aspx
MembershipProvider Class
http://msdn.microsoft.com/en-us/library/system.web.security.membershipprovider.aspx
uniqueidentifier (Transact-SQL)
Having done many financial projects in the past, I suspect over cautious. Especially since its not you posting potential NDA information
Luhn card numbers and their assigned bank information is widely available to purchase. So whilst this information itself is possibly subject of a non disclosure contract, I dont believe a relating lookup algorithm is going to be a problem.
January 31, 2012 at 8:05 am
You guys are right (but not 100%). If I tell you the stories (which are not stories by the way) about job termination on the spot & criminal cases against DBAs / DEVs on financial projects, you will also join the league of over-cautious DBAs & DEVs.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy