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?

Nilesh Shah · Answer

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.

Tapan Patel · Answer

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 R · Answer

I am working in inventory applicaion.so i will tell about my suggestion Qty: is only allow number. So you can set NUMERIC(180) data type Unit Cost: is getting decimal value like 10.1000. So you can set NUMERIC(184) 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