Monthly Archives: January 2009

Using OPTION (FORCE ORDER) with user definied functions in SQL Server 2005

Posted on by

Really interesting thing I realised today: Using OPTION (FORCE ORDER) within a UDF, at least in SQL Server 2005 (i haven’t tested in other versions), doesn’t work if you are within the RETURN clause. It does, however, if you aren’t.

For example:

CREATE FUNCTION SchemaName.udfName ()
RETURNS VARCHAR(50)
AS
BEGIN
    RETURN
    (   
        SELECT  t1.Field
        FROM    SchemaName.Table1 t1
        INNER JOIN SchemaName.Table2 t2
            ON t1.Field1=t2.Field2
        INNER JOIN SchemaName.Table3 t3
            ON t2.Field1=t3.Field2
        OPTION (FORCE ORDER)
    )
END

will just return

Msg 156, Level 15, State 1, Procedure udfName, Line 12
Incorrect syntax near the keyword ‘OPTION’.

however,

CREATE FUNCTION SchemaName.udfName ()
RETURNS VARCHAR(50)
AS
BEGIN

    DECLARE @ret VARCHAR(50)
    SELECT  @ret=t1.Field
    FROM    SchemaName.Table1 t1
    INNER JOIN SchemaName.Table2 t2
        ON t1.Field1=t2.Field2
    INNER JOIN SchemaName.Table3 t3
        ON t2.Field1=t3.Field2
    OPTION (FORCE ORDER)
    RETURN
    (   
        @ret
    )
END

will work just fine!

The Phantom Menace

Posted on by

How irritating. When I came home yesterday, my main workstation was simply dead. It appears that the power supply has just given up completely. For the last 14 months it has been running 24×7 without a single hiccup, but now its just ready for the trash bin. Tragic.

Since a good power supply is one of the most important components of a truly stable workstation, I was running an Antec Phantom passively cooled power supply, that cost more than certain netbooks, so I wasn’t really happy at seeing it die unexpectedly.

The funny thing is, after searching the web, it turns out to be not uncommon for a Phantom to die completely after about a year and a half. I guess mine lost a few extra months based on it always being on (and I mean always…). I wish I knew that when I was assembling the workstation…

So on Monday it’s time to go power supply-shopping. I think I’ll go with a traditional big ol’ fan this time. I’m looking at a Zalman ZM-600 HP Silent, or maybe a Corsair VX550 or TX650.

Anyone have any recommendations? Particularly, will the Corsair VX or TX line give me the lowest volume/performance? I don’t have time to scour the internet and read all the reviews this time, I just have to get something fast…