- Feature Articles
- CodeSOD
- Error'd
-
Forums
-
Other Articles
- Random Article
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
Instead of trying to force-fit the GUID approach, or adopting some date/time pattern with a questionable resolution, I'll be replacing it with a 1-row 1-column table with the next INT or BIGINT to use within the body of the PK.
It turns out that there are actually multiple new classes of synthetic IDs that need to be added, each with its own prefix letter. My posting only mentioned the 'N' version. They will all share the same table & logic for allocating the next number to be used as part of the PK.
Simple and correct -- or is there something wrong with this approach too?
Admin
Without details, it appears to be a better approach, provided it is properly implemented.
Admin
No, TRWTF is missing the final ">" on your closing "
" tag! :pAdmin
Admin
x.y_id = y.id y.z_q_id = z_q.id
Admin
Admin
Schroedinger's Cat is also in two states at the same time.
Admin
Oh, I see, somebody never had to learn German or Russian. Lucky you...
Admin
If you're using SQL Server 2012, you can also use a SEQUENCE. It handles a lot of race condition possibilities for you.
http://msdn.microsoft.com/en-us/library/ff878091.aspx
Admin
Indeed, if someone shouldn't be allowed access to something, then don't allow access to it. Making it harder to find and hoping he won't guess it is NOT security, it's wishful thinking.
Admin
You have no idea what you're talking about.
Hint: if you think 128 or 256 or 512 bits of entropy is merely "obscurity", you don't deserve a job in security.
Admin
http://xkcd.com/538/
Admin
Oh, god. Not this again.
You'll have to lock the table, read it, update it, commit it.
If you don't lock the table, two processes can read it at the same time and get the same "GUID".
If you do lock the table while reading, the approach "works", but kills your performance as all processes have to queue up to get at the next "GUID", and if one of the processes fails part way through without committing, your whole system stops dead.
If you can, use a sequence, or something else that your DB undoubtedly has to handle this exact situation.
Admin
Admin
Admin
Admin
The truly WTF is that a project manager can influence the solution by on his/her own interpretation of the requirement. And if Jay keeps letting this happens, his development life will be as miserable as it can get.
Admin
Admin
Admin
And Firebird and Interbase both have generators but you would have to convert it to a string and put the N at front but that would be handled by the trigger.
There are better ways than truncating a GUID
Admin
Allowing the “N” to vary over the letters of the alphabet (though still staying upper case) would be enough to allow a complete encoding with 1 bit to spare.
Admin
Admin
Admin
When working with randomly allocated unique IDs you have to ask yourself two questions.
1: How many records will the database have (n) 2: What probablity of collision is acceptable (p)
Let x be the number of values of our unique ID
Assume that n >> 1 , also assume that since we are trying to find values that will make one collision very unlikely two or more collisions will be extremely unlikely.
The number of pairs of records is approximately (n^2)/2 The probablity of any particular pair colliding is 1/x The probablity of a collision in the set as a whole is approximately (n^2)/2x
Seting p = (n^2)/2x and solving for x gives us
x=(n^2)/2p
Since we haven't been told how big the database in question is or the acceptable probablity of a collision we can't say whether the unique ID he has generated is sufficiently large or not.
Admin
Endis vurn, hoom der inserten der data en der tablen, pootin oom ver databasen! Bork! Bork! Bork!
Admin
SQL Server has had SEQUENCE for a lot longer than that. But it still doesn't have an atomic way to retrieve the generated SEQUENCE value on insert, (unless they added that in 2012; not sure,) which makes for race conditions of its own.
I know Firebird lets you do that with the INSERT INTO ... RETURNING syntax; what other databases have that?
Admin
Oracle appears to have it (and an incredibly easy way to use sequences to resolve this issue); "Insert into ... values (concat('N' + seq.nextval), ...)" I really don't understand why any engineer would let the PM decide on the implementation details of the DB (or really anything). Isn't that my job?
Admin
You have not done your research, sir. There's a reason I linked the MSDN page.
SQL Server has had IDENTITY properties allowed on table fields for a very long time, but SEQUENCE is never-before-seen-brand-new in SQL Server 2012. The query "SELECT * FROM sys.sequences" will fail on any version of SQL Server prior to 2012. They are similar to Oracle SEQUENCEs (unsurprisingly) and they represent a unique way of handling a series of numbers.
A SEQUENCE is a table-independent value. It is not tied to a specific field in a specific table, but is managed as an entity on it's own.
You can get a value using the NEXT VALUE FOR construct (examples here: http://msdn.microsoft.com/en-us/library/ff878370.aspx ). Unless you specifically allow your SEQUENCE to restart (or CYCLE) the sequence when the max value has been reached, the server will only return any given value for NEXT VALUE FOR once. It's like a take-a-number paper ticket at the DMV. The values are consumed.
SQL Server can do this with the OUTPUT clauses, which are valid for INSERT, UPDATE, and DELETE statements (as well as the SQL Server-specific MERGE statement) using the inserted.* and deleted.* special table references. See here: http://msdn.microsoft.com/en-us/library/ms177564.aspx That has been in place since SQL Server 2005.
Admin
What was wrong with "N2013041222124600001"?
Admin
...and then can you are told that the PK has to be a clustered index...
Admin
TRWTF is that a project manager gets to decide these kind of things...
Admin
Yeah, passwords are dumb, since anyone can get to a website and type one in.
Ignoring the fact that a UUID has exponentially more entropy than anyone's banking password on thus website, using a UUID instead of a non-linear sequence isn't just for "making it hard to guess". It's also more scalable, and hides business intelligence. It's the same reason I started using invoices prefixed with the year when I started my business: I didn't want anyone to know they were only the first (or 5th, or 20th) person I've ever billed.
Admin
SELECT curid FROM idtable previd = curid nextid = curid + 1 UPDATE idtable SET curid = nextid WHERE curid = previd if record update count = 0 repeat above
The UPDATE only succeeds if curid was not updated by another process. No locks required.
Admin
Using insert with an output clause or a select @@identity immediately after an insert will work on older MS SQL and with no race conditions as long as you do not try to share a connection between threads, which you should not do anyway ;)
Admin
Because I have nothing else to contribute, I hereby contribute GuidToBase64 (for MS SQL Server 2008 and higher. Maybe 2005.)
CREATE FUNCTION dbo.Guidtobase64 ( @guid UNIQUEIDENTIFIER ) RETURNS VARCHAR(22) AS BEGIN DECLARE @bin VARBINARY(20) SET @bin = CONVERT(VARBINARY, @guid) RETURN Replace( Replace( Cast(N'' AS XML) .value('xs:base64Binary(xs:hexBinary(sql:variable("@bin")))' , 'varchar(20)') , '+', '-') , '/', '_') END
Reversing this is left as an exercise.
CAPTCHA: I am the SAGACITER
Admin
That works right up until someone discovers you don't really have that much entropy. All it takes is a little bug in the PRNG to be found an you are TOAST.
Correct nobody is going to 'guess' valid ideas in a sparsely populated 128-bit or larger space. As a security guy though I would call you FOOLISH to depend on that. Because there have been many vulns due to the discovery that something people though was random was actually directly predictable, or there were at least ways to dramatically reduce the search space.
Admin
Admin
Admin
The 'N' part of the key has meaning, it's a grouping code, so the OP tells us. That means the database design doesn't even make it to first normal form, fields should not be multi-value. Just use a sequence to generate a unique key, put the CHAR(1) values in a separate column. When an invoice/report is printed out, then concatenate the two values together as a string. Mixing presentation with storage layers, spells poor performance. Also, pro-tip, hire someone who has a clue about databases.
Admin
True, but a SEQUENCE is a bit cleaner, IMO, and it allows you to easily use the same series of numbers for multiple tables. That might be a rare use-case, but it's there. You also don't have to put the logic in the application. You can do this:
create sequence mainseq as bigint start with 1 increment by 1;
create table mytable ( id bigint not null primary key constraint DF_mytblid default next value for mainseq, code varchar(20) not null, .... );
Now other applications can access your DB and can create new IDs and the logic is already clear.
Admin
UPDATE idtable SET curid = curid + 1 SELECT curid FROM idtable INSERT ... COMMIT
Of course, that doesn't make it correct, just lucky.
Admin
Admin