Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Error after disable Index in a table

I would like to do Bulk Insert, but it takes a long time. My Idea is to disable Index on the table, to do INSERTs a little bit faster. But after that I get this error:

Error: The query processor is unable to produce a plan because the index CRMD_MKTTG_TG_I~0 on table or view 'CRMD_MKTTG_TG_I' is disabled.

I am generating my table with this script:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [CRMD_MKTTG_TG_I](
    [CLIENT] [nvarchar](3) NOT NULL,
    [GUID] [varbinary](16) NOT NULL,
    [TG_GUID] [varbinary](16) NULL,
    [BP_GUID] [varbinary](16) NULL,
    [BP_ORG] [nvarchar](2) NOT NULL,
    [BP_DEL] [nvarchar](1) NOT NULL,
    [REL_GUID] [varbinary](16) NULL,
    [RELATIONSHIP] [nvarchar](6) NOT NULL,
    [TYPE_BP_GUID] [nvarchar](1) NOT NULL,
    [TYPE_REL_GUID] [nvarchar](1) NOT NULL,
    [ROW_NUMBER] [int] NOT NULL,
 CONSTRAINT [CRMD_MKTTG_TG_I~0] PRIMARY KEY CLUSTERED 
(
    [CLIENT] ASC,
    [GUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

any idea?

like image 488
Kaja Avatar asked Nov 04 '15 10:11

Kaja


1 Answers

[CRMD_MKTTG_TG_I~0] is a clustered index. If you disable it, you can't access to the data (select, insert, update, delete data).

Read Disable Indexes and Constraints:

  • Disabling an index prevents user access to the index
  • and for clustered indexes to the underlying table data.

In this case, you can only:

  • drop the clustered index and transform it into a heap,
  • query your table
  • and then create a new clustered index.
like image 181
Julien Vavasseur Avatar answered Sep 20 '22 15:09

Julien Vavasseur