Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Analyzer
Posting Yak Master
115 Posts |
 Posted - 2010-02-15 : 12:17:44
|
Need to REBUILD a composite PK index combining an INT/VARCHAR(85) columns.However SQL Server says the operation can only be run OFFLINE due to a ConfigurableField(NTEXT) col, all NULLs. A 24/7 operation so OFFLINE is not an option. I'm told I cannot drop the ConfigurableField(NTEXT) col which has no relationship with the PF index so far as I can see.Any ideas of a workaround |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-15 : 12:30:34
|
| I'm not understanding something.What's the relationship between VARCHAR(85) column and the ConfigurableField NTEXT column? I assume that "ConfigurableField" is that the name of an NTEXT column? |
 |
|
|
Analyzer
Posting Yak Master
115 Posts |
Posted - 2010-02-15 : 12:49:40
|
| Yup. No direct relationship with the PK index and the ConfigurableField NTEXT column which SQL complains about.Is the problem by virtue of the clustered indexed has to reorganise all the columns? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-15 : 13:09:20
|
| Not that I can think of.Can you post DDL for the table (e.g. SQL for its Create Table statement) |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-02-15 : 13:23:30
|
| A clustered index, by definition, consists of all columns in the table. Hence, if there's an NTEXT column in a table, the clustered index on that table cannot be rebuilt online. No workaround.Rebuild offline or reorganise (which is always an online operation)--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-15 : 14:00:43
|
| Ah, I mis-read. I thought that task was to CHANGE the Clustered Index (to different keys)Gail: If VARCHAR(MAX) used instead of TEXT would that suffer the same problem? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-02-16 : 00:00:38
|
| Yes. A look at the appropriate page of BoL shows that...REBUILD WITH ONLINE = ON fails if the table has one or moreXML index Spatial indexLarge object data type columns: image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-16 : 01:49:51
|
| Interesting. Perhaps makes a case for putting them in a 1:1 "parallel" table? |
|
|
|
|
|
|
|
|
Cannot REBUILD IDX ONLINE due to NTEXT col