SQL Server中的数据库范围唯一但简单的标识符 [英] Database-wide unique-yet-simple identifiers in SQL Server
问题描述
首先，我知道这个问题和建议（使用GUID）不适用于我的情况。
First, I'm aware of this question, and the suggestion (using GUID) doesn't apply in my situation.
我想要简单的UID，以便我的用户可以通过电话轻松地传达这些信息：
I want simple UIDs so that my users can easily communicate this information over the phone :
您好，我的订单有问题
1584
Hello, I've got a problem with order 1584
hello，我有一个订单问题
4daz33-d4gerz384867-8234878-14
hello, I've got a problem with order 4daz33-d4gerz384867-8234878-14
我想要那些是唯一的（数据库范围），因为我有几种不同的'对象'...有订单ID，和交货ID和计费ID，并且由于这些之间没有一对一的关系，我无法猜测ID是什么类型的对象。
I want those to be unique (database wide) because I have a few different kind of 'objects' ... there are order IDs, and delivery IDs, and billing-IDs and since there's no one-to-one relationship between those, I have no way to guess what kind of object an ID is referring to.
使用数据库范围的唯一ID，我可以立即知道我的客户指的是什么对象。我的用户可以在搜索工具中输入ID，我保存他的额外点击进一步优化搜索。
With database-wide unique IDs, I can immediately tell what object my customer is referring to. My user can just input an ID in a search tool, and I save him the extra-click to further refine what is looking for.
我目前的想法是使用不同种子1，2，3等的标识列，增量值为100.
My current idea is to use identity columns with different seeds 1, 2, 3, etc, and an increment value of 100.
这产生了一些问题：
-
如果我最终得到100多个对象类型，
What if I eventually get more than 100 object types? granted I could use 1000 or 10000, but something that doesn't scale well "smells"
种子被丢失的可能性复制，数据库问题等等）
Is there a possibility the seed is "lost" (during a replication, a database problem, etc?)
更普遍的是，我还应该注意其他问题吗？
more generally, are there other issues I should be aware of?
是否可以使用非整数（我目前使用bigints）作为标识列，以便我可以在ID前面加上代表对象类型的东西？ （例如varchar列）
is it possible to use an non integer (I currently use bigints) as an identity columns, so that I can prefix the ID with something representing the object type? (for example a varchar column)
这将是一个好主意，使用一个主表只包含一个标识列，所以我可以只需插入一行，当需要一个新的想法。我觉得它可能有点过分，我恐怕会复杂我所有的插入请求。此外，我将无法确定对象类型，而无需查看数据库
would it be a good idea to user a "master table" containing only an identity column, and maybe the object type, so that I can just insert a row in it whenever a need a new idea. I feel like it might be a bit overkill, and I'm afraid it would complexify all my insertion requests. Plus the fact that I won't be able to determine an object type without looking at the database
有其他聪明的方法来解决我的问题吗？ p>
are there other clever ways to address my problem?
推荐答案
为什么不在所有表格上使用标识，给用户，简单地粘上单个字符的类型？例如O1234是一个订单，D123213是交货等等？这样你不必工程师一些疯狂的计划...
Why not use identities on all the tables, but any time you present it to the user, simply tack on a single char for the type? e.g. O1234 is an order, D123213 is a delivery, etc.? That way you don't have to engineer some crazy scheme...
这篇关于SQL Server中的数据库范围唯一但简单的标识符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
