Derived primary key function or horizontal partitioning?

[TerryMc]

I am building an app for our lab that requires a primary keys that codes unique incremental codes for multiple locations. The key field(s) must have a component to identify which location added the record and an incremental sequence that will be different for each location. These numbers are used to label samples in the lab and need to be gapless if possible because the samples need to be retained and physically filed by these labels.

It will need to look something like this in a view.

Derived from some function where the first digit would indicate the location and the following numbers the unique incremental sequence for that location.

[SamplesTbl]
Workorder
1000001
1000002
2000010
2000011
1000003
2000012

or using a compound key

[SamplesTbl]
LocationID Seq
1 1
1 2
2 10
2 11
1 3
2 12

I have two ideas on how to accomplish this both of which have some limitations so I am looking for some feedback/opinions.

Option 1 (Using one SamplesTbl)

The only way I can see to do this is with some kind of ID table which holds the next available Seq number for a location then some kind of Key Generator which does something like this.

UPDATE generator SET keyval = keyval+1
Select keyval into keyval from generator
…
COMMIT (though I really don’t know how to write this yet)

This relies on UPDATE to hold a lock, but can’t this lead to some ‘Lock Contention’ issues if I am using this as the backend of a .NET app where I will enter data and someone goes for lunch in the middle of adding records. How can I design the app to generate the key value at the last possible moment so that the lock is release quickly. I need ‘Gapless’ numbering if I can manage it so an immediate COMMIT before the ‘real’ transaction would waste a number if the subsequent ‘real’ transaction is rolled back.

This seems like it could be a lot of work plus I still need a way to identify which location is adding the record so I can update the correct keyval. I am hoping I can use IS_MEMBER or IS_SRVROLEMEMBER to tell which location they are at (assuming I place the users in different security roles based on location)


Option 2 (Using horizontal partitioning)

Instead of putting the samples info into one table I could create identical tables for each location ie

CREATE TABLE SamplesLab1 (LabID int, SampleNum int, …
CREATE TABLE SamplesLab2 (LabID int, SampleNum int,…

Then I could add a constrant and/or default value to the LabID then use an identity column for the Sample ID then make the two the compound Primary Key. This method would get me away from all that key generation stuff, which seems a much easier way to go. But I would need to create partitioned views such as.

Select * FROM SampleLab1
UNION ALL
Select * FROM SampleLab2

Which works fine but how do I update(edit) the data in the partitioned view . It is ‘supposed’ to be possible in SQL Server 2000 but I cannot figure out how.


Any advise or opinion on which way I should go with this would be greatly apretiated.

Terry

 

[NoCoolHandle]

1 year ago, I would have said go for the horizontal partition with a location column. Now I would tend to go toward the function option or a calculated column which is a combo of the location col and an indentity field.

The reason for this is query prerformance. However if query performance is not an issue and insert is more important I would have gone for the first option.

Background:
We have an app that querys 24,000,000 records for a single value. When we build a single col keyfield we were able to bring back rows significantly faster. This would help in updates/joins or just point querys. For a multi col search, the querys returned on an averabe of .009 seconds but upto .037 and for a single col (contrived col -location/key) we were getting an average of .003 with an upper of .017.

Therefore seeing a 300% improvement by combining the two columns.

The obvious tradeoff is that this Column needs to have it's value created or merged at insert, so will slow inserts (slightly).

Hope this helps

Rob

 

[TerryMc]

Thanks Rob

I think you may be right. We will never get anywhere near 24 million records, we will add maybe 100K records/year and we may not bring much data in, but query speed could be an issue.

The app I am replacing is Access 97 so I don’t have much to go on but it has some very complicated reports for sample results that have joins on 10+ tables plus queries in sub-reports with more multi-table joins. These reports are critical, as they need to be created one by one in real-time, and one big reason we are going SQL Server is that the reports were ridiculously slow.

This ‘Workorder’ field will be all through the tables as an FK. I generally don’t like using an auto increment like the 'identity' as testing, deployment, renumbering etc is much simpler without it, but in this case it made things much simpler for me and I am new to SQL Server programming.

Insert speed should not really a major issue, as we will not have many users entering new sample records, but concurrency maybe.

When I add new records I will insert data into apox 50 fields. Using the ‘combo’ key I will need some kind of an ‘ID Table’ if I understand correctly. Do you have any suggestions/examples I could look at to write my function to create this key, and how best to deal with multiple users requesting ID’s at the same time.

I am still quite green with SQL Server and any help would be greatly appreciated.