en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP

 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize

Recent Entries

Minimize
ספט22

Written by: ronen ariely
22/09/2013 03:55 RssIcon

In this short blog I will show how easy it is to add sequence's column to an existing table which already have data. Sequence is a new element added to SQL server 2012 (and can be find in MySQL and Oracle for a very  long long… long time) for implementing automatically assigned integer values. We can even assign a sequence to an existing integer column which already have values in it, in a direct simple query (not like an Identity column).

-- DDL
CREATE TABLE MySequenceTbl (
    MyCities NVARCHAR(10)
)
GO
  
-- DML
INSERT INTO MySequenceTbl
    VALUES ('York'),('Boston'),('Chicago')
GO
  
-- CHECKING DATA
SELECT * FROM MySequenceTbl
GO
  
-- Create Sequence Object
-- This is an independent Object and not a table's feature like Identity.
-- We can use any Sequence on any table.
-- In this script I create new sequence for the use with our table.
CREATE SEQUENCE MYSequence
    AS INT
    START WITH 1
    INCREMENT BY 1
GO
  
-- Adding Column (if we don't have one, or using integer column that exist)
ALTER TABLE MySequenceTbl
    ADD ID INT
GO
  
-- insert value on existing records if this is a new column
UPDATE MySequenceTbl
    SET ID = (NEXT VALUE FOR MYSequence)
GO
  
-- CHECKING DATA
SELECT * FROM MySequenceTbl
GO
  
-- Adding CONSTRAINT, using our Sequence, to the table
-- This part creates the relationship between the table and the Sequence.
ALTER TABLE MySequenceTbl
    ADD CONSTRAINT MySeqConstrain DEFAULT (NEXT VALUE FOR MYSequence) FOR [ID]
GO
  
-- Adding data and checking the results
INSERT MySequenceTbl ([MyCities]) VALUES ('Seattle')
SELECT * FROM MySequenceTbl
GO
  
-- Clean
DROP TABLE MySequenceTbl
DROP SEQUENCE MYSequence
GO

Have Fun :-)