INTERVIEW QUESTIONS DATABASE MS SQL SERVER DETAILS

Question: How Extra Digits Are Handled with NUMERIC Data Type Literals?

Answer: Exact numeric data types defined with NUMERIC(p,s) has two limits defined by two parameters: p (precision) and s (scale):

* Maximum number of digits of the integer part (digits before the decimal point) is defined as p-s. If this limit is passed, SQL Server will give you an arithmetic overflow error.
* Maximum number of digits of the decimal part (digits after the decimal point) is defined as s. If this limit is passed, SQL Server will perform a round operation.

The tutorial exercise below gives an example of arithmetic overflow errors and rounding operations.

-- Exact numeric value
DECLARE @x NUMERIC(9,2); -- NUMERIC(p,s)
SET @x = 1234567.12;
SELECT @x;
GO
1234567.12

-- Overflow error: p-s limit passed
DECLARE @x NUMERIC(9,2); -- NUMERIC(p,s)
SET @x = 123456789.12;
GO
Msg 8115, Level 16, State 8, Line 2
Arithmetic overflow error converting numeric to data type
numeric.

-- Rounding on extra decimal digits: s limit passed
DECLARE @x NUMERIC(9,2); -- NUMERIC(p,s)
SET @x = 1234567.12345;
SELECT @x;
GO
1234567.12

Exact numeric data types defined with NUMERIC(p,s) has two limits defined by two parameters: p (precision) and s (scale):

* Maximum number of digits of the integer part (digits before the decimal point) is defined as p-s. If this limit is passed, SQL Server will give you an arithmetic overflow error.
* Maximum number of digits of the decimal part (digits after the decimal point) is defined as s. If this limit is passed, SQL Server will perform a round operation.

The tutorial exercise below gives an example of arithmetic overflow errors and rounding operations.

-- Exact numeric value
DECLARE @x NUMERIC(9,2); -- NUMERIC(p,s)
SET @x = 1234567.12;
SELECT @x;
GO
1234567.12

-- Overflow error: p-s limit passed
DECLARE @x NUMERIC(9,2); -- NUMERIC(p,s)
SET @x = 123456789.12;
GO
Msg 8115, Level 16, State 8, Line 2
Arithmetic overflow error converting numeric to data type
numeric.

-- Rounding on extra decimal digits: s limit passed
DECLARE @x NUMERIC(9,2); -- NUMERIC(p,s)
SET @x = 1234567.12345;
SELECT @x;
GO
1234567.12 Source: CoolInterview.com

If you have the better answer, then send it to us. We will display your answer after the approval.

Rules to Post Answers in CoolInterview.com:-

• There should not be any Spelling Mistakes.
• There should not be any Gramatical Errors.
• Answers must not contain any bad words.
• Answers should not be the repeat of same answer, already approved.
• Answer should be complete in itself.

Name :*
Email Id :*
Answer :*
Verification Code	Not readable? Load New Code
Enter the above shown code: *
	Inform me about updated answers to this question