Friday, September 14, 2012 4:38 PM
We have an image column which store the PDFs, it is taking a lot of space, so my question is, is it possible to move only the Image column to Secondary Filegroup without moving the entire table.
Currently the Secondary file group is in different drive.
i thought that if we can create a nonclustered index on the image column and store it on the secondary group.
We are using SQL Server 2008.
Friday, September 14, 2012 5:21 PM
First, I'll switch to VARBINARY(MAX) since IMAGE has been deprecated or maybe use a FILESTREAM VARBINARY(max) column, in-which case the pdf file is stored outside the database and can be placed anywhere. VARBINARY functions and most feature (outside of encrytion) still work with this column type including the FILESTREAM data being backed up with the structured data in the database.
As a side note, only the table can be placed on the filegroup, not a column, and to move the existing data you will need to rebuild the clustered index.
Also, normal indexes can only be 900 bytes in size, plus I really don't think in the case of pdf files it would do you much good any way. If you want to search/index the data, you need to looked at using the full-text search engine in SQL server. It is designed to do weighted key word searches on documents like pdf and other text based files.
William F. Kinsley