4
CREATE TABLE [dbo].[TicketTasks]
(
        [TicketTaskId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
        [TicketTaskTypeId] [char](2) NOT NULL,
        [TicketId] [int] NOT NULL, 
        [CreatedUtc] [datetime] NOT NULL,
        [DeletedUtc] [datetime] NULL,
        [DepartmentId] [int] NOT NULL,
        [TaskAction] [nvarchar](max) NULL,
        [TaskResult] [nvarchar](max) NULL,
        [TaskPrivateNote] [nvarchar](max) NULL,

        CONSTRAINT [PK_TicketTasks] 
           PRIMARY KEY CLUSTERED ([TicketTaskId] ASC) 
)

HI this is my table structure.

  1. TicketTaskId which is clustered
  2. TicketId non clustered index with [CreatedUtc], [DeletedUtc], [DepartmentId]
  3. TaskAction, TaskResult, TaskPrivateNote is Added for full text indexing

I have around 50 million records in this table. Currently I have to drop and recreate non clustered index for TicketID to sort in desc.

Does this dropping of non clustered index affect any other indexes?

Will it remove the full text indexing done for the table?

manu vijay
  • 367
  • 3
  • 13
  • Can you add the DDL for the indexes? Do you have additional UNIQUE constraints? – EzLo Feb 13 '19 at 08:48
  • A fulltext index depends on the keyindex it's specified against. Without knowing the index definitions you've used, it's hard to be definite but since the non-clustered index you're talking about appears to be multi-column and you can't use one of those as the keyindex for Full Text, why would you think one would depend on the other? – Damien_The_Unbeliever Feb 13 '19 at 08:53
  • TaskAction, TaskResult, TaskPrivateNote only full text indexing used ,non clustered index not created for these three columns. For ticketID,DeletedUtc, DepartmentId I have created different non clustered indexes. – manu vijay Feb 13 '19 at 09:21

1 Answers1

0

Common non-clustered indexes are independent of each other, the only problem you might get will be if you tamper with the clustered one (usually accompanied by a PRIMARY KEY), since it physically reorganizes the data and all other indexes contain a pointer to it.

However, full text indexes are attached to an index that enforces uniqueness of values (a UNIQUE index). This is done by the KEY INDEX option when creating the full text index:

CREATE FULLTEXT INDEX 
    ON [dbo].[TicketTasks](TaskAction)   
    KEY INDEX <UniqueIndexName>

So as long as you don't change this referenced unique index, you can drop the other non-clustered indexes.

EzLo
  • 13,780
  • 10
  • 33
  • 38
  • I havent tampered with the clustered one (Primary key). I tried to drop One non clustered index for TicketID and then I recreated it in another sort order (ASC to DESC). After this activity when I checked the full text indexing done was removed. – manu vijay Feb 13 '19 at 09:25