CREATE SEQUENCE Error : invalid value for 'INCREMENT BY'

Question

CREATE SEQUENCE dbo.Sequence1
AS decimal
START WITH 1 INCREMENT BY 0.3
MINVALUE 2 MAXVALUE 4
CYCLE;

Error :

An invalid value was specified for argument 'INCREMENT BY' for the given data type.

I want to create a fractional counter. What's wrong? Nope support decimal?

UPD:

When I create

CREATE SEQUENCE dbo.Sequence1
AS float
....

SQL Server Management Studio returns error :

The sequence object 'dbo.Sequence1' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, or any user-defined data type that is based on one of the above integer data types.

That's why I'm trying to create a sequence with the decimal type.

You have several interesting things happening here. First, you defined a decimal but you did not define the scope or precision. That means it will use the default. You should always define the scope and precision. Second is you have start with defined as less than the minvalue. Not really sure what that would do but it doesn't make sense logically. — Sean Lange
– Sean Lange, Commented Jul 2, 2015 at 20:07
Like Gordon says below you can only use decimal with a scale of 0 (no decimal points allowed). Sequences are whole numbers only. — TheMadDBA
– TheMadDBA, Commented Jul 2, 2015 at 20:13
@kellismitt: When your problem can't be solved, redefine the problem. :) In this case, I'd multiply everything through by 10 (increment of 3, minvalue of 20, maxvalue of 40) and divide when you need to actually do anything with it. — Ben Thul
– Ben Thul, Commented Jul 2, 2015 at 20:40

Gordon Linoff · Accepted Answer · 2015-07-02 20:08:18Z

3

This is a bit long for a comment. The documentation is quite specific that create sequence is for integers:

[built_in_integer_type | user-defined_integer_type]

A sequence can be defined as any integer type. The following types are allowed.

(Emphasis is mine.)

Hence, values with decimal points are not allowed.

decimal is only allowed with a scale of 0 (meaning that there are no decimal points).

answered Jul 2, 2015 at 20:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

CREATE SEQUENCE Error : invalid value for 'INCREMENT BY'

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related