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

Question

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? 

 

Answer 1

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.

Answer 2

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

 

https://www.connectionstrings.com/sql-server-data-types-reference/

 

Answer 3

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