Which data type store unit cost price and amount in sqlserv?

ahmed salah

9y5.8k

In sql server 2012 I design database for pharmaceutical company to store data of orders

Inside this database I have Table order inside it have three fields

Quantity

Unit Cost price

Amount( Quantity * Unit Cost price)

Quantity datatype is integer

What data type i can use for Unit Cost price field and Amount field ?

Amount will be big because production Qunatity from 200000 to 1000000 box in order .

Unit cost price from 1 pound to 200 pound .

so that Which data type store unit cost price and amount in sqlserver 2012?

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Tapan Patel9y ago
On top of Nilesh's reply, it is always good to keep below reference handly.

https://www.connectionstrings.com/sql-server-data-types-reference/
Thiruppathi R9y ago
I am working in inventory applicaion.so i will tell about my suggestion

Qty: is only allow number. So you can set NUMERIC(18,0) data type
Unit Cost: is getting decimal value like 10.1000. So you can set NUMERIC(18,4) data type

Note:
In future end users will be asked extern thire Cost value form 10.1000 to 10.100000.that time we have to stuck. So numeric datatype already is there.you can make it as an User Define Datatype.

Note:Don't forget to accept.if you agreed my reply
Nilesh Shah9y ago
If your quantity is going to be less than 2147483647 then use int datatype,
if your quantity is going to be more than that and less than 9223372036854775807, use bigint data type.

if you are sure Unit cost price is going to be only from 1 pound to 200 pound, use smallint datatype.
but dont guess here, ask or clarify with BA. if you need to store fractions either use decimal or money or smallmoney datatype

for amount, use money data type which can store value upto 922337203685477.5807

and I suggest you to use calculated column for amount.