FileStream Partitioning Scheme

• Gayathri.Varadarajan

  SSCrazy

  Points: 2080

  More actions

  February 25, 2009 at 6:01 am

  #197455

  Hi,

  I have the following scenario:

  Currently we are having a varbinary(max) column in SQL2005 which we are proposing to change it to a filestream data.The table is partitioned.

  So I created multiple filegroups for the filestreams.I created a new partition scheme for the FileStream.

  --For Data

  CREATE PARTITION SCHEME [fPScheme] AS PARTITION [fAuditPFN] TO ([FG1], [FG2], [FG3])

  --For FileStream

  CREATE PARTITION SCHEME [fFSPScheme] AS PARTITION [fAuditPFN] TO (FileStreamGroup1, FileStreamGroup2,FileStreamGroup3)

  Now when I try to create the table,

  1 CREATE TABLE [dbo].dummy(

  2 [ID] [bigint] IDENTITY(1,1) NOT NULL,

  3 DUMMYROWGUIDCOL UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL DEFAULT NEWID() UNIQUE (DUMMYROWGUIDCOL,partkey) on [fPScheme]([PartKey]) ,

  4 [TransactionID] [bigint] NOT NULL,

  5 [AdditionalText] varbinary(max) filestream NULL,

  6 [CreatedAt] [datetime] NOT NULL,

  7 [PartKey] [int] NOT NULL

  8

  9 ) ON [fPScheme]([PartKey])

  10 FILESTREAM_ON [fFSPScheme];

  11 GO

  I get the following error :

  A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column.

  inspite of me creating the rowguidcol as not null and defining unique constraint on this.

  Please suggest to proceed.

• jdarsley

  Mr or Mrs. 500

  Points: 541

  More actions

  March 3, 2009 at 4:58 am

  #952333

  I'm guessing the problem is that your ROWGUIDCOL isn't actually defined as unique in its own right. Your UNIQUE clause incorporates the PartKey column as well.

  Try removing PartKey from the UNIQUE clause.

  Cheers

  John

• jacob sebastian

  SSChampion

  Points: 11822

  More actions

  January 24, 2010 at 8:29 pm

  #1107986

  I agree with John. The UNIQUEIDENTIFIER column should be unique by itself.

  .