Decimal as identity

Mike Eghtebas
Mike Eghtebas used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
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 EghtebasDatabase and Application Developer

Author

Commented:
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 ChongSoftware Team Lead

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Ryan ChongSoftware Team Lead
Commented:
perhaps you can reading this:

IDENTITY (Function) (Transact-SQL)
https://msdn.microsoft.com/en-sg/library/ms189838.aspx

in this article, it mentioned that:

seed
Is the integer value to be assigned to the first row in the table. Each subsequent row is assigned the next identity value, which is equal to the last IDENTITY value plus the increment value. If neither seed nor increment is specified, both default to 1.

increment
Is the integer value to add to the seed value for successive rows in the table.

hence, passing an increment smaller than 1 will not going to work
>>IDENTITY(1, 0.02)
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
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 EghtebasDatabase and Application Developer

Author

Commented:
Could you give me a TSQL code to generate decimal data type identity column?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
I don't think you can do it directly since the increment need to be an integer value.
You can try to full SQL Server and in your example increment by 2 and then when you want to show the value, divide it by 100.
Example:
#1: 100.00 (value stored) --> SELECT (orderid/100) --> returns 1.00
#2: 102.00 (value stored) --> SELECT (orderid/100) --> returns 1.02
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
CREATE TABLE MyOrders
(
orderid_base decimal(14,0) NOT NULL IDENTITY(0, 2),
orderid AS orderid_base / 100.0 PERSISTED
 CONSTRAINT PK_MyOrders_orderid PRIMARY KEY
, custid INT NOT NULL
, empid INT NOT NULL
, orderdate DATE NOT NULL);

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial