Microsoft SQL Server

SQL Server 2012 comes bundled with new T-SQL statements. Some of these features are useful and essential. T-SQL Enhancement are rare to be seen regarding new statements. But, Microsoft has decided to change the product now and then.

These new T-SQL statement will for sure help T-SQL developers and DBAs. But, if you have to write a common script which might be ran on old servers, try to avoid using them. In general, if you have one development server and related production server for the same. For such environment, you may use new features without any risk.But, if you are writing a script which is common for SQL Server 2008 - R2 and previous versions, you might have to avoid these new features. Ok, let's start with T-SQL Enhancements.

[1] Sequence:

A Sequence is an auto-number with increments after each usage. Sequence can look like identity column, but Identity column belongs to a table, but sequence is database wide object and can be used in multiple places in your script.

This feature has below advantages -
1. It can be utilized in multiple stored procedures where you want a global numeric value to be incremented every time.
2. You may decide what increment you want and you can name a sequence. There can be as many sequences you want.

Creating a Sequence:

CREATE SEQUENCE [NewSequence] START WITH 1 INCREMENT BY 1
 

The above statement creates a sequence with name NewSequence. This sequence has staring number as 1 and increment is also by 1.

How to use a Sequence?

To use a sequence, you will use NEXT VALUE FOR statement which is also new statement.

SELECT NEXT VALUE FOR [NewSequence] 

Let's test it in Management Studio. I will suggest you to open two Query windows in Management and run the Select statement mentioned above. You will see new values incrementing on both the sides and both of Query windows updating the same.

(Article is not completed yet...)

- Som Dutt Tripathi