Tuesday, May 22, 2012

SQL Server 2012-T-SQL

As part of the SQL Server 2012 release there have been several enhancements. One of the most used in the database side of things is T-SQL, it has a very wide application. In oracle there is a feature called sequence which is table independent and the new value can be obtained any time as well as during UPDATE. This is a powerful feature which has been non existent till SQL Server 2008. In SQL Server 2012 the concept of sequence has been introduced. In the SQL Server Management studio one can fin sequences under programmability under the Database. It is illustrated in the image below.

For example in the user database called Features I created a sequence called seq_test where the start value is set to 1 and is incremented by 1. The syntax is given below.


The statement above will create a sequence seq_test. Now In order to retrieve sequential values from the sequence use the following statement:

SELECT NEXT VALUE FOR dbo.seq_test. This would return the value 1 since the starting value is set to 1. When the select statement is run again the value will be 2.
Now If I were use this sequence in a insert statement where in one of the values is an incremental value I could do the following: Let us assume we have a table called Employee which a id column and a name column.
INSERT INTO dbo.Employee
VALUES(NEXT VALUE FOR dbo.seq_test,'James');
In Summary this is a useful feature which can be utilised by developers in situations where a incremental type value is needed.

No comments:

Post a Comment