0

I'm implementing the file stream in my database for the first time and I need to write a script that is going to change the file-group of file stream in a specific table.

I was trying to work with the solutions that I found on the internet like creating a new cluster index and dropping existing but it is not working with the file-stream filegroup.

GO
Create Unique Clustered index [UQ__TblPhoto__3214EC260876E955] ON [TblPhotos] (ID asc)  WITH (DROP_EXISTING=ON) ON [FileGroup-2018]

This is the code I tried.

Endrit Sheholli
  • 101
  • 1
  • 3
  • 19
  • To change the filestream filegroup, use the `FILESTREAM_ON` clause (`(WITH DROP_EXISTING = ON) FILESTREAM_ON `). The regular `ON` clause is for the non-filestream data. This assumes you have another filestream filegroup you want to move the data to; changing the location of an existing filegroup is a completely different exercise. – Jeroen Mostert Jun 11 '19 at 13:46
  • I actually don't want to move the data, I just need to change the filegroup so that the app saves files in a diferent location in the future – Endrit Sheholli Jun 12 '19 at 08:27
  • 1
    You can't do that easily, because SQL Server doesn't store the filegroup per row. Using multiple filegroups is only possible by partitioning the table, with the new partition residing on a different filegroup. Adding this retroactively to a table is relatively complicated. Alternatively, you could "manually partition" the table by creating a new table on a new filegroup, renaming the old table, and offering a view in place of the old one that combines both tables with a `UNION ALL`. You may need an `INSTEAD OF INSERT` trigger on that view to get inserts working. – Jeroen Mostert Jun 12 '19 at 08:32

0 Answers0