Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLAndy

I'm Andy Warren, currently a SQL Server trainer with End to End Training. Over the past few years I've been a developer, DBA, and IT Director. I was one of the original founders of SQLServerCentral.com and helped grow that community from zero to about 300k members before deciding to move on to other ventures.

Partitioning Strangeness

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.

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.