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!
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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
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.
perhaps you can reading this:
IDENTITY (Function) (Transact-SQL)
https://msdn.microsoft.com/en-sg/library/ms189838.aspx
in this article, it mentioned that:
hence, passing an increment smaller than 1 will not going to work
>>IDENTITY(1, 0.02)
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)
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.
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial