SQL Server Question | Have you ever gave length in the Float data type declaration? | SQL Interview Question

Well yes we can provide the length while declaring a Float data type, al though it is not compulsory. If you do not specify the length then SQL Server by default take that as FLOAT(53).

Syntax

--

float [ (n) ]

--

n – Where n is the number of bits that are used to store the mantissa of the float number in scientific notation
and, therefore, dictates the precision and storage size. If n is specified, it must be a value between 1 and 53.

Notes –

1. If you specify any value between 1-24 than SQL Server treats n as 24.
2. If you specify any value between 25-53 than SQL Server treats n as 53.
3. If you don’t specify any value than SQL Server treats n as 53.
4. If you specify n then it must be a value between 1 and 53.

N & the Storage Bytes

n value Precision Storage size
1-24 7 digits 4 bytes
25-53 15 digits 8 bytes

Range

Data type Range
float – 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308

Note

Conversion of float values that use scientific notation to decimal or numeric is restricted to values of precision 17 digits only. Any value < 5E-18 rounds down to 0.

Sample Example – 1 | Float without N

--

CREATE TABLE TestFloat
(
	 Id INT
	,Vals FLOAT
)
GO

OUTPUT
--
Commands completed successfully.

--

Here we have not specified any value so Prec should be 53

Sample Example – 2 | Float with N between 1 – 24

--

CREATE TABLE TestFloat1
(
	 Id INT
	,Vals FLOAT(10)
)
GO


OUTPUT
--
Commands completed successfully.


--

Here we have specified n value between 1-24 so Prec will be 24.

Sample Example – 3 | Float with N between 25-53

--

CREATE TABLE TestFloat2
(
	 Id INT
	,Vals FLOAT(49)
)
GO

OUTPUT
--
Commands completed successfully.


--

Here we have specified n value between 25-53 so Prec will be 53.

Sample Example – 4 | Float with N > 53

--

CREATE TABLE TestFloat3
(
	 Id INT
	,Vals FLOAT(54)
)
GO

--

In this case we shall get the below Error Message.

Msg 2750, Level 16, State 1, Line 34 Column or parameter #2: Specified column precision 54 is greater than the maximum precision of 53.

You can read more from below.
https://docs.microsoft.com/en-us/sql/t-sql/data-types/float-and-real-transact-sql?view=sql-server-2017

Related Puzzle-
https://msbiskills.com/2018/07/19/sql-puzzle-remove-trailing-zeros-puzzle-the-double-precision-float53-data-type-advanced-sql/

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Performance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com

Advertisements