Need a Database-unique ID key
-
You can have key with the ident property - its guaranteed unique for the table. You can have a guid - that's guaranteed universally unique.
Is there an attribute/data type for a key that is guranteed unique just for the database?
I want to avoid setting up a separate table that tracks the sequence of the key values if I can avoid it. Plus I'd like the field to be non-binary, so I'd like to avoid using a guid (plus our client app doesn't support guids anyway).
-
I am confused are you asking if the database it self can be globally uniqe??? Can you be more descriptive of your needs?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
GUIDs can be considered non-binary. An example of a GUID:
B579070C-412E-4ECB-9FFF-EE99B5D3FD9A
This can come across as char or varchar. For instance:
SELECT CAST(NEWID() AS char(36))
Other than that, if you want to keep key values unique just for the DB, you're probably going to have to do something to track it yourself. Even GUID isn't a 100% guarantee. The reason I say this is if two processes request a GUID at the same time, they'll get the same value. While very unlikely, the possibility is still there.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
What I meant was, I need a few fields (one field of same type in a few tables) and each value collectively in all those fields in the database must be unique. It doesn't have to be a guid (ie unique all over the universe!) just unique within each database.
What it is is a cheque number field for an accounting system. This cheque number can be recorded in the creditors, general ledger and payroll transaction tables. But as you only have one cheque number sequence in the organisation, these values must be unique for the database. As you can imagine, a guid isn't suitable for a cheque number.
I wanted to avoid tracking the sequence in its own table as that might present a performance/locking bottleneck.
-
Brian, I think I'd be willing to risk the guid. Not even sure it's possible to get a duplicate and even if it is, odds have to be pretty low. Add an index to guarantee uniqueness hard to see how you could get hurt.
Andy
-
Andy I agree with you, but am providing the standard warning.
I've been burned on NICs with the same MAC address once upon a time, after all. Miked, I think you would have to track this in a separate table. However, thinking about this, since you're dealing with a cheque number, you can leverage that table to your advantage to store some metadata about the cheque or at least about who initially created it in the system. Might be a "feature" that is meritable.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
Its only partially built on the MAC address, so maybe it wouldnt matter? I agree with what you're saying, just wouldnt want to make people avoid GUID's on a really low risk possibility. Though I think Im the only one evangelizing them!
Andy
-
It's partially on the MAC (or a psuedo MAC if there's no NIC) and also on the time, but I don't know to what slice... so while it wouldn't affect say an application compile, it could theoretically affect two separate transactions at the DB level... key word is "theoretically." The only reason I don't like GUIDs is the size.
But I've used GUIDs where appropriate and will continue to do so. I've never had a constraint violation, either. So I've never run across the prob, but I guess it has happened (since Microsoft put the warning in BOL)?
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
There are two simple (maybe too simple) solutions:
1) use different start numbers for the identity, for every table, with ‘enough’ space for estimated use.
Table A: identity (1,1),
Table B: identity (10000,1),
Table C: identity (20000,1) ….
2) use different start numbers and one single increment (for say a max of 15 tables in a Db)
Table A: identity (1,15),
Table B: identity (2,15),
Table C: identity (3,15)…
quote:
You can have key with the ident property - its guaranteed unique for the table. You can have a guid - that's guaranteed universally unique.Is there an attribute/data type for a key that is guranteed unique just for the database?
I want to avoid setting up a separate table that tracks the sequence of the key values if I can avoid it. Plus I'd like the field to be non-binary, so I'd like to avoid using a guid (plus our client app doesn't support guids anyway).
-
quote:
Andy I agree with you, but am providing the standard warning. I've been burned on NICs with the same MAC address once upon a time, after all. Miked, I think you would have to track this in a separate table. However, thinking about this, since you're dealing with a cheque number, you can leverage that table to your advantage to store some metadata about the cheque or at least about who initially created it in the system. Might be a "feature" that is meritable.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
I would agree with this approach. At a general level, their are certain things common to all cheques: an amount, a payee, a date, etc. I would create a single table, call it Cheques, for example, with columns denoting those attributes which are common to all cheques. Then I would create child tables for different types of cheques - if necessary - that would provide columns for attributes specific to different types of cheques. Child tables would have foreign keys pointing back to the general table of cheques.
If you're worried about having to enter data into two separate tables (e.g. a general ledger plus a cheques table, or a creditors table and a cheques table) then consider creating views that join the various child tables to the general cheques table; data entry and queries can be done with the views as opposed to direct table access. This will mask the physical structure necessary to support the requirements of your logical structure.
Matthew Burr
-
Forgive me if I'm missing the point, but I think you're talking about two different things at least...in my application, all client adjustments have a unique serial number which also happens to be shared across 3 tables. I have a separate table that holds counters and a function which enforces integrity to ensure two users don't get the same number from the counter table. However, a cheque number is not going to be something which can use a system-generated value. I keep the cheque number and the transaction serial number in separate columns, because the cheque number is captured on cheque postings from the user interface. Cheques being printed are notorious for jumping and re-prints etc. Unless you're talking about a very expensive printer that can print the number on the cheque, this won't work.
Sorry if this is not quite on topic.
Regards
Simon
-
Regardless of how the cheque number is entered into the system, whether by GUI or by auto-generation, maintaining a single table of cheques allows you to insure uniqueness of cheque numbers (presuming that is a business requirement) and perform quick searches on past cheques (whereas searching for the information would be slightly more intensive if you had to search for a cheque in several tables).
Matthew Burr
I've been burned on NICs with the same MAC address once upon a time, after all. Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply