September 13, 2019 at 3:45 am
Problem with PARTITION SWITCH
I'm having a problem creating a partitioned table with a filestream column. I'm getting error:
Cannot create table 'MyTable' since a partition scheme is not specified for FILESTREAM data
My table is as follows. Any help is greatly appreciated.
CREATE TABLE MyTable
(
IndexID BIGINT IDENTITY(1,1),
PartitionID SMALLINT,
IndexGUID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL
CONSTRAINT DF_MyTable_IndexGUID DEFAULT (NEWID())
CONSTRAINT UQNP_MyTable_IndexGUID UNIQUE NONCLUSTERED,
Data_STREAM VARBINARY(MAX) FILESTREAM,
CONSTRAINT PK_Mytable PRIMARY KEY CLUSTERED
(
PartitionID,
IndexID
) ON PS_Partition( PartitionID ) FILESTREAM_ON [fg_filestream]
) ON PS_Partition( PartitionID ) FILESTREAM_ON [fg_filestream]
GO
UPDATE---
I actually managed to get the table created. The table below gets created. I had to specifically indicate that the unique constraint is on [PRIMARY] (non-partitioned) and create a partition scheme in the filestram filegroup. However my problem now is with partition switching. I successfully created a non-partitioned staging table identical to the partitioned table, but the switching operation doesn't work.
CREATE TABLE MyTable
(
IndexID BIGINT IDENTITY(1,1),
PartitionID SMALLINT,
IndexGUID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL
CONSTRAINT DF_MyTable_IndexGUID DEFAULT (NEWID())
CONSTRAINT UQNP_MyTable_IndexGUID UNIQUE NONCLUSTERED ON [PRIMARY],
Data_STREAM VARBINARY(MAX) FILESTREAM,
CONSTRAINT PK_Mytable PRIMARY KEY CLUSTERED
(
PartitionID,
IndexID
) ON PS_Partition( PartitionID ) FILESTREAM_ON PS_FilestreamPartition
) ON PS_Partition( PartitionID ) FILESTREAM_ON PS_FilestreamPartition
GO
CREATE TABLE MyTable_STAGE
(
IndexID BIGINT IDENTITY(1,1),
PartitionID SMALLINT,
IndexGUID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL
CONSTRAINT DF_MyTable_STAGE_IndexGUID DEFAULT (NEWID())
CONSTRAINT UQNP_MyTable_STAGE_IndexGUID UNIQUE NONCLUSTERED ON [PRIMARY],
Data_STREAM VARBINARY(MAX) FILESTREAM,
CONSTRAINT PK_Mytable_STAGE PRIMARY KEY CLUSTERED
(
PartitionID,
IndexID
) FILESTREAM_ON fg_filestream
) FILESTREAM_ON fg_filestream
GO
My test is:
insert MyTable ( PartitionID, Data_STREAM )
values ( 1, convert(varbinary(max),10) )
GO
ALTER TABLE MyTable SWITCH PARTITION 1 TO MyTable_STAGE
I get error:
'ALTER TABLE SWITCH' statement failed. The table 'MyTable' is partitioned while index 'UQNP_MyTable_IndexGUID' is not partitioned.
However, I can't drop the non-partitioned unique index on MyTable as it violates the filestream, and would make the table unusable.
September 13, 2019 at 8:53 pm
To be able to use ALTER TABLE PARTITION SWITCH, all indexes on the table must be on the partition scheme.
So change
CONSTRAINT UQNP_MyTable_IndexGUID UNIQUE NONCLUSTERED ON [PRIMARY],
to
CONSTRAINT UQNP_MyTable_IndexGUID UNIQUE NONCLUSTERED ON PS_Partition(PartitionID)
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply