Insane Perspiration

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!

SQL Express is the free, low-powered MySQL-competing version of SQL Server. It’s primarily neutered when it comes to hardware utilization (it will use at most 1 CPU and 1GB memory) and maximum database size (4GB excluding log files), but for quite a large number of personal or workgroup-level applications, as well as test and development machines, that’s quite enough. As opposed to for instance MySQL, it has the full TSQL language implemented, so you can make reasonably advanced systems based on it. Did I mention that it’s free?

Anyway, the default installation of SQL Express gives you an install that borders on complete unuseable, giving you the impression that this is only a toy, move along, install that full version from your MSDN disks (or The Pirate Bay). But it can, in fact, be made to work resonably.

The default installation gives you two main problems: It doesn’t install Management Studio, so you’re left to managing the database inside of Visual Studio, which of course is a horrible experience. Secondly, it installs the server as a named instance (which there is no reason for at all on a computer with only one instance), creating all kinds of strange problems with programs not expecting this. The installation process has an alternative for “default instance”, but this doesn’t in fact create a normal default instance! Someone at Microsoft is probably laughing himself to sleep over how he could get this practical joke into the product…

Anyway, to fix it, start by uninstalling. There is no way to alter the situation after installation. Then make sure you have a download of Microsoft® SQL Server® 2008 Express with Tools (don’t miss the “with Tools” part). Start the install, and select all features, especially “Management Tools – Basic”. If you don’t have Management Tools in your feature list, you didn’t listen to me when I told you not to miss the “with Tools” part. Go back and redownload.

When you come to the Instance Configuration screen, don’t leave the default Named instance at SQLExpress. Don’t, as you might be prone to believe, choose Default instance either. Create a Named instance with the instance name “MSSQLSERVER” and Instance ID “MSSQLSERVER”.

On Server Configuration, choose what user to run the service under (like SYSTEM or NETWORK SERVICE, or maybe your own user if you’re on a development machine), and make sure you’ve got the collation you want. On Database Engine Configuration, I recommend setting up Mixed Mode Authentication, since this gives you the most alternatives to get all your programs connecting to the server in some way. Make sure you add yourself as a server administrator, if you’re going to use windows authentication. You may also want to move the data directories to somewhere useful, by default they reside under C:\Program Files\Microsoft SQL Server\yadda\yadda\yadda. Personally, I allways put them in C:\MSSQL (or on some other drive than C if such a drive exists on the machine) and shorten the paths from the useless MSSQL.10.MSSQLSERVER\MSSQL crap, so that data files for example lie in C:\MSSQL\Data. Nice and simple.

After installation, you’ll likely want to go into SQL Server Configuration Manager and enable TCP/IP and maybe Named Pipes under SQL Server Network Configuration. As for TCP/IP, you need to enable it on the Protocol tab, and then Enable it again under IP Addresses. And of course, if you want to be able to reach the server from outside of the machine it’s installed on, you need to tweak your firewall. Make sure SQL Server, Server Browser and Server Agent services are set to Automatic startup, and restart them (for the protocol changes to kick in).

That’s all there is to it. Now you have a database that can do most everything you need, except use the profiler (yes, that sucks, but I guess MS has to have something left to push people into buying Workgroup Edition).