Avatar of Mike Eghtebas
Mike Eghtebas
Flag 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
Microsoft SQL ServerMicrosoft SQL Server 2008Microsoft SQL Server 2005

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon
Ryan Chong

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
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.
Ryan Chong

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 started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
SOLUTION
Ryan Chong

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Vitor Montalvão

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.
Mike Eghtebas

ASKER
Could you give me a TSQL code to generate decimal data type identity column?
SOLUTION
Vitor Montalvão

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.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Scott Pletcher

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.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.