alter index - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

alter index

Author

Topic

acollins74
Yak Posting Veteran

82 Posts

Posted - 2007-11-05 : 16:26:34

I am getting an error below when trying to use online indexing.
The error returned list a series of datatypes not supported for ONLINE=ON
The problem is the datatype referred in the table is varbinary which is not listed.
Also I created a test table with a varbinary index and all is good in the hood.
Can someone help with the below error.
Thanks in advance.

Database: Adventureworks
Microsoft SQL Server 2005 - 9.00.2047.00 (X64) Apr 14 2006 01:11:53 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

ALTER INDEX ALL
ON Production.[Document] REBUILD
WITH (ONLINE = ON, SORT_IN_TEMPDB = ON, MAXDOP = 2)

Msg 2725, Level 16, State 2, Line 1
Online index operation cannot be performed for index 'PK_Document_DocumentID' because the index contains column 'Document' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. For non-clustered index the column could be an include column of the index, for clustered index it could be any column of the table. In case of drop_existing the column could be part of new or old index. The operation must be performed offline.

Thank you,
Adam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-05 : 16:41:23

Ummm, varbinary is listed in the error as not being allowed for online index operations.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-05 : 16:42:05

It would help if you posted the table and index DDL statements.

CODO ERGO SUM

acollins74
Yak Posting Veteran

82 Posts

Posted - 2007-11-05 : 16:58:01

varbinary(max) is listed but i don't see varbinary.

I can post the ddl but found an example out of the AdventureWorks db thinking most would have access to this.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-05 : 17:12:01

Here's what BOL says:

quote:

Indexes, including indexes on global temp tables, can be rebuilt online with the following exceptions:

Disabled indexes

XML indexes

Indexes on local temp tables

Partitioned indexes

Clustered indexes if the underlying table contains LOB data types

Nonclustered indexes that are defined with LOB data type columns

Please post the DDL of your table so that we can see if it meets the criteria.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

acollins74
Yak Posting Veteran

82 Posts

Posted - 2007-11-05 : 17:16:21

USE [AdventureWorks]
GO
/****** Object: Table [Production].[Document] Script Date: 11/05/2007 17:15:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [Production].[Document](
[DocumentID] [int] IDENTITY(1,1) NOT NULL,
[Title] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FileName] [nvarchar](400) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FileExtension] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Revision] [nchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ChangeNumber] [int] NOT NULL CONSTRAINT [DF_Document_ChangeNumber] DEFAULT ((0)),
[Status] [tinyint] NOT NULL,
[DocumentSummary] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Document] [varbinary](max) NULL,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Document_ModifiedDate] DEFAULT (getdate()),
CONSTRAINT [PK_Document_DocumentID] PRIMARY KEY CLUSTERED
(
[DocumentID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING ON
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key for Document records.' , @level0type=N'SCHEMA',@level0name=N'Production', @level1type=N'TABLE',@level1name=N'Document', @level2type=N'COLUMN',@level2name=N'DocumentID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Title of the document.' , @level0type=N'SCHEMA',@level0name=N'Production', @level1type=N'TABLE',@level1name=N'Document', @level2type=N'COLUMN',@level2name=N'Title'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Directory path and file name of the document' , @level0type=N'SCHEMA',@level0name=N'Production', @level1type=N'TABLE',@level1name=N'Document', @level2type=N'COLUMN',@level2name=N'FileName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'File extension indicating the document type. For example, .doc or .txt.' , @level0type=N'SCHEMA',@level0name=N'Production', @level1type=N'TABLE',@level1name=N'Document', @level2type=N'COLUMN',@level2name=N'FileExtension'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Revision number of the document. ' , @level0type=N'SCHEMA',@level0name=N'Production', @level1type=N'TABLE',@level1name=N'Document', @level2type=N'COLUMN',@level2name=N'Revision'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Engineering change approval number.' , @level0type=N'SCHEMA',@level0name=N'Production', @level1type=N'TABLE',@level1name=N'Document', @level2type=N'COLUMN',@level2name=N'ChangeNumber'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'1 = Pending approval, 2 = Approved, 3 = Obsolete' , @level0type=N'SCHEMA',@level0name=N'Production', @level1type=N'TABLE',@level1name=N'Document', @level2type=N'COLUMN',@level2name=N'Status'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Document abstract.' , @level0type=N'SCHEMA',@level0name=N'Production', @level1type=N'TABLE',@level1name=N'Document', @level2type=N'COLUMN',@level2name=N'DocumentSummary'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Complete document.' , @level0type=N'SCHEMA',@level0name=N'Production', @level1type=N'TABLE',@level1name=N'Document', @level2type=N'COLUMN',@level2name=N'Document'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date and time the record was last updated.' , @level0type=N'SCHEMA',@level0name=N'Production', @level1type=N'TABLE',@level1name=N'Document', @level2type=N'COLUMN',@level2name=N'ModifiedDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Product maintenance documents.' , @level0type=N'SCHEMA',@level0name=N'Production', @level1type=N'TABLE',@level1name=N'Document'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key (clustered) constraint' , @level0type=N'SCHEMA',@level0name=N'Production', @level1type=N'TABLE',@level1name=N'Document', @level2type=N'CONSTRAINT',@level2name=N'PK_Document_DocumentID'
GO
ALTER TABLE [Production].[Document] WITH CHECK ADD CONSTRAINT [CK_Document_Status] CHECK (([Status]>=(1) AND [Status]<=(3)))
GO
ALTER TABLE [Production].[Document] CHECK CONSTRAINT [CK_Document_Status]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Check constraint [Status] BETWEEN (1) AND (3)' , @level0type=N'SCHEMA',@level0name=N'Production', @level1type=N'TABLE',@level1name=N'Document', @level2type=N'CONSTRAINT',@level2name=N'CK_Document_Status'

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-11-05 : 17:22:11

[Document] [varbinary](max) NULL,

That's why....

I'm just curious why you didn't see that from the error message you had. It's written in plain english.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-05 : 17:22:56

You fail this criteria due to the nvarchar(max) and varbinary(max): Clustered indexes if the underlying table contains LOB data types.

Try rebuilding only the indexes that meet the criteria instead of using ALL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

acollins74
Yak Posting Veteran

82 Posts

Posted - 2007-11-05 : 19:02:01

Plain english. Huh, better take that off my resume.

Lesson learned by looking at information_schema.columns
for the datatypes. Unfortunately I was only looking at the column DATATYPE in my script.

BOL
CHARACTER_MAXIMUM_LENGTH
int
Maximum length, in characters, for binary data, character data, or text and image data.

-1 for xml and large-value type data. Otherwise, NULL is returned. For more information, see Data Types (Transact-SQL).

Haven't fully tested yet but it appears that it is safe to conclude the following statement will return datatypes.

select table_schema,table_name from information_schema.tables
where table_type = 'base table'
and table_name not in
(select table_name from information_schema.columns
where data_type in ('image', 'text', 'ntext', 'varchar', 'nvarchar', 'varbinary', 'xml')
and table_name is not null
and (character_maximum_length <> -1
or character_maximum_length is not null)

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-11-06 : 10:25:08

Why do you need to look at data types from the information_schema views? Here is the data type in your create table statement:

[Document] [varbinary](max) NULL,

Here is the error you posted:

Msg 2725, Level 16, State 2, Line 1
Online index operation cannot be performed for index 'PK_Document_DocumentID' because the index contains column 'Document' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. For non-clustered index the column could be an include column of the index, for clustered index it could be any column of the table. In case of drop_existing the column could be part of new or old index. The operation must be performed offline.

It actually tells you that something is wrong with trying to do the online index operation to the column "Document". That is the best case scenario. The error message is basically jumping up and down waiving it's hands saying there's an issue with the column "Document". There's no guess work here. It tells you the exact problem...that it's either "text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml". Why try to fight it instead of just looking at the structure of this particular table and seeing that the Document field is in fact varbinary(max)...the exact problem that the error message told you? It gave you the table and field name both. Why is it so hard to look at that field (Document) and see that it fails to meet the criteria and will have to be done offline?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources