Toad World® Forums

FW: Toad-SS 5.6 - Alter doesn't allow the altering of an existing Index on a table -- Support Question

This is a question not a bug or NFR !

In the below active table [GCMA].[APP].[BillGroupSchedules] , there is a column
that needs to be deleted ,[CutOffID] .

I can see how to drop the column, and Foreign key in the Table Alter, but I
cannot see where I can adjust the index
idx_BillGroupSchedules_incBGID_Rdate_COdata_CoID .

However, I do see that I can seperately alter the index under the index tab..
Therefore, I have concluded that this was/is a design standard for Toad-SS,
where the altering of Indexes is outside the scope of the Table Alter process.
It should be noted that SSMS treats index management differently as well and not
very well either.

Please advise if this set in stone, for I would see advantages to making the
functionality (altering indexes) part of Table Alter.

Granted this may be an item for the idea pond, but prior to that I want to hear
from support and development on the afforementioned.

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn.com

678.414.0090 my cell Primary

Hank.Freeman50 (Skype)

/****** Object: Table [APP].[BillGroupSchedules] Script Date: 3/8/2012 10:03:46
AM ******/

USE [GCMA] ;

GO

IF EXISTS

( SELECT

FROM

sys.objects

WHERE

object_id = OBJECT_ID (N '[APP].[BillGroupSchedules]' ) AND

type IN (N 'U' ))

BEGIN

DROP TABLE [GCMA].[APP].[BillGroupSchedules] ;

END

GO

SET ANSI_NULLS ON ;

GO

SET QUOTED_IDENTIFIER ON ;

GO

IF NOT EXISTS

( SELECT

FROM

sys.objects

WHERE

object_id = OBJECT_ID (N '[APP].[BillGroupSchedules]' ) AND

type IN (N 'U' ))

BEGIN

CREATE TABLE [APP].[BillGroupSchedules](

[BillGroupScheduleID] INT IDENTITY ( 1 , 1 ) NOT NULL

,[BillYear] INT NOT NULL

,[BillMonth] INT NOT NULL

,[BillGroupID] INT NOT NULL

,[BillDate] DATETIME NOT NULL

,[CompanyID] INT NOT NULL

,[ReadingDate] DATETIME NOT NULL

,[CutOffDate] DATETIME NULL

,[CutOffID] INT NOT NULL

,[CreateUserID] INT NOT NULL

,[CreateDate] DATETIME NOT NULL DEFAULT ( getdate ())

,[LastUpdateUserID] INT NOT NULL

,[LastUpdateDate] DATETIME NOT NULL

,[DeleteFlag] BIT NOT NULL DEFAULT (( 0 ))

, CONSTRAINT [FK__BillGroup__BillG__1B4B60F5] FOREIGN KEY

([BillGroupID])

REFERENCES [APP].LuBillGroups

, CONSTRAINT [FK__BillGroup__CutOf__1C3F852E] FOREIGN KEY

([CutOffID])

REFERENCES [APP].LuBillGroups

, CONSTRAINT [PK_BillGroupSchedules_BillGroupScheduleID] PRIMARY KEY

CLUSTERED

([BillGroupScheduleID] ASC )

WITH (PAD_INDEX = OFF

, FILLFACTOR = 90

,IGNORE_DUP_KEY = OFF

,STATISTICS_NORECOMPUTE = OFF

,ALLOW_ROW_LOCKS = ON

,ALLOW_PAGE_LOCKS = ON

,DATA_COMPRESSION = NONE)

ON [PRIMARY]

, CONSTRAINT [CK__BillGroup__Delet__72A872BE] CHECK

(([DeleteFlag] = ( 0 ) OR

[DeleteFlag] = ( 1 ))))

ON [PRIMARY] ;

END ;

GO

/****** Object: Index
[APP].[BillGroupSchedules].[idx_BillGroupSchedules_BillGroupID] Script Date:
3/8/2012 10:03:46 AM ******/

IF EXISTS

( SELECT

FROM

sys.indexes

WHERE

object_id = OBJECT_ID (N '[APP].[BillGroupSchedules]' ) AND

name = N 'idx_BillGroupSchedules_BillGroupID' )

BEGIN

DROP INDEX [idx_BillGroupSchedules_BillGroupID] ON
[GCMA].[APP].[BillGroupSchedules] ;

END

GO

IF NOT EXISTS

( SELECT

FROM

sys.indexes

WHERE

object_id = OBJECT_ID (N '[APP].[BillGroupSchedules]' ) AND

name = N 'idx_BillGroupSchedules_BillGroupID' )

BEGIN

CREATE NONCLUSTERED INDEX [idx_BillGroupSchedules_BillGroupID]

ON [APP].BillGroupSchedules

WITH (

PAD_INDEX = OFF

, FILLFACTOR = 90

,IGNORE_DUP_KEY = OFF

,STATISTICS_NORECOMPUTE = OFF

,ONLINE = OFF

,ALLOW_ROW_LOCKS = ON

,ALLOW_PAGE_LOCKS = ON

,DATA_COMPRESSION = NONE)

ON [PRIMARY] ;

END

GO

/****** Object: Index
[APP].[BillGroupSchedules].[idx_BillGroupSchedules_incBGID_Rdate_COdata_CoID]
Script Date: 3/8/2012 10:03:46 AM ******/

IF EXISTS

( SELECT

FROM

sys.indexes

WHERE

object_id = OBJECT_ID (N '[APP].[BillGroupSchedules]' ) AND

name = N 'idx_BillGroupSchedules_incBGID_Rdate_COdata_CoID' )

BEGIN

DROP INDEX [idx_BillGroupSchedules_incBGID_Rdate_COdata_CoID] ON
[GCMA].[APP].[BillGroupSchedules] ;

END

GO

IF NOT EXISTS

( SELECT

FROM

sys.indexes

WHERE

object_id = OBJECT_ID (N '[APP].[BillGroupSchedules]' ) AND

name = N 'idx_BillGroupSchedules_incBGID_Rdate_COdata_CoID' )

BEGIN

CREATE NONCLUSTERED INDEX [ idx_BillGroupSchedules_incBGID_Rdate_COdata_CoID ]

ON [APP].BillGroupSchedules

INCLUDE

(

[BillGroupID], [ReadingDate], [CutOffDate], [CutOffID] )

WITH (

PAD_INDEX = OFF

, FILLFACTOR = 100

,IGNORE_DUP_KEY = OFF

,STATISTICS_NORECOMPUTE = OFF

,ONLINE = OFF

,ALLOW_ROW_LOCKS = ON

,ALLOW_PAGE_LOCKS = ON

,DATA_COMPRESSION = NONE)

ON [PRIMARY] ;

END

GO

image002.jpeg

image001.gif


image003.jpeg

Hello, Hank!
In other words, you want to change the index with Alter Table DOE, as far as possible to constraints? If so, this improvement has not yet been scheduled, because it takes very time-consuming to implement. I checked when I remove a column related to the index, the index is recreated without the removed column, see picture.

Maria.

Maria,

I respectfully request that you have an index and FKey related to the column as
I have submitted, then see if works for you, for it doesn’t work for me…

I have submitted a bug to Gita for same.

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn.com

678.414.0090 my cell Primary

Hank.Freeman50 (Skype)
image001.gif

Maria,

I respectfully request that you have an index and FKey related to the column as
I have submitted, then see if works for you, for it doesn’t work for me…

I have submitted a bug to Gita for same.

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn.com

678.414.0090 my cell Primary

Hank.Freeman50 (Skype)
image002.jpeg