Create Partitioned Temp Table Hit Error "Msg 1921, Level 16, State 1, Line %n Invalid partition scheme '%s' specified.

Question

Hi all,

I tried to create a partitioned table to help speed up some queries that need large range scan (so index is not helpful). There is performance improvement and the such tables can be discarded after short term use. However, the customer has strict policy in creating permanent tables. So I intend to create partitioned temp table instead, and hit the captioned error. The SQLs I executed are:

CREATE PARTITION FUNCTION part_fn (int)
AS RANGE RIGHT FOR VALUES (0,1,2,3);

CREATE PARTITION SCHEME part_schm
AS PARTITION part_fn
ALL TO ('PRIMARY');

CREATE TABLE perm_tbl (f1 int, f2 char(8), f3 numeric(20,0))
on part_schm(f1);
-- The above completes without error

CREATE TABLE #temp_tbl (f1 int, f2 char(8), f3 numeric(20,0))
on part_schm(f1);
/*
Msg 1921, Level 16, State 1, Line NN
Invalid partition scheme 'part_schm' specified.
(This error occurs for both IaaS and PaaS. My situation is SQL database.)
*/

I Googled and could not find information addressing the above situation. One link (https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e8c1c08e-1211-443e-9c66-1765986787d6/is-it-possible-to-create-partition-on-temp-table-in-sql-server?forum=sqldataaccess) mentions that this is not a proper use case. As partitioned temp table is useful in my case, I would like to seek advice on how to accomplish this.

Thanks and best regards

Answer 1

It works perfectly for me on a SQL Server Developer Edition but you forgot the # on the table name. You need to write #perm_tbl instead of perm_tbl with out the # sign.

CREATE PARTITION FUNCTION part_fn (int)  
AS RANGE RIGHT FOR VALUES (0,1,2,3);  
GO  
CREATE PARTITION SCHEME part_schm  
AS PARTITION part_fn  
ALL TO ('PRIMARY');  
GO  
CREATE TABLE #perm_tbl (f1 int, f2 char(8), f3 numeric(20,0))  
on part_schm(f1);  

The above worked for me.

However, please consider using In-Memory OLTP in the scenario you described.

Answer 2

Hi Alberto,

Thanks for your info. My test case did include the # sign for the temp table (re-capped from initial post to below):

...
CREATE TABLE perm_tbl (f1 int, f2 char(8), f3 numeric(20,0))
on part_schm(f1);
-- The above completes without error

CREATE TABLE #temp_tbl (f1 int, f2 char(8), f3 numeric(20,0))
on part_schm(f1);
/*
Msg 1921, Level 16, State 1, Line NN
Invalid partition scheme 'part_schm' specified.
(This error occurs for both IaaS and PaaS. My situation is SQL database.)
*/

It is good to hear that it can run on your platform but this is not the case of my project. As mentioned in the original post, it fails for both IaaS & PaaS deployment models. The use of In-Memory OLTP is also not applicable due to the usage scenario I mentioned earlier.

However, your finding still gives me some hope that this may be supported but fails due to some mis-configuration only.

Thanks and best regards