April 23, 2014 at 11:13 pm
Hi All,
I am working on an ASP.net web application which inserts new record into an underlying table. It is actaully a ConfirmationNumber and should be unieque. I have used
abs(checksum(newid()))
For this purpose. Is there a better way to accomplice this?
April 23, 2014 at 11:41 pm
is it a system generated number ? or enter by users
April 23, 2014 at 11:49 pm
twin.devil (4/23/2014)
is it a system generated number ? or enter by users
It is a system generated number.
April 23, 2014 at 11:50 pm
Confusing Queries (4/23/2014)
Hi All,I am working on an ASP.net web application which inserts new record into an underlying table. It is actaully a ConfirmationNumber and should be unieque. I have used
abs(checksum(newid()))
For this purpose. Is there a better way to accomplice this?
Any restrictions or limits, i.e. as numeric only, alpha numeric, max length and such?
I have often used variations of the newid() function, have some reservations on the checksum as there could be collisions.
😎
April 24, 2014 at 12:03 am
Eirikur Eiriksson (4/23/2014)
Any restrictions or limits, i.e. as numeric only, alpha numeric, max length and such?
It should be numeric or alpha numeric with maximum of 16 characters. Thanks for the heads up that checksum() sometimes give duplicate.
What would be the better way to auto generate confirmation number then?
April 24, 2014 at 12:17 am
Confusing Queries (4/24/2014)
Eirikur Eiriksson (4/23/2014)
Any restrictions or limits, i.e. as numeric only, alpha numeric, max length and such?
It should be numeric or alpha numeric with maximum of 16 characters. Thanks for the heads up that checksum() sometimes give duplicate.
What would be the better way to auto generate confirmation number then?
does this number shows on any reports or is it a internal number?
if it is internal you can use HashByte could be an option.
April 24, 2014 at 12:24 am
twin.devil (4/24/2014)
Confusing Queries (4/24/2014)
Eirikur Eiriksson (4/23/2014)
Any restrictions or limits, i.e. as numeric only, alpha numeric, max length and such?
It should be numeric or alpha numeric with maximum of 16 characters. Thanks for the heads up that checksum() sometimes give duplicate.
What would be the better way to auto generate confirmation number then?
does this number shows on any reports or is it a internal number?
Since the project has not reached at a point from where i can say for sure that we are going to use it in the report on not. May be we might not use it in any of the report. But we would be querying a lot on this field since this field would be the life line of the whole application.
April 24, 2014 at 12:32 am
Confusing Queries (4/24/2014)
twin.devil (4/24/2014)
Confusing Queries (4/24/2014)
Eirikur Eiriksson (4/23/2014)
Any restrictions or limits, i.e. as numeric only, alpha numeric, max length and such?
It should be numeric or alpha numeric with maximum of 16 characters. Thanks for the heads up that checksum() sometimes give duplicate.
What would be the better way to auto generate confirmation number then?
does this number shows on any reports or is it a internal number?
Since the project has not reached at a point from where i can say for sure that we are going to use it in the report on not. May be we might not use it in any of the report. But we would be querying a lot on this field since this field would be the life line of the whole application.
hmmm if you r not 100 % sure on this, you can do two things here:
1) create a custom unique number (like combination of DateTime plus any other Alpha/numeric number)
2) you can also use SEQUENCE .
3) you can also set that particular column to Identity as well.
April 24, 2014 at 12:49 am
twin.devil (4/24/2014)
1) create a custom unique number (like combination of DateTime plus any other Alpha/numeric number)
Something along these lines? (ugly but works) 😎
(121 retains the milliseconds while 126 and 127 truncate them if the millisecond value is 0.)
SELECT REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(40),DATEADD(HOUR,7978521,CONVERT(DATETIME2(2),SYSDATETIME())),121),'-',''),' ',''),':',''),'.','')
April 24, 2014 at 9:00 am
Stop working to get something you could easily get by using Identity or SEQUENCE as stated by twin.devil.
You should use a PK (or unique) constraint as well to ensure uniqueness. Int will give you at least over 2 billion values and bigint will give you more values than you'll ever need.
April 24, 2014 at 9:04 am
Luis Cazares (4/24/2014)
Stop working to get something you could easily get by using Identity or SEQUENCE as stated by twin.devil.You should use a PK (or unique) constraint as well to ensure uniqueness. Int will give you at least over 2 billion values and bigint will give you more values than you'll ever need.
Often the values should not be predictable, such as voucher numbers etc.
😎
April 24, 2014 at 9:19 am
Why not just increase the length to 36 characters and use newid()?
Or you could use an identity column and use ASCIIEncoding to convert the value to a base64 string when presenting it in a query string or whatever externally. That would make it a bit more difficult to just guess the value.
_______________________________________________________________
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/
April 24, 2014 at 9:22 am
Sean Lange (4/24/2014)
Why not just increase the length to 36 characters and use newid()?Or you could use an identity column and use ASCIIEncoding to convert the value to a base64 string when presenting it in a query string or whatever externally. That would make it a bit more difficult to just guess the value.
I agree, any calculation will be a PITA to validate with high concurrency systems.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply