Is it SQL92 compliant to store the number 1 in a field that is defined as
Does this mean that the precision is a floating decimal place or static that must have 3 decimal places even if they are all zero?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
The precision is the total number of decimal digits that can be stored on both sides of the decimal place. The scale is the total number of decimal digits to the right of the decimal point which can be less than or equal to the precision. Therefore the maximum value that can be stored in a DECIMAL(3,3) or a NUMERIC(3,3) is 0.999. The easiest way to find this out is to actually try it out.
Using mysql 5.5.20 the below returns 0.999:
SET @val = (SELECT CAST(1 AS DECIMAL(3,3))); SELECT @val;
Whereas the following returns 1:
SET @val = (SELECT CAST(1 AS DECIMAL(3,0))); SELECT @val;
I hope this helps.
EDIT: I forgot to mention that on most platforms attempting to perform the above will result in some type of Arithmetic Overflow Exception. For instance the below statement running on Microsoft SQL Server 2008 will throw an error:
DECLARE @val DECIMAL(3,3); SET @val = 1; SELECT @val;