Sequence – Union / Union all

You have known the difference between UNION and UNION ALL. But there is similarity when used sequence. It is not possible to use them to combine sequence values.

Create the following sequence

CREATE SEQUENCE MYSEQ
AS INT
START WITH 1 INCREMENT BY 1

You get the sequence value using

SELECT NEXT VALUE FOR MYSEQ 

If you want to use UNION or UNION ALL combing different sequence values, it is not possible. Both the following codes when executed throws error

SELECT NEXT VALUE FOR MYSEQ
UNION
SELECT NEXT VALUE FOR MYSEQ 

SELECT NEXT VALUE FOR MYSEQ
UNION ALL
SELECT NEXT VALUE FOR MYSEQ 

There error is

Msg 11721, Level 15, State 1, Line 2

NEXT VALUE FOR function cannot be used directly in a statement that uses a DISTINCT, UNION, UNION ALL, EXCEPT or INTERSECT operator.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s