asked on Decimal as identity
I will stay with int, bigint, etc. and will not use decimal. This question is about using decimal if need be. I am just investigating this.
Question: How can I correct the following:
Error:
Msg 2749, Level 16, State 2, Line 27
Identity column 'orderid' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, and constrained to be nonnullable.
I am running it in SQL Server 2014. Is it deprecated in this version of there is something wrong with the following line:
orderid decimal(10,4) NOT NULL IDENTITY(0, 0.02)
CONSTRAINT PK_MyOrders_orderid PRIMARY KEY
Question: How can I correct the following:
CREATE TABLE MyOrders
(
orderid decimal(10,4) NOT NULL IDENTITY(0, 0.02)
CONSTRAINT PK_MyOrders_orderid PRIMARY KEY
, custid INT NOT NULL
, empid INT NOT NULL
, orderdate DATE NOT NULL);
Error:
Msg 2749, Level 16, State 2, Line 27
Identity column 'orderid' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, and constrained to be nonnullable.
I am running it in SQL Server 2014. Is it deprecated in this version of there is something wrong with the following line:
orderid decimal(10,4) NOT NULL IDENTITY(0, 0.02)
CONSTRAINT PK_MyOrders_orderid PRIMARY KEY
Microsoft SQL ServerMicrosoft SQL Server 2008Microsoft SQL Server 2005
Last Comment
Scott Pletcher
ASKER
Having decimal(10,4) is a must. Otherwise it is just like int or bigint. I want to have
orderid
1.001
1.002
etc.
orderid
1.001
1.002
etc.
do you think possible to use a varchar instead? it seems that MS SQL just don't allow a primary key in a zero scaled decimal data type.
i try to find a documentation to support this argument.
i try to find a documentation to support this argument.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Identity column 'orderid' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, and constrained to be nonnullable.I think this error message is very clear. If you want to use a non integer numeric data type then the scale need to be 0. Isn't a bug but how the things really works and I don't think you can do it in another way.
ASKER
Could you give me a TSQL code to generate decimal data type identity column?
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
CREATE TABLE MyOrders
(
orderid decimal(10,0) NOT NULL IDENTITY(1,1)
CONSTRAINT PK_MyOrders_orderid PRIMARY KEY
, custid INT NOT NULL
, empid INT NOT NULL
, orderdate DATE NOT NULL);
or use an int, bigint, smallint, tinyint type instead, like:
orderid Int NOT NULL IDENTITY(1,1)
orderid bigint NOT NULL IDENTITY(1,1)
...
etc