Link to home
Start Free TrialLog in
Avatar of Mike Eghtebas
Mike EghtebasFlag for United States of America

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:
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);

Open in new window


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
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

so, it means a decimal primary key must be a decimal with a scale of 0, hence you need to try:

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
Avatar of Mike Eghtebas

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.
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.
SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Could you give me a TSQL code to generate decimal data type identity column?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial