The crucial point with the conversion error can be demonstrated by trying these statements

SELECT 1 UNION SELECT 'A'

and

SELECT 'A' UNION SELECT 1

In both cases, you'll get

Syntax error converting...

Are you sure Ashok? I ran the query with SQL 2005 and got the conversion error.

One thing to check is that it's the version of the server software that matters...

Thank you for that link.

I may not register because their Privacy Policy seems to suggest 'you have no privacy from our advertisers'.

However, their existing material should be a useful...

Poor (non-existent?) QA on this question. Unexpected from this site.

And what about the role of sales_category? The results of the query will also reflect the 'performance' of the sales_category, whatever...

From a programmer's point of view, a superficial look at your code suggests that you're asking SQL Server to do lots of function calls for each of your million rows....

Here is the alternative coding of the function dbo.fnBinaryReal2Real

CREATE FUNCTION dbo.fnBinaryReal2Real

(

@BinaryFloat BINARY(4)

)

RETURNS REAL

AS

BEGIN

DECLARE@Mantissa REAL,

@Exponent...

I've recoded Peso's function to provide improved performance. Using the benchmark posted by Jeff Moden (4/10/2007) run-time changed from 1.0 seconds to 0.16 seconds.

[font="Courier New"]

CREATE FUNCTION dbo.fnBinaryFloat2Float

(

...

Well done, Jeff Moden and Peso. The function fnBinaryFloat2Float was just what I needed.

Well, actually, I wanted a function to convert BINARY(4) to REAL. But by changing some of the...