Ran across this in a recent class while covering partitioning. Start by creating a standard partitioning function and the scheme, these intended for demo against the Person.Contact table in Adventureworks:

CREATE PARTITION FUNCTION pfContact (datetime) AS RANGE LEFT FOR VALUES ('1/1/2004');

CREATE PARTITION SCHEME schContact
AS PARTITION pfContact
TO ([primary], [primary]);

Then try creating the new table using the scheme:

CREATE TABLE [#temp](
    [ContactID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [Title] [nvarchar](8) NULL,
    [FirstName] nvarchar(50) NOT NULL,
    [MiddleName] nvarchar(50) NULL,
    [LastName] nvarchar(50) NOT NULL,
    [Suffix] [nvarchar](10) NULL,
    [EmailAddress] [nvarchar](50) NULL,
    [EmailPromotion] [int] NOT NULL ,
    [Phone] nvarchar(20) NULL,
    [PasswordHash] [varchar](40) NOT NULL,
    [PasswordSalt] [varchar](10) NOT NULL,
    [ModifiedDate] [datetime] NOT NULL ,
) ON schContact([ModifiedDate])

That yields an error:

Msg 1921, Level 16, State 1, Line 1
Invalid partition scheme 'schContact' specified.

My first thought was that it was that last extra comma, the one after modified date. But, it turns out that it doesn’t work with temp tables. Sort of. I’m not sure how often I’d want to partition a temp table, but it was another one of those interesting times when a student does something different, things break, and you get a less than helpful error message!

Wasn’t too hard to resolve by just making it a ‘real’ table, but my guess is that it breaks because the function and scheme are in the current db, the table would be in TempDB. If you create the objects all in Tempdb it works fine. I'll try to get something nicer written up and sent to Steve soon.