Although it is a old feature for those who knows ORACLE but for SQL server developers it is a new feature.
Let understand it by an example. Suppose we want an auto incremented column a part from primary key which is a identity column,
then to achieve this we can use sequence feature.
We can create sequence feature by following command
“CREATE SEQUENCE StudentEnrollmentId AS INT
START WITH 2014000
INCREMENT BY 1”
so if you see above statement we have created a sequence with name StudentEnrollmentId which is an integer type sequence and first value means starting point is 2014000 and each time when we call sequence it will be incremented by 1.
We can create same sequence by screen also as shown in below figure
We have other option also as shown in below
CREATE SEQUENCE SEQUENCE_NAME
START WITH <constant>
INCREMENT BY <constant>
CYCLE | NO CYCLE
CACHE int | NO CACHE
as shown in above option we can provide minimum & maximum for sequence. We have cycle option mean restart again after reaching maximum or minimum.
Now we can use it with following way
“SELECT NEXT VALUE FOR StudentEnrollmentId”
I hope this might help you somewhere.
Sometimes we require sequence column or we can a row number column so we have ROW_NUMBER() function in SQL SERVER.
Lets understand this by following example
In this example we have person table which have firstname, last name, middlename columns. Now we want to fetch records with a extra column ROW_Number() so we write following query
SELECT ROW_NUMBER() OVER (ORDER BY LastName) As ROWNO,FirstName,MiddleName,LastName FROM [Person].[Person]
When we run above query we get following result set. If you see below result set we have one extra column which is ROWNO
Lets understand the query in this we are getting sequence or Row number order by Last name.
There is one more option with ROW_NUMBER which is partition by
lets understand this by below query what we want for we want row number for each record based on last name means whenever new last name introduce row number start again from one. to achieve this we have to write following query
SELECT ROW_NUMBER() OVER (PARTITION BY LastName ORDER BY LastName) As rowNo,FirstName,MiddleName,LastName FROM [Person].[Person]
See below snap when we run we get following result
If you see above snap you will find that when the Last name change row number also change so we did partition by last name means for each change.
I hope you might have used earlier but I thought It might be possible it will helpful to someone who not aware.