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

Adding record to db using stored procedure and identity counter. Expand / Collapse
Author
Message
Posted Thursday, October 25, 2012 7:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, March 2, 2014 1:41 PM
Points: 18, Visits: 37
While I've been using MS SQL for years, never really learned it.
Using test environment SQL 2005, Windows 7 but hardly relevant.
Code environment is ColdFusion.

I am trying to ...

In the past when I have added a member record to db, I have used the identity (auto number) as their account number.
Changing schema to having the identity counter serve no purpose other than provide uniqueness to record. Not be their A/C nr.

Instead a new column member_id BIGINT will be the number; while keeping uniqueness with the identity counter.

Using a stored proc, I am inserting vitals into table, then I want to, within the SQL Server Stored Pro do the following:

Determine the highest number of column member_id. Add 1 to that, then update the newest record (the one I just added) to reflect that.

In ColdFusion it is easy. Unsure how to syntax it in stored proc.

select max(member_id) as highnum from member (easy)

newnum=highnum+1 (nope)

update member set member_id=nemnum where member_autonum=@@identity

So ... how do I write those two lines in the stored proc? I'd rather make it clean and do it in the SP.

Many thanks.

Robert



Post #1376998
Posted Thursday, October 25, 2012 9:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
You can do something like this:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

DECLARE @NewVal BIGINT = (SELECT MAX(member_id) FROM dbo.member WITH (TABLOCKX))+1;

INSERT INTO dbo.Member (member_id, column list)
VALUES (@NewVal, @Params);

COMMIT;

Do it in one step, not as separate Insert then Update steps.

This sample sets the isolation level very aggressively. One of the main problems with this kind of computation is that two concurrent inserts can potentially end up with the same value. Serializable and TablockX will prevent that. Will also slow the query down, but if you make the insert fast enough, that shouldn't matter.

With the right indexing on the table, the Max() query should be trivially fast. If not, then you'll want to move that part of the process into a single-row table. Record the next value in that table, use TablockX and query it, use that in the insert into the main table. Single-row query will be very fast, and you don't end up with hyper-aggressive locking on the main table if anything slows the insert down.

But really, you're probably better off just using a computed column for member_id. Then you don't have to do any of this. Why have two columns in one table that both go up by 1 for every insert? I'm sure there's a business reason for it, probably something along the lines of "we don't want the account numbers to be that small", but it can solved in more efficient, less error-prone ways. Like re-seeding the existing column to start at a higher value, or using a computed column that adds some reasonably large number to the existing column.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1377077
Posted Thursday, October 25, 2012 9:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, March 2, 2014 1:41 PM
Points: 18, Visits: 37
Thanks for your fast response.

The reason we're doing this is simple.

In order to upgrade our db environment, using clustering, mirroring or whatever methods we choose ... we feel that with another copy of our tables on another (redundant) server, it is too easy to end up with ...

DB1 - Table Member

member_id name
1 Charles
2 Brian

DB2 - Table Member (mirror)

2 Brian
1 Charles

where, because member_id column is identity, the member in one db can have a different account number (based on ident) than the other db.

What I do not understand is that when MS does mirroring, how does identity derived column get handled? When you insert a row that number is dynamically added. Or in a mirror, do they "relax" the restriction on the identity column, fill it in, and then reapply the restriction?

We've also thought about writing our own "quasi" mirror ... where ColdfuSion would automatically ever 2-3 minutes add new rows to db "2". In that case we would really not have a proper account number in the second db?

Am I making sense?

Robert
Post #1377085
Posted Thursday, October 25, 2012 9:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, March 2, 2014 1:41 PM
Points: 18, Visits: 37
We were also going to make the account number column a key. Then add it is a 0 and update it.
As we cannot add two 0's, (not unique) ... we would be locking the table until the update.
Your method is probably more clean. But we could still get two records with the same number?

Post #1377088
Posted Thursday, October 25, 2012 9:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, March 2, 2014 1:41 PM
Points: 18, Visits: 37
I typed in the line

DECLARE @NewVal BIGINT = (SELECT MAX(member_id) FROM dbo.member WITH (TABLOCKX))+1;

exactly ... and I get a syntax error Incorrect near '('

Post #1377098
Posted Thursday, October 25, 2012 10:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:20 AM
Points: 2,795, Visits: 5,581
Just noticed you said you test environment is 2005

You have to split up the declare and assign of the @newlVal variable.

So

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

DECLARE @NewVal BIGINT;

SELECT @NewVal = (SELECT MAX(member_id) FROM dbo.member WITH (TABLOCKX))+1;

INSERT INTO dbo.Member (member_id, column list)
VALUES (@NewVal, @Params);

COMMIT;


Just a note you posted in a 2008 forum on here so GSquared gave you a 2008 answer.

Cheers,

Rodders...



Post #1377112
Posted Thursday, October 25, 2012 10:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
britinusa (10/25/2012)
I typed in the line

DECLARE @NewVal BIGINT = (SELECT MAX(member_id) FROM dbo.member WITH (TABLOCKX))+1;

exactly ... and I get a syntax error Incorrect near '('



You posted the question in the SQL 2008 forum, and I missed that you're working in SQL 2005 in your post. That construct only works in 2008 and later versions.

As mentioned, split it in two. First declare, then Set.

DECLARE @NewVal BIGINT;

SET @NewVal = (SELECT MAX(member_id) FROM dbo.member WITH (TABLOCKX))+1;

If you're going to be using replication for this, and that's the reason for the change, then you should look in to GUIDs instead of IDs.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1377139
Posted Tuesday, October 30, 2012 6:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
britinusa (10/25/2012)
Thanks for your fast response.

The reason we're doing this is simple.

In order to upgrade our db environment, using clustering, mirroring or whatever methods we choose ... we feel that with another copy of our tables on another (redundant) server, it is too easy to end up with ...

DB1 - Table Member

member_id name
1 Charles
2 Brian

DB2 - Table Member (mirror)

2 Brian
1 Charles

where, because member_id column is identity, the member in one db can have a different account number (based on ident) than the other db.

What I do not understand is that when MS does mirroring, how does identity derived column get handled? When you insert a row that number is dynamically added. Or in a mirror, do they "relax" the restriction on the identity column, fill it in, and then reapply the restriction?

We've also thought about writing our own "quasi" mirror ... where ColdfuSion would automatically ever 2-3 minutes add new rows to db "2". In that case we would really not have a proper account number in the second db?

Am I making sense?

Robert


I don't think mirroring can result in that situation, with different ID values for the same data. I'm more accustomed to replication and log shipping for DR/BC, and haven't used mirroring much, so I could be wrong. But I think the way it works, from the transaction log, would pretty much prevent that from occuring.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1378743
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse