Converting Sequence in Oracle to Identity in MSSQL-CodePudding

I'm trying to recreate some tables originally in oracle to a new database in Azure Data Studio (MSSQL) and having trouble with sequences. I was able to create all of the sequences in use but found out after the fact that they can't be used in table creation like they can in Oracle and I'm now not sure how to create the tables.

How would I recreate this in MSSQL with identities if sequences can't be used in CREATE TABLE or ALTER TABLE statements?

Example originally from Oracle attempted in MSSQL:

CREATE SEQUENCE SEQ_Example MINVALUE 1 INCREMENT BY 1 START WITH 1;

CREATE TABLE Example
(
    ADDRESS_ID  NUMBER DEFAULT SEQ_Example.NEXTVAL NOT NULL,
    ADDRESS     VARCHAR2(250),
    CITY        VARCHAR2(250),
    STATE       VARCHAR2(50),
);

Error received in Azure:

The name "SEQ_Example.NEXTVAL" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

CodePudding user response：

The SQL ISO standard use the expression "NEXT VALUE FOR <sequence_name>" to give a new value in place of the DEFAULT constraint into the column definition and MS SQL Server conforms to the standard.

An other way to do this is to use the IDENTITY property like :

CREATE TABLE Example
(
ADDRESS_ID  int NOT NULL IDENTITY, 
ADDRESS     VARCHAR(250),  
CITY        VARCHAR(250), 
STATE       VARCHAR(50),
... )

In this case, MS SQL Server does not conforms to the standard by the fact that IDENTITY way invented years ago by Sybase (in the early 80's)