Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

generating and storing a counter Expand / Collapse
Author
Message
Posted Thursday, March 5, 2009 1:30 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 4, 2009 1:49 PM
Points: 100, Visits: 100
I need to sequentially number events created by users with an incremental counter. In the events table, I have a unique key on the combination of OrgId, TypeId, and OrgEventCounter. Are there any “best practices” on how best to handle the unlikely possibility that 2 users will try to save at the same time with the same OrgEventCounter? Here’s a stripped down version

DECLARE @OrgEventCounter int

SET @OrgEventCounter =
(SELECT MAX(OrgEventCounter)
FROM dbo.Event
WHERE OrganizationID = 2325 AND DocumentTypeOrgID = 1) + 1

INSERT dbo.Event
(OrganizationID, TypeID, OrgEventCounter)
VALUES (1234, 1, @OrgEventCounter)

I could trap for :

Msg 2627, Level 14, State 1, Line 9
Violation of UNIQUE KEY constraint 'IX_DocDataEvent'. Cannot insert duplicate key in object 'dbo.DocDataEvent'.

and loop until it works, but it seems there should be a more elegant way. Any insight is appreciated.
Post #669595
Posted Thursday, March 5, 2009 1:44 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 758, Visits: 1,031
The way I would do this is to keep the counter in a separate table, and use an identity field on that table as the counter.

However, if you combine your two queries, and use a suffiently high isolation level, it should also do the trick:

--
-- if there is no trigger on your Event table, then use the following
-- logic to get the next counter
--
update Quantifi.dbo.DocDataEvent
set @OrgEventCounter = OrgEventCounter = OrgEventCounter + 1
where OrganizationID = 2325 and
DocumentTypeOrgID = 1

--
-- assuming you have a trigger on the Event table that updates the
-- counter in the Quantifi.dbo.DocDataEvent table
--
set transaction isolation level serializable
begin transaction;
insert into dbo.Event(
OrganizationID,
TypeID,
OrgEventCounter
)
select 1234,
1,
max(OrgEventCounter) + 1
from Quantifi.dbo.DocDataEvent
where OrganizationID = 2325 and
DocumentTypeOrgID = 1;
commit;

Post #669616
Posted Thursday, March 5, 2009 1:54 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
Hi

There are several possible practices.

If you only need an unique id you may use an IDENTITY INT column. If it does not matter if the id is an INT you can also use a TIMESTAMP or an UNIQUEIDENTIFYER.

I for my own do not like IDENTITY columns. Maybe you may add a ID-Table which maintains the IDs so you can cache a set of IDs in your client application and/or abstract the INSERT action into a procedure.

Greets
Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #669636
Posted Thursday, March 5, 2009 1:58 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, September 19, 2013 6:00 AM
Points: 707, Visits: 2,706
florian.reischl (3/5/2009)
I for my own do not like IDENTITY columns.


Wow, that's some news there! How can you not like identity columns? I tell database developpers to put 1 on each and every table in the database. Only in really really rare scenarios we don't put an identity column.

What's your sight on this? It puts an easy uniqueness to the rows for transmitting to another server, it's a perfect field for the update statements, and so is it for the delete. What's so wrong with it?


Cheers,

J-F
Post #669640
Posted Thursday, March 5, 2009 2:02 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 4, 2009 1:49 PM
Points: 100, Visits: 100
Sorry Bruce, for clarification, the tables in my 2 statements are actually the same table. I updated my original post.
Post #669649
Posted Thursday, March 5, 2009 2:07 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 4, 2009 1:49 PM
Points: 100, Visits: 100
I do not have a trigger that updates the counter. I can add one if I decide that it's definitely the best option. I generally only use triggers when I have no other good solution.
Post #669656
Posted Thursday, March 5, 2009 2:07 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:34 AM
Points: 6,259, Visits: 2,031
Jean-François Bergeron (3/5/2009)
florian.reischl (3/5/2009)
I for my own do not like IDENTITY columns.


Wow, that's some news there! How can you not like identity columns? I tell database developpers to put 1 on each and every table in the database. Only in really really rare scenarios we don't put an identity column.

What's your sight on this? It puts an easy uniqueness to the rows for transmitting to another server, it's a perfect field for the update statements, and so is it for the delete. What's so wrong with it?


Wait until you start using replication ....



* Noel
Post #669658
Posted Thursday, March 5, 2009 2:15 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
Wow, that's some news there! How can you not like identity columns? I tell database developpers to put 1 on each and every table in the database. Only in really really rare scenarios we don't put an identity column.

What's your sight on this? It puts an easy uniqueness to the rows for transmitting to another server, it's a perfect field for the update statements, and so is it for the delete. What's so wrong with it?


Hi J-F

I don't like it because of some reasons:

Replication
Our DBAs say they don't like it becase of replicatiion problems. Don't ask me, I'm no DBA and do not know very much about replication. I don't really believe this...

Manipulation of many data
Our applications generate many data on client side before they become commited. We use an ID-Table which contains type (= table) specific ID-Ranges. So we can set new IDs to all new rows and then bulk insert them in one bulk.

The profit is that the client application can build all the data within the client application with their final IDs and without the @@IDENTITY select for further links to other objects (rows).

Resume
I think its just a subjective (maybe stupid) sight of me ;) .

Greets
Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #669673
Posted Thursday, March 5, 2009 2:22 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:34 AM
Points: 6,259, Visits: 2,031
florian.reischl (3/5/2009)
Wow, that's some news there! How can you not like identity columns? I tell database developpers to put 1 on each and every table in the database. Only in really really rare scenarios we don't put an identity column.

What's your sight on this? It puts an easy uniqueness to the rows for transmitting to another server, it's a perfect field for the update statements, and so is it for the delete. What's so wrong with it?


Hi J-F

I don't like it because of some reasons:

Replication
Our DBAs say they don't like it becase of replicatiion problems. Don't ask me, I'm no DBA and do not know very much about replication. I don't really believe this...

Manipulation of many data
Our applications generate many data on client side before they become commited. We use an ID-Table which contains type (= table) specific ID-Ranges. So we can set new IDs to all new rows and then bulk insert them in one bulk.

The profit is that the client application can build all the data within the client application with their final IDs and without the @@IDENTITY select for further links to other objects (rows).

Resume
I think its just a subjective (maybe stupid) sight of me ;) .

Greets
Flo


Your DBAs are RIGHT



* Noel
Post #669683
Posted Thursday, March 5, 2009 2:27 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, September 19, 2013 6:00 AM
Points: 707, Visits: 2,706
noeld (3/5/2009)
florian.reischl (3/5/2009)
Wow, that's some news there! How can you not like identity columns? I tell database developpers to put 1 on each and every table in the database. Only in really really rare scenarios we don't put an identity column.

What's your sight on this? It puts an easy uniqueness to the rows for transmitting to another server, it's a perfect field for the update statements, and so is it for the delete. What's so wrong with it?


Hi J-F

I don't like it because of some reasons:

Replication
Our DBAs say they don't like it becase of replicatiion problems. Don't ask me, I'm no DBA and do not know very much about replication. I don't really believe this...

Manipulation of many data
Our applications generate many data on client side before they become commited. We use an ID-Table which contains type (= table) specific ID-Ranges. So we can set new IDs to all new rows and then bulk insert them in one bulk.

The profit is that the client application can build all the data within the client application with their final IDs and without the @@IDENTITY select for further links to other objects (rows).

Resume
I think its just a subjective (maybe stupid) sight of me ;) .

Greets
Flo


Your DBAs are RIGHT


Wanna state why? From what I read, you can give servers different identity seeds (like separate to get 4 seeds).

First is even positive numbers
Second is even negative numbers
Third is not even positive numbers
Fourth is not even negative numbers.

What are the issues of Identity columns on a replication action?


Cheers,

J-F
Post #669690
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse