Post reply

Create a column in different filegroup

  • May 4, 2010 at 11:56 am

    #221162

    Hi Experts,

    I have a table with 10 columns, one of the columns has TEXT datatype. Is it anyway possible to create that particular column in a different filegroup within that table? OR I have to put that column in separate table only and put that table in different filegroup?

    Thanks...

  • May 4, 2010 at 12:25 pm

    #1162030

    I don't think the option is available in the SSMS table designer, but look up CREATE TABLE in SQL Server Books Online, and see the option { TEXTIMAGE_ON { filegroup | "default" }.

    Also, since SQL Server 2005, Microsoft has reccomended using varchar(MAX) instead of the TEXT datatype, and it will eventually be dropped from future releases.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • May 4, 2010 at 1:06 pm

    #1162048

    Thanks Eric...

    I created a database with two file groups, primary(default) and secondary and then creating following table.

    CREATE TABLE [dbo].[TestTable]( [ID] [int] NOT NULL,

    [Line] [smallint] NOT NULL,

    [Name] TEXT )

    ON [PRIMARY] TEXTIMAGE_ON [secondary]

    So, now when I add data to this table, TEXT data will automatically go to secondary filegroup right? I tried doing this by inserting some big data but if I check the sp_helpfile, actually primary file size is going up and secondary remains the same. Any suggestion?

  • May 4, 2010 at 1:10 pm

    #1162049

    Execute a "checkpoint", and then again confirm if the secondary file has increased in size.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • May 4, 2010 at 2:04 pm

    #1162087

    Yes it works... thanks Eric...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply