9

In the documentation of CREATE SEQUENCE for T-SQL, you can see that the CREATE SEQUENCE command has no ORDER parameter.

For comparison, the Oracle docs for CREATE SEQUENCE show the ORDER/NOORDER options:

ORDER

Specify ORDER to guarantee that sequence numbers are generated in order of request. This clause is useful if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys.

ORDER is necessary only to guarantee ordered generation if you are using Oracle Database with Real Application Clusters. If you are using exclusive mode, sequence numbers are always generated in order.

NOORDER

Specify NOORDER if you do not want to guarantee sequence numbers are generated in order of request. This is the default.

Does Microsoft SQL Server provide a strong ordering constraint for SEQUENCEs? Or does Microsoft not consider it important in general?

  • 2
    With Oracle this typically only makes sense with a RAC - as far as I know there is nothing equivalent to a RAC in SQL Server. Why do you think you need it? – a_horse_with_no_name Aug 2 '19 at 10:35
  • I'm just making a program compatible with SQL Server. It creates some sequences, and in the CREATE SEQUENCE statements, I ran into the ORDER parameter, and I just wanted to be sure that it wouldn't be a problem (I am unsure of exactly why the ORDER was added in the first place so I thought I might ask just in case). – Daniel Gray Aug 2 '19 at 12:03
12

In SQL Server all the sequence numbers are generated on a single instance so they are already inherently ordered. And if you’re just using them as unique numbers it shouldn’t really matter anyway.

The Oracle implementation is different because in a RAC environment (not all Oracle implementations), two sequence numbers could be generated by two different “instances” (that’s the wrong term for Oracle but just drawing equivalency in SQL Server). Normally you wouldn’t care if the one requested first was slower and got a higher number than the one requested later - unless, as the Oracle docs say, you were using the sequences as timestamps to truly reflect the order of the original request.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.