Global Sequencer
-
I want to be able to have a Unique sequence number accross multiple tables and databases (like having 1 server inserting odd numbers while the other one inserting even numbers). I used to accomplish this in oracle using a simple sequencer, What is the alternative to accomplish unique number accross the servers?
-
You could use the IDENTITY [ ( seed , increment ) ] property with varying seeds and increments.
Table1 ... GlobalUnq INT IDENTITY(1, 3) ... 1, 4, 7, 10...
Table2 ... GlobalUnq INT IDENTITY(2, 3) ... 2, 5, 8, 11...
Table3 ... GlobalUnq INT IDENTITY(3, 3) ... 3, 6, 9, 12...
Have the increment be the number of tables, and the seed be the "table number".
Just an idea.
Once you understand the BITs, all the pieces come together
-
Good workaround, but that will help me for 1 table only (or small amount of tables).
Suposse I have Table01, Table02, ..., Table99 (99 tables).
I wanna be able to have a UID in Table23 which will never match any other UID in any other table.
If such a thing is not possible, I will need to add a list_id column which make a unique compound index with the identity field. Such a change will need a overhaul in the applications tho.
-
If you're looking for a unique identifier, you could go for a GUID column. That is Globally Unique, so across tables, databases and servers.
Obviously, it is not a 'sequence' number, so it might not suit your needs.
-
Above should work for as many tables as you want...
Table1 ... GlobalUnq INT IDENTITY(1, 100) ... 101, 201, 301, 401...
Table2 ... GlobalUnq INT IDENTITY(2, 100) ... 102, 202, 302, 402...
Table3 ... GlobalUnq INT IDENTITY(3, 100) ... 103, 203, 303, 403...
....
Table99 ... GlobalUnq INT IDENTITY(99, 100) ... 199, 299, 399, 499...
Like I wrote: Have the increment be the number of tables, and the seed be the "table number".
If the number of tables is not known at this time, or could expand, give yourself some room to grow... Make the increment 1000, or 10000 ect. Might want to use BigInt instead of INT ???
Otherwise you might be looking at makeing an INSERT TRIGGER on every table, within it, querying & updating a "global sequence pool".
Once you understand the BITs, all the pieces come together -
Ozamora,
A GUID will produce a globally unique identifier, if that is what you need, then that's the ticket.
If you just need some ever incrementing unique numbers, then the IDENTITY with differing seeds and increments may be the way. This will NOT give you consecutive incrementing numbers across all your tables unless each and every table gets a new record at the same time. This method may be least "peformance detrimental".
If the INSERT to each/all of the target tables "runs" through the same code segment, that may be the place to put your logic to get / update the next increment #, then insert into the target table with that number. Or, do the work in triggers on each table. This kind of approach could give you consecutive incrementing numbers across all the tables since there is one source for the increment. Note: Failed or rolledback Inserts may leave gaps in the sequence. If that is not allowed, then you need some kind of "2 staged" coding approch.
Let us know why/how you need the "sequence", this may let us give you a more appropriate answer.
Once you understand the BITs, all the pieces come together -
A table refers to a client and contains customer information. This customer performs "actions" and they are stored in a huge partitioned view. The partitioned View contains the UID of this customer. So we can have as many tables as we want and just one partitioned view. The workaround will be to attach a List_id (which refers to the table) and combine it with the identity and thus have a unique compound identifier. Thanks all for your help.
-
That will work(around) fine.
BTW, Are you "vertically" partitioning for the purpose of query performance against the VIEW? so that SQL Srv. "drops" curtain tables from the execution plan as the result of the WHERE clause being used.
If so, did you once have one big table? Has performance been greatly affect by the vertical partitioning?
Thanks.
Once you understand the BITs, all the pieces come together -
Yes I did veritcally partitioned the table, and the view only has information of the last 14 days. Each day worth of data is stored in a just one table.
So how do I handle the same customer_id if he has actions done for many days? Each action is related to a batch_id, which is only related to one day. So the combination batch_id, list_id, customer_id is a non clustered compound primary key for each single table in the partitioned view.
Whenever I want to locate a customer_id, I will always query with the batch_id and list_id, thus making the optimizer look at just one portion of a table in the partitioned view.
To answer you second question, it has not greatly affected the performance, but it has greatly affected (positively) the maintenance of tables.
BTW: Partitioned views is a deprecated Oracle7 feature which is pretty well handled by Sql Server 2000.
-
Did you know that SQL Srv. will effectively "drop" a table from the execution plan if:
1. View is like SELECT ... UNION ... UNION ... UNION ... UNION ... etc.
2. The "partitioned" "root" tables have a ROW level CONTRAINT on a column that does not allow for certain values.
3. WHERE clause of the query of the view relates to the constrained column in #2 above.
In your case the "day of the data" or "batch_id" is your "constraint". Try adding a constraint to the batch_id column of one of your "root tables" that only allows for "that day's batch_id's". Notice changes in the Estimated Execution Plan when differrent WHERE clauses are used. The WHERE clause must have something like AND Batch_ID Between Val1 and Val2. If any of the tables "contrain" it's Batch_id outside of range defined by Val1 AND Val2, SQL "drops" the table(s) from the execution plan. There will be nearly zero access to that particular table, and may provide extreme performance enhancements under certain circumstances.
Let us know.
Once you understand the BITs, all the pieces come together -
Yes ThomasH. I checked the execution plan and the optimizer effectively drops the tables that are not needed and then uses the indexes required just in the table needed. Thanks for the explanation though.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply