Using a SEQUENCE object to generate letters.

SQL Server 2012 introduces a new T-SQL object used to generate numbers, a SEQUENCE, similar to an identity column. You can think of a sequence as a take a number object, you just ask it to give you a number, and you get a number.

When creating a SEQUENCE you need to specify the following:

• Start
• Increment
• Min Value, Max Value
• Cycle / No Cycle – starts of when the Max value is hit or not
• Cache

The SEQUENCE is all about generating numbers…

The question comes up on using a SEQUENCE to generate letters, to rotate through a SEQUENCE of A to Z, then to start over?

Technically a SEQUENCE can only generate numbers, but here is how you can use it to generate letters.

Here is the sample code to do it.

CREATE SEQUENCE lettersSequence
 AS tinyint
 MINVALUE 65
 MAXVALUE 90
 CYCLE
 INCREMENT BY 1 ;

SELECT char(NEXT VALUE FOR lettersSequence);

SELECT char(NEXT VALUE FOR lettersSequence);
SELECT char(NEXT VALUE FOR lettersSequence);
SELECT char(NEXT VALUE FOR lettersSequence);
SELECT char(NEXT VALUE FOR lettersSequence);
SELECT char(NEXT VALUE FOR lettersSequence);
SELECT char(NEXT VALUE FOR lettersSequence);

Enjoy.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!