4

While searching for UUIDs I stumbled upon ULIDs https://github.com/ulid/spec

And I want to use them in my SQL Server database as primary key for my users (instead of basic incremental value).

So I wanted to ask:

  1. What datatype is the best suited? uniqueidentifier? binary16? something else? and why?

  2. Are there some other extra steps to fully benefit from ULID structure? especially sorting/searching? in comparison to other UUIDs.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Kebechet
  • 1,461
  • 15
  • 31
  • 1
    Do you have any reason at all to not just use an `identity` specification, which I guess is what you meant by mentioning "basic incremental value"? If yes, tell us these reasons so we can help you. If no, go for a bigint identity. – George Menoutis Jul 15 '22 at 22:48
  • 1
    The reason not to use `identity` is quite simple. Security. I want accountId be an unique specified which client will have in `JWT` (between API and client app). And from `identity` you can 1) find out how many users were already registered by creating new account and checking that value 2) do iteration attacks (depends on implementation), so thats why I want to use some kind of `UUID` instead of `int`/`bigint` `identity` and through those `UUID` docs I came to `ULID`. I use `identity` on other columns that are not exposed to client side. – Kebechet Jul 16 '22 at 10:22
  • Fair reasoning. As food for thought, I will argue that a bigint starting at 1 billion, or some other large-yet-arbitrary number will provide false, thus dubious, information about total rows. – George Menoutis Jul 16 '22 at 11:01
  • 1
    Thought about that as well, from this approach you are still not protected against iteration attacks/iteration bruteforcing and also you for example are able to see daily/weekly increment in users. By so many things you have to take in consideration I ended up just with UUIDs and the ULID seems to be the most promising one. But I havent found much about its implementation online. – Kebechet Jul 16 '22 at 11:07
  • 1
    There is one mistake in your reasoning: Getting the largest value of an identity column does *not* tell you how many users have registered. Identity columns are not guaranteed to be sequential, and in fact will nearly always have [gaps](https://stackoverflow.com/questions/14642013/why-are-there-gaps-in-my-identity-column-values#:~:text=The%20main%20reason%20is%20rollbacks,IDs%20series%20w%2Fo%20gaps.). Moreover, you don't have to seed the identity at 1. The "first" identity value can be any integer you like. But yes, security-by-obscurity may be of some benefit to guard against iteration. – allmhuran Jul 17 '22 at 17:27
  • for ULID in sql server , you should use binary(16). otherwise, if you use uniqueidentifier, then IDataReader will convert value to GUID , not ULID – goldii Apr 27 '23 at 09:42

0 Answers0