Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
can we create a nonclustered index on IMAGE datatype column

Answered can we create a nonclustered index on IMAGE datatype column

  • Friday, September 14, 2012 4:38 PM
     
     
    Sign In to Vote
    0

    hi

    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.

    please suggest.

    Thanks

     

All Replies

  • Friday, September 14, 2012 5:21 PM
     
     Answered
    Sign In to Vote
    1

    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.

    Bill

    William F. Kinsley

    • Proposed As Answer by Prashant Jha Saturday, September 15, 2012 4:47 AM
    • Marked As Answer by Kushal_sg Monday, September 17, 2012 8:13 AM
    •