January 25, 2017 at 4:28 am
I have a large filestream table and i want to partition that table on multiple drives, for this purpose i created partition function , partition scheme and now i want to move existing table to new partition scheme
when i try to drop primary key constraint from the table it gives the error "A table that has FILESTREAM columns must have a nonnull unique column with the ROWGUIDCOL property"
below is the code to reproduce the error
any help will be appreciated.
CREATE DATABASE [test]
ON PRIMARY
( NAME = N'test', FILENAME = N'H:\test\test.mdf' ),
FILEGROUP [FSG] CONTAINS FILESTREAM DEFAULT
( NAME = N'FS', FILENAME = N'H:\test\Images' , MAXSIZE = UNLIMITED)
LOG ON
( NAME = N'test_log', FILENAME = N'H:\test\test_log.ldf' )
GO
use test
CREATE TABLE [dbo].[Images](
[imageid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[imagefile] [varbinary](max) FILESTREAM NULL,
CONSTRAINT [PK_Images_imageid] PRIMARY KEY CLUSTERED
(
[imageid] ASC
) ON [PRIMARY] FILESTREAM_ON [FSG]
) ON [PRIMARY] FILESTREAM_ON [FSG]
GO
ALTER TABLE [dbo].[Images] DROP CONSTRAINT [PK_Images_imageid]
Msg 5505, Level 16, State 2, Line 1
A table that has FILESTREAM columns must have a nonnull unique column with the ROWGUIDCOL property.
February 10, 2017 at 7:25 am
hello Zafar,
try to just recreate the index of the primary key, using the option "with ( drop_existing = on )". Use the new filegroup or partition sheme as target for the index.
Have a look here for an example how to move a table between filegroups.
Just get sure you add all options of your current index to not lose any of them. Maybe you should script your Index.
The topic, how to move table between filegroups, is e. g. discussed here
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply