Something for Oracle PL/SQL Developers in Denali

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;
USE AdventureWorks2008R2;

— 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’);

Here are my favorite 2 postings that I’ve read so far to give you good depth of knowledge on using the Sequence object in CTP 1 of Denali from T-SQL guru master Itzik Ben-Gan here and here.

Advertisements

2 responses to “Something for Oracle PL/SQL Developers in Denali

  1. Pingback: Tweets that mention Something for Oracle PL/SQL Developers in Denali « Blog Home for MSSQLDUDE -- Topsy.com

  2. Pingback: Think About Replacing Uniqueidentifier with Sequence in Denali « Blog Home for MSSQLDUDE

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s