Sql-server – Why is the output of this query being returned like this

sql serversql-server-2008t-sql

I have a T-SQL query :

select 
    CAST(0 as float(53)),
    CONVERT(nvarchar(max),CAST(0 as float(53)),128)

the output of which is

0  ,  0.0E0

Can some one explain why it is not 0 , 0? The database is SQL Server 2008.

Best Answer

Syntax for CONVERT:

 CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

[style] for float data type :

0 (default)   A maximum of 6 digits. Use in scientific notation, when appropriate.
1 Always 8 digits. Always use in scientific notation.
2 Always 16 digits. Always use in scientific notation.
126, 128, 129 Included for legacy reasons and might be deprecated in a future release.

You can use below command to get expected result :

CONVERT(nvarchar(max),CAST(0 as float(53)),0)

Related Solutions

Sql-server – Running query against SQL Server with FreeTDS’s bsqldb returns blank output

I suspect the one of the values is NULL, so the whole expression concatenation is NULL.

You'd need this:

CAST(ISNULL(PageGuid, '') as varchar(max)) + ',' + 
CAST(ISNULL(TenantGuid, '') as varchar(max)) + '/' +
CAST(ISNULL(OriginalFileGuid, '') as varchar(max))

Sql-server – Why does ‘sysfiles1’ show up in the sys.objects catalog view in SQL Server 2008

sysfiles1 is an internal catalog table. There are many of them (basically any table with object_id between 1 and 99 is, by definition, an internal catalog table). The query compiler refuses to bind queries to them under normal context, so if you try to select from them you get the error you see. DAC connections can bind to them and retrieve data, but you still won't be able to update them.

This is all described on MSDN, see System Base Tables.

sysfiles1 though is a special case, not documented in the link above. ~~This catalog table exists only in databases upgraded from SQL 2000~~. See http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/09/19/761437.aspx:

... sysfiles1 table is a linked-heap. This table contains the locations of the files comprising the database ...

Best Answer

Related Solutions

Sql-server – Running query against SQL Server with FreeTDS’s bsqldb returns blank output

Sql-server – Why does ‘sysfiles1’ show up in the sys.objects catalog view in SQL Server 2008

Related Question