NewID = lastrecord ID + 1

[jsnunez]

Hi all

I have a table that when a new record is added, the key must be the previous record key + 1

I am having probelms when 2 or more users add new records at the same time. Is ther a way to create a trigger that will populate the key fiedl for the new record with the key of the last record + 1

thanks
jsn

 

[vongrunt]

Is identity column acceptable in this case?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[jbenson001]

There is a way to do that, but why not use an identity column. This way you don't have to worry about this situatuion and let SQL Server handle it.

Ko,

 

[SQLDenis]

use an identity field instead

create table table1 (Id int identity, dateField datetime)
insert into table1
select getdate()

select * from table1



“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[jsnunez]

As far as I understood, identity column increases automatically, but I had some issues transferring a table with an identity column is re-sequenced. Also it is a general advice to do not use identity fields as primary keys.

jsn

 

[jbenson001]

That is general advice by who? I almost always use Identity columns as a primary key. And what issues did you have by "transferring" a table?

 

[DotNetGnat]

Also it is a general advice to do not use identity fields as primary keys.

is that right guys??

-DNG

 

[jsnunez]

Hi

I had a big problem after transferring this work ticket table from one database to another, it was resequenced.

I am not using an autoincrease field as key field, I want to increaes it manually.

Is there a way to increase it in a trigger?

thanks
jsn

 

[SQLDenis]

you can do it in a trigger or put transaction around the code that is running now

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[jsnunez]

HOW?

Could you please send me some examples

thanks
jsn

 

[vongrunt]

> As far as I understood, identity column increases automatically, but I had some issues transferring a table with an identity column is re-sequenced.

Only if transfer rutine fiddles with SET IDENTITY_INSERT or DBCC CHECKIDENT.

> Also it is a general advice to do not use identity fields as primary keys.

Yup. There are some arguments against identity values, from theoretical to practical.

> Is there a way to increase it in a trigger?

AFAIK nope. Primary key is NOT NULL by definition, and this column-level constraint will kick in before trigger gets executed. Maybe I'm wrong... dunno.

There is one dirty hack - using UDF for column default - but you'll very likely have to isolate transactions above isolation level 1 to prevent all posible concurrency scenarios with negative outcome.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[jsnunez]

I mean examples in how to inmcrease the ID value using a trigger

thanks
jsn

 

[druer]

In a previous position we had a table of the next available ID's to use for given tables. If you try to use the MAX value then you have a bigger performance hit than if you just keep track of the values in another table.

We then created a user defined function that would do a query against the table to find the next value to assign, and when reading issue a request for an update lock, assuring that no other process could grab the same number.

SELECT value FROM table_of_ids WITH (UPDLOCK) where table_id = @TableId that was input

(@TableId is just identifier of which table we needed the value of)

Then the function would update the value by incrementing it by 1 and then return the value it had selected.

In the code that did the insertions we simply called the fn_GetIdValue(table_id_we_want), fieldvalue, fieldvalue etc.

I imagine you could also call the function as the default, or put the assignment in your insert trigger as well. It accomplished the task of doing just what Identity field would do, and resolved some of the issues surrounding IDENTITY fields.