0

I have a couple of tables in a database which I inherited which have NVARCHAR(MAX) columns. Now that the data is several million rows, adding an index takes a long time and because of those columns, I can't do it with the ONLINE option. Meaning - I require an outage.

So I want to shift those columns to other tables and leave the columns which are frequently accessed. This will mean a staged process which will end in dropping the NVARCHAR(MAX) columns from the original tables.

The question I have is this, will that just magically enable those columns to be indexed ONLINE? They were created with the TEXTIMAGE_ON clause. Is there anything I need to do to address that? Or do I have to completely recreate the table and re-populate it? (really bad outcome)

Thanks

Improve this question
8
  • Have you used sp_tableoption? Commented Oct 21 at 8:18
  • 6
    I think you need to provide table definition and index you are trying to create. There are restrictions for creating indexes online, but I don't see how NVARCHAR(MAX) restricts you in any obvious way. Commented Oct 21 at 11:16
  • 1
    "This will mean a staged process which will end in dropping the NVARCHAR(MAX) columns from the original tables." - FWIW, it would probably be more efficient to leave those heavy columns in the original table, and move the other lighter columns to a new table, and then use sp_rename to just rename the tables accordingly, or even better, use a view with the original table name, that joins the two tables back together, as your layer of abstraction. Commented Oct 21 at 21:29
  • Thanks @J.D. that makes sense. It achieves the goal of being able to add indexes ONLINE going forward. The last outage was too long. Commented Oct 22 at 1:37
  • 1
    SQLFiddle says otherwise. So you need to provide more info like DDL for the table and index, SQL Server version, errors you are getting. Commented Oct 22 at 7:21

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.