jangorecki jangorecki - 1 month ago 16
SQL Question

SQL Server - sequence behind the view

I would like to isolate query next value of the sequence on the database side so my application just needs to query from view and it obtain its unique id of process.



CREATE SEQUENCE SEQ_VID MINVALUE 1 MAXVALUE 2147483647;
CREATE VIEW VID AS SELECT NEXT VALUE FOR SEQ_VID AS ID;
-- I got following error:
-- NEXT VALUE FOR function is not allowed in check constraints, default objects, computed columns, views, user-defined functions, user-defined aggregates, user-defined table types, sub-queries, common table expressions, derived tables or return statements.


While the approach works fine on postgres and h2 db it looks it will not work for sqlserver.

I've found nice post about the limitations of sequences in sql server here.

So the question is how can I achieve isolation of quering from sequence in case of MS sqlserver, so my application dont have to be db vendor aware?

TT. TT.
Answer

The NEXT VALUE FOR function in SQL Server has several restrictions. One of these restrictions is that the NEXT VALUE FOR function cannot be used in a View.

From the documentation on NEXT VALUE FOR (Transact-SQL) - Limitation and Restrictions:

The NEXT VALUE FOR function cannot be used in the following situations:

  • In views, in user-defined functions, or in computed columns.

You will have to find another way of doing things. I would suggest creating a Stored Procedure that would return a next value as an OUTPUT parameter.

Comments