Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Inspecting a rather critical database that is used by some software on my system, I found that one of the tables had a primary key on the column Id, where Id is calculated using checksum(newid()). This makes me feel uneasy; the newids are guaranteed to be unique, but by applying the checksum (to a 32-bit integer, presumably for performance reasons?), you have some chance that you get a collision (with 1M rows in the table, I'd put that chance at around 1:4000, too much for my liking).

So:

  • Am I missing some crucial bit of information that says the above is actually okay?
  • If not - what happens if the application tries to insert a new row into the table and the checksum(newid()) gives a primary key that already exists? Will my table blow up? Will the insert fail and leave it up to the application what to do with it?
share|improve this question
3  
I don't understand the purpose of this design at all. Is it supposed to facilitate random numbers instead of sequential? Does the application have retry logic to handle duplicates? –  Aaron Bertrand May 21 '13 at 20:46
    
@Aaron I don't know WHY they did that and whether they have retry logic... I'm a user of that particular piece of software, not a developer, which is why I'm worried about my data. Just making sure I'm not missing something obvious before I file a bug report for this. –  us2012 May 21 '13 at 20:54
add comment

2 Answers

I have to presume that the application is written in such a way that it tries the insert, and if it fails, it just tries again. Otherwise the insert will fail (PK violation) and your users will have complained and filed bugs.

If the purpose of this is to just assign unique numbers in a random order, then it may be the case that the application is working just fine. I think you should only file a bug report if you can demonstrate that the application does something wrong when a collision occurs - not easy given the likely failure rate. An easier way, if you have source code access, would be to review the portion of the code where they perform the insert (or find out if it calls a stored procedure to do so).

That in mind, there is a much easier and fault tolerant way to do this. Build a table up front, with a slew of numbers ordered randomly, then just pluck a number off the top whenever you need one.

CREATE TABLE dbo.Destination(ID INT /*, other columns */);

CREATE TABLE dbo.Source
(
  RowNum INT PRIMARY KEY,
  ID INT, -- UNIQUE
  Used BIT NOT NULL DEFAULT 0
);

INSERT dbo.Source(RowNum, ID) 
SELECT ROW_NUMBER() OVER (ORDER BY NEWID()), ID
  FROM 
  (
    SELECT ID = CHECKSUM(NEWID()) 
      FROM sys.all_objects AS s1
      CROSS JOIN sys.all_objects AS s2
      -- should produce about 4 million rows,
      -- on my crappy VM this took two minutes,
      -- add another cross join if you need more
  )
  AS x GROUP BY ID; 

Then when you need to add a row, you can just say:

UPDATE TOP (1) dbo.Source SET Used = 1
  OUTPUT inserted.ID /*, @params */ 
  INTO dbo.Destination
WHERE Used = 0;

(You could also DELETE TOP (1) however I find keeping them useful for auditing etc.)

share|improve this answer
1  
+1. Unfortunately, I am a user of this particular bit of software, so I can't change the design. I'm trying to figure out whether I should file a bug report for this :) . (No idea what the purpose is, random numbers make no sense, a PK on an increasing ID would be so much better for that particular data.) –  us2012 May 21 '13 at 20:55
add comment

My guess is they were trying to create a 4-byte GUID situation.

Not sure what the benefit would be though: OK, it would avoid insertion 'hot spots' (at the expense of fragmentation built in the design). But being only 4 bytes the chances of collisions are vastly higher than a GUID. So it would fit a relatively small table with a very high rate of new records.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.