June 12, 2015 at 10:02 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.
April 10, 2019 at 12:54 pm
Just stumbled over this old question.
A possible anwere would be: try to disable the index instead of dropping it. After the switch you have to rebuild it (may take very long, so it may not be the best solution).
God is real, unless declared integer.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply