Home Index error on executing DDL
Reply: 0

Index error on executing DDL

Manish K
1#
Manish K Published in 2018-01-12 14:36:35Z

When I run the DDL mentioned below in sql server, I'm getting an error as:

"Warning! The maximum key length for a clustered index is 900 bytes. The index 'TBL_MyTable' has maximum length of 1278 bytes. For some combination of large values, the insert/update operation will fail."

Just wondering how do I resolve this error?

Thanks.

BEGIN

DECLARE @constraint_name nvarchar(256)
DECLARE @table_name nvarchar(256)
DECLARE @col_name nvarchar(256)


SET @table_name = N'TBL_MyTable'
SET @col_name = N'myDate'

SELECT @constraint_name = CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE 
    where TABLE_NAME = @table_name AND COLUMN_NAME = @col_name  

    IF @constraint_name IS NOT NULL
        BEGIN
            EXEC('ALTER TABLE [dbo].['+ @table_name +'] DROP CONSTRAINT ' + @constraint_name);          
        END 
            EXEC('ALTER TABLE [dbo].['+ @table_name +'] ALTER COLUMN myDate DATE NOT NULL');            

            EXEC('ALTER TABLE [dbo].['+ @table_name +'] ADD CONSTRAINT 
            [PK_TBL_MyTable] 
            Primary Key 
                 (  column1,
                    myDate,
                    column2,
                    column3,
                    column4,
                    column5,
                    column6)')

END
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.295807 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO