1

Hello Im afraid about concurrency on partner application cause in the last days it was having troubles with CRUDS Operations, especially with inserts. So I ran SQL Profiler and note that his insert statement dont use transaction and also he is using :

INSERT INTO TABLEA VALUES ( (SELECT MAX(NUMBERFIELD) +1 FROM TABLEA), ....);

How avoid the use of MAX()+1 for generate primary keys? I suggest to use autoincrement or transaction scopes but he dont want or maybe he dont know how to achieve that, is there another way for lead with this?

Using SQL and C++ 1.1

*Its not my code but I might consider show this post to him cause I think he have to consider that all opinions are welcome. :)

jalf
  • 243,077
  • 51
  • 345
  • 550
Jonathan Escobedo
  • 3,977
  • 12
  • 69
  • 90
  • SQL Server's equivalent of MySQL's autoincrement is called **IDENTITY**: http://msdn.microsoft.com/en-us/library/aa933196%28SQL.80%29.aspx – OMG Ponies Oct 28 '09 at 22:27

2 Answers2

4

If you code it right, you should be able to get the same concurrency out of this as with an IDENTITY solution. It is not intuitive; you would think that the locking would reduce concurrency. But I ran several tests with five distinct connections hammering the table, and proved to myself that the MAX+1 trick performed just about exactly the same as IDENTITY. You can see the discussion in the first section of the following blog post:

https://sqlblog.org/2009/10/12/bad-habits-to-kick-making-assumptions-about-identity

Anyway here is the syntax I recommend (I don't really trust the INSERT ... VALUES (SUBQUERY) model, sorry):

   DECLARE @NextID INT;

   BEGIN TRANSACTION;

   SELECT @NextID = COALESCE(MAX(NumberColumn), 0) + 1
       FROM dbo.TableA WITH (UPDLOCK);

   INSERT dbo.TableA SELECT @NextID;

   COMMIT TRANSACTION;

You'll have to imagine the error handling portions (omitted for brevity) but I think you'll get the drift.

Of course don't take any of this for gospel. You will need to run tests on your hardware using your data and access patterns to determine whether concurrency will be harmed by this method. This could very well be one of those "it depends" answers.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
3

Have you considered making the primary key column IDENTITY? It'll make SQL Server automatically generate values for the column:

CREATE TABLE Test (
    Id int identity primary key not null
    -- ...
)
Mehrdad Afshari
  • 414,610
  • 91
  • 852
  • 789
  • Agreed! A lot of people use the MAX+1 trick because they believe this column has some magic meaning and that there can't be gaps. There are some business requirements where this sequence-without-gaps is a necessity, but I think they are few and far between (e.g. companies who generate invoice numbers from this type of column). – Aaron Bertrand Oct 28 '09 at 22:32
  • Aaron: It's ironic that `MAX() + 1` does not provide that consequence number requirement either. You can still have gaps if you delete a row (except the last one). – Mehrdad Afshari Oct 28 '09 at 22:36
  • That is true, but when the requirement is a real business need, there is likely going to be a trigger preventing delete, and a method of "soft delete" - where the row is marked as "deleted" but isn't really removed, maybe just no longer exposed to the front end. – Aaron Bertrand Oct 28 '09 at 22:41
  • I will use identity too, but its not my decision to coverage this trouble, I just discuss my opinion with him but he refuse to consider this way. – Jonathan Escobedo Oct 29 '09 at 14:16
  • please be aware that identity does not guarantee successive numbers : http://stackoverflow.com/questions/14162648/sql-server-2012-column-identity-increment-jumping-from-6-to-1000-on-7th-entry – Chtioui Malek Oct 06 '13 at 21:05