March 5, 2009 at 1:30 pm
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.
March 5, 2009 at 1:44 pm
[font="Verdana"]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;
[/font]
March 5, 2009 at 1:54 pm
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
March 5, 2009 at 1:58 pm
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
March 5, 2009 at 2:02 pm
Sorry Bruce, for clarification, the tables in my 2 statements are actually the same table. I updated my original post.
March 5, 2009 at 2:07 pm
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.
March 5, 2009 at 2:07 pm
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
March 5, 2009 at 2:15 pm
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... :hehe:
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
March 5, 2009 at 2:22 pm
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... :hehe:
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
:hehe: Your DBAs are RIGHT :hehe:
* Noel
March 5, 2009 at 2:27 pm
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... :hehe:
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
:hehe: Your DBAs are RIGHT :hehe:
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
March 5, 2009 at 2:30 pm
:hehe: Your DBAs are RIGHT :hehe:
Hi Noel
Thank you for this information! I still do not really know why but I don't have to. I'm an architect and only need to know that I have to avoid IDENTITIY columns 😉
Greets
Flo
March 5, 2009 at 2:31 pm
Luckily I haven't ran into replication much. Because as the only technology seems to be the defacto where I work is clustering or nothing heh. But for replication issues; I would think Identity column causes issues in Merge replications correct? Because seed values might be different in two different nodes?
But aside from that in stand alone databases, mirroring, etc. Is there anywhere you guys have found Identity column to be a bad choice? Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 5, 2009 at 2:37 pm
Okay I suspected that this discussion will start now...
I got my coke and my popcorn 😀 so let me learn about pro and contra of IDENTITIES! I'm really always interested in different sights!
Greets
Flo
March 5, 2009 at 2:40 pm
Mohit (3/5/2009)
Luckily I haven't ran into replication much. Because as the only technology seems to be the defacto where I work is clustering or nothing heh. But for replication issues; I would think Identity column causes issues in Merge replications correct? Because seed values might be different in two different nodes?But aside from that in stand alone databases, mirroring, etc. Is there anywhere you guys have found Identity column to be a bad choice? Thanks.
Sure a cluster is always a good solution, but it is cost factor. We additionally have an external desaster-recovery data-center at another location. I think here is no other way than a replication.
March 5, 2009 at 2:42 pm
Mohit (3/5/2009)
Luckily I haven't ran into replication much. Because as the only technology seems to be the defacto where I work is clustering or nothing heh. But for replication issues; I would think Identity column causes issues in Merge replications correct? Because seed values might be different in two different nodes?But aside from that in stand alone databases, mirroring, etc. Is there anywhere you guys have found Identity column to be a bad choice? Thanks.
Merge is just one of the problems, in transactional you will have to checkident the subscriber tables if you need failover.
Disconnected Editing is cumbersome with identity columns; therefore ID tables are there to rescue you 😀
.. and there are more but they are very specific and you could arguably say that identity could still be used.
Don't get me wrong on places like DW with Dimession and Fact Tables it makes sense.
On OLTP and disconnected systems, there are many situations in which there are better alternatives.
* Noel
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply