2 of my favorite new T-SQL features in SQL Server Denali (SQL 11)

Microsoft released the first CTP version of SQL Server Denali during the SQLPASS Community Summit in Seattle last year. Since this major event in the SQL Server community I had a little bit of time to look into the CTP 1 version of Denali and see what new features they are providing us. 2 interesting features in the area of T-SQL I want to cover today throughout this blog post: Sequences and Paging

Let’s start with sequences first. If you already worked with Oracle you should be already familiar with sequences, because they are used everywhere in the database design. For SQL Server sequences are a completely know concept. A sequence is nothing else like an INT IDENTITY column, but with the difference that a sequence is created on the database level and can be used and accessed through all your database objects (like tables, stored procedures, stored functions, etc.). Sequences are created with the new T-SQL statement CREATE SEQUENCE:

CREATE SEQUENCE MyNewSequence
   AS INT
   MINVALUE 1
   MAXVALUE 1000
   START WITH 1
GO

As you can see, the sequence get’s a name which must be unique within the current database. Then you have to specify the underlying data type of the sequence, like TINYINT, SMALLINT, INT, BIGINT. You can also specify a minimum and maximum value (MINVALUE, MAXVALUE) and which value your sequence should return, when the sequence is accessed for the first time (START WITH). To track all your sequences within a database, SQL Server provides you a new system view – sys.sequences:

SELECT * FROM sys.sequences
GO

After you have created a new sequence you can start using it through the new command NEXT VALUE FOR:

DECLARE @id INT
SET @id = NEXT VALUE FOR MyNewSequence

SELECT @id
GO

Read more: SQL Server Central

Posted via email from .NET Info