Getting back to our common theme on this blog regarding Oracle & SQL Server coexistence, migration and integration, I want to touch today briefly on a new feature in SQL Server v-next / Denali / SQL 11 / SQL 2011, whatever you wish to call it at this time. Let’s call it by code name Denali. CTP 1 is avaliable for download here. For as long as SQL Server has been around, developers have had not-so-nice things to say about the limitations on the T-SQL Identity column property, used for a similar numeric sequences in tables to PL/SQL sequences. It should be noted that sequences are standard SQL objects and SQL Server has used identity to implement similar functionality. Interestingly, Oracle-to-SQL Server migration tools such as SQL Server Migration Assistance for Oracle, a free download from Microsoft.com, utilizes its own sequence generator when converting to T-SQL. This way, you can use the Oracle CURRVAL and NEXTVAL.
With SQL Server identity, you do not get flexibility in that it is tied to a single table while sequence objects are not. They are separate manageable objects. You also do not set min, max and steps cannot be modified in an identity column.
Here is sample code for creating a sequence:
SET NOCOUNT ON;
— Specific INT in most cases
CREATE SEQUENCE dbo.MySequence AS INT
START WITH 1
INCREMENT BY 1;
— Generate next value for sequence
SELECT NEXT VALUE FOR dbo.MySequence;
— In your table, you can store that sequence as an INT column
CREATE TABLE dbo.MyTable
MyID INT NOT NULL,
MyName VARCHAR(50) NOT NULL,
CONSTRAINT PK_ID PRIMARY KEY(MyID)
INSERT INTO dbo.MyTable(MyID, MyName) VALUES(NEXT VALUE FOR dbo.MySequence, ‘Mark’);