February 24, 2011 at 7:38 am
Hi All,
I have a table with a PK of type bigint. Let's call the field "ID". The rule I have to follow is that the ID value of a new record is determined by applying a complex mathematical function to the ID of the previous record. I can't go into detail as it's a confidential algorithm but hopefully it's the principal that matters.
If I was just inserting one record I could do something like:
INSERT myTable (ID) -- (it's not really called that, Joe)
SELECT dbo.MyFunc(MAX(ID)) FROM myTable
However, this is a table with many rows. I may wish to insert a million at a time. Is there a way to accomplish this without resorting to the type of RBAR logic above?
Thanks,
Richard
February 24, 2011 at 9:21 am
you could do it in a trigger probably
but that would be a rbar in the trigger
or you could try a computed fields which should work
February 24, 2011 at 9:43 am
I think it would be best to incorporate the logic of the function into the INSERT statement especially as access to myTable will need to be SERIALIZED.
This could be done directly from a SP or via an INSTEAD OF trigger.
February 24, 2011 at 10:12 am
Sounds like a proprietary SEQUENCE to me. Can you refactor?
Your proprietary sequence (you called it ID) had to start somewhere so you must have a seed. Since all proprietary "ID"s are derived from the previous proprietary "ID" tracing back to the original seed then you ought to be able to pre-generate a table containing as many proprietary "ID"s as you may ever reasonably need.
1. Create new "sequence control" table: CREATE TABLE dbo.proprietary_ID_control
(
ID BIGINT NOT NULL
IDENTITY(1, 1)
CONSTRAINT [pk_dbo.proprietary_ID_control] PRIMARY KEY,
proprietary_ID BIGINT,
CONSTRAINT [uk_dbo.proprietary_ID_control.proprietary_ID]
UNIQUE NONCLUSTERED (proprietary_ID) -- for safety
) ;2. Add a new column to dbo.myTable called ID_temp as BIGINT IDENTITY(1,1) NOT NULL. NOTE: this may take some time to add depending on the size of the table and the table will be locked during the operation.
3. With IDENTITY_INSERT ON back-fill dbo.proprietary_ID_control mapping dbo.myTable.ID_temp to dbo.proprietary_ID_control.ID and dbo.myTable.ID to dbo.proprietary_ID_control.proprietary_ID. Remember to SET IDENTITY_INSERT OFF on dbo.proprietary_ID_control.
4. Drop the column ID from dbo.myTable.
5. Rename the column ID_temp to ID on dbo.myTable.
That's you're schema refactoring.
To support new business and existing code:
1. Generate as many new proprietary IDs into dbo.proprietary_ID_control as you think you'll ever need.
2. Refactor your queries to join into dbo.proprietary_ID_control to pickup the proprietary_ID using ID.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 24, 2011 at 10:16 am
Thanks, both.
I see what you mean but it does look as though both of these are very much row-based. Perhaps, given that the value of record(n) always depends on the value of record (n-1) that's the only way to do it.
OPC - thank you too - your post arrived while I was typing the above. I think that method is definitely worth exploring and I'll look into it further. I don't know exactly how many of these I may "reasonably need" but it could well be >100 million. But if it takes a couple of days to generate the table it doesn;t really matter as it's a one-off.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply