CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008
mr.sverrir
mr.sverrir

Reputation: 414

T-SQL data type REAL FLOAT(n) on SQL Server

Is anyone aware of the reasons why the following line of T-SQL code works (under SQL Server 2008)?

SELECT cast(2 as real(10))

I expect the server to complain in the very same way it does when I attempt this:

declare @x real(10)
set @x  = 4.1234567
select @x

and I get:

Msg 2716, Level 16, State 1, Line 16
Column, parameter, or variable #2: Cannot specify a column width on data type real.

I realise that for MS SQL 2008 there is a data type REAL equivalent to FLOAT(24).

I just would like to know why CAST does not complain, i.e., it was possible to write nonsense as REAL(4,2) within CAST (under SQL 2000), it works fine with REAL(10) under SQL 2008 (again in CAST)?

Any suggestions and clarification appreciated. Thank you.

Upvotes: 0

Views: 1459

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239744

It would appear that CAST (and CONVERT) translates type synonyms (such as real) into their underlying type (i.e. float), for which length specifiers are valid. For another example, you can do:

SELECT CONVERT(sysname(10),'abcdefghijkl')

But you cannot declare a variable of type sysname(10), since sysname is a synonym for nvarchar(128) (or varchar(30), depending on SQL version)

In short, I'd say it's a quirk of how CAST/CONVERT work, but shouldn't be relied upon.

Upvotes: 2

Related Questions