October 25, 2012 at 7:55 am
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
October 25, 2012 at 9:25 am
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
October 25, 2012 at 9:35 am
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
October 25, 2012 at 9:38 am
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?
October 25, 2012 at 9:56 am
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 '('
October 25, 2012 at 10:19 am
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...
October 25, 2012 at 10:58 am
britinusa (10/25/2012)
I typed in the lineDECLARE @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
October 30, 2012 at 6:55 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply