Friday, February 24, 2012

Error while updating INDEXES

I was updating few indexes in one of my database tables when I got this warning message:
"Warning! The maximum key length is 900 bytes. The index 'IX_cc_statusText' has maximum length of 1024 bytes. For some combination of large values, the insert/update operation will fail."

Can anyone help me out as to:
1. Why this warning was thrown?
2. What are the implications of the same?
3. How to rectify this?

Here is the script I was using to update the indexes:

CREATE INDEX [cc_programEnrollment14] ON [dbo].[cc_programEnrollment]([cc_company_uid]) ON [PRIMARY]
GO

CREATE INDEX [IX_cc_program_uid] ON [dbo].[cc_programEnrollment]([cc_program_uid]) ON [PRIMARY]
GO

CREATE INDEX [IX_cc_status_uid] ON [dbo].[cc_programEnrollment]([cc_status_uid]) ON [PRIMARY]
GO

CREATE INDEX [IX_cc_CompanyID] ON [dbo].[cc_programEnrollment]([cc_CompanyID]) ON [PRIMARY]
GO

CREATE INDEX [IX_cc_statusText] ON [dbo].[cc_programEnrollment]([cc_statusText]) ON [PRIMARY]
GO

CREATE INDEX [IX_cc_initiatedDate] ON [dbo].[cc_programEnrollment]([cc_initiatedDate]) ON [PRIMARY]
GO

CREATE INDEX [IX_cc_initiatedBy_uid] ON [dbo].[cc_programEnrollment]([cc_initiatedBy_uid]) ON [PRIMARY]
GO

CREATE INDEX [IX_cc_SponsorID] ON [dbo].[cc_programEnrollment]([cc_SponsorID]) ON [PRIMARY]
GO

CREATE INDEX [IX_cc_SponsorDistributor_uid] ON [dbo].[cc_programEnrollment]([cc_SponsorDistributor_uid]) ON [PRIMARY]
GO

CREATE INDEX [IX_cc_programEnrollment] ON [dbo].[cc_programEnrollment]([cc_isDealerEligible], [cc_uid]) ON [PRIMARY]
GO

Thanks in advance
DexterHey Dexy,

you're index creation is failing because your column cc_statusText is longer than 900 bytes. You should try and create your indexes on the smaller columns, as I'm quite positive larger columns will take a bit longer to look up in the index.

Have a look here...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_0ldf.asp

Regards,
-Kilka

No comments:

Post a Comment