2

As the question states, what are the downsides of using NewSequentialID as the default value of a table vs NewID()? The obvious advantage is that it won't fragment our index as much.

Is there any concern for ever maxing out the sequence?

Dilbert789
  • 864
  • 2
  • 14
  • 29
  • 2
    Well, for one, it's still a very wide key compared to, say, an INT. And you've lost one of the charms that the value is relatively random and not predictable. What are the advantages you have established in favor of using a GUID at all (regardless of fragmentation)? – Aaron Bertrand Jul 29 '13 at 19:14
  • 1
    If you're already aware of the int/big_int vs GUID arguments.....the only "disadvantage" of using the NewSequentialID is that someone may be able to guess a GUID. Like, if you had a webapp that said /EmployeeEdit.aspx?EmployeeKey=123....your end user may be able to "guess" /EmployeeEdit.aspx?EmployeeKey=124. This same thing could be done with NewSequentialID... – granadaCoder Jul 29 '13 at 19:24
  • The other major advantage is that you get reliability sortability by date created (if that's important to you), which you may not be able to get even with a `CreateDate` column. – Joe Enos Jul 29 '13 at 20:32
  • NewSequentialID contains the server's MAC address (or one of them), therefore knowing a sequential ID gives a potential attacker information that may be useful as part of a security or DoS attack. – redcalx Mar 12 '14 at 15:42
  • @AaronBertrand The advantage of using the GUID is that the existing code already does this. We're looking at probably a couple months each of dev and testing time to try to change ID's to INT values. – Dilbert789 Apr 09 '14 at 18:54

2 Answers2

0

I don't see how a default value on a field could really be a disadvantage.

If you want to control the ids of some records before you insert them, it can be handy to use NEWID() instead of the default sequential id (so you can generate the records and their associations before you interact with the database, and you won't have to query it afterwards to get the ids back). Although the two are not mutually exclusive...

As granadaCoder said, the sequential ID could be inferred, but IMO the benefit is so great in term of performance and maintenance that it would be a mistake not to use it.

jazzytomato
  • 6,994
  • 2
  • 31
  • 44
  • You can mimic a "client side" "sequential" id with UuidCreateSequential http://www.pinvoke.net/default.aspx/rpcrt4.uuidcreatesequential As Thomas says, the benefit of guid's is you can "wire up" all associations before shipping to the database.....aka, you don't have to wait for the IDENTITY columns to return their values to insert child (FK) records. But (as mentioned by aaron-b), there are some disadvantages. Choose wisely. – granadaCoder Jul 29 '13 at 23:37
0

newsequesntialid is not supported by Azure

Jonathan de M.
  • 9,721
  • 8
  • 47
  • 72
alexs
  • 1,507
  • 18
  • 17