This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(313.6, 4%, 40%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EGG%3C/text%3E%3C/svg%3E)
got the message
Warning! The maximum key length is 900 bytes. The index 'UQ__Transact__396D246F48ECC9B6' has maximum length of 1024 bytes. For some combination of large values, the insert/update operation will fail.
On trying to create a table:
>
CREATE TABLE "dbO"."Transaction_doc" (
"tx_doc_Id" bigint NOT NULL UNIQUE,--DEFAULT AUTOINCREMENT,
"subAcct_ID" INTEGER NULL,
"acct_id" INTEGER NULL,
"Count" SMALLINT NOT NULL DEFAULT 1,
"processed" SMALLINT NOT NULL DEFAULT 0,
"fileSpec" VARCHAR(1024) NOT NULL UNIQUE,
"Updated" TIMESTAMP NULL,
"Updated_By" VARCHAR(30) NULL,
CONSTRAINT "PK_Transaction_Doc_tx_doc_id" PRIMARY KEY ( "tx_doc_Id" ASC )
)
what have I done to trigger the message?
PS I thought bigint is only 8 bytes and Sqlanywhere does not have trouble with this.)
ooops, i found the error in the unique constraint on fileSpec being 1K,
oh well sql server is nice but seems sql anywhere is more capable.
or this is only a limitation of localdb of sql server 2014 and problem goes away with real server or 2019?
PS how to edit/delete my question?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(236.8, 76%, 32%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EEM%3C/text%3E%3C/svg%3E)
Click the upper right button.
or this is only a limitation of localdb of sql server 2014 and problem goes away with real server or 2019?
No, it's a general limitation in all SQL Server editions.
oops, I grooved, the message was complaining on the unique fileSpec of 1k length.
is this the max indexkey length of sql server 2016 local db?
what about 2019
As Tom said, SQL Server 2016 and Azure SQL Database have increased the maximum size for index keys with nonclustered indexes. The new maximum key size for nonclustered indexes is 1700 bytes. The maximum key size for clustered indexes remains 900 bytes.
Please refer:
https://learn.microsoft.com/en-us/archive/blogs/sqlserverstorageengine/increased-nonclustered-index-key-size-with-sql-server-2016
Regards
Echo
Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thank you for understanding!
The index 'UQ_Transact_396D246F48ECC9B6' has maximum length of 1024 bytes
And how is that index defined? Does it may have the column "fileSpec" = 1.024 bytes length?
no idea where UQ_Transact_396D246F48ECC9B6 came from. I did not explicitly define that.
the actual definition I got after the server did its bits,
CREATE TABLE [dbo].[Transaction_doc] (
[tx_doc_Id] BIGINT NOT NULL,
[subAcct_ID] INT NULL,
[acct_id] INT NULL,
[Count] SMALLINT DEFAULT ((1)) NOT NULL,
[processed] SMALLINT DEFAULT ((0)) NOT NULL,
[fileSpec] VARCHAR (1024) NOT NULL,
[Updated] DATETIME DEFAULT (getdate()) NULL,
[Updated_By] VARCHAR (30) NULL,
[VERSION] ROWVERSION NOT NULL,
CONSTRAINT [PK_Transaction_Doc_tx_doc_id] PRIMARY KEY CLUSTERED ([tx_doc_Id] ASC),
UNIQUE NONCLUSTERED ([fileSpec] ASC),
UNIQUE NONCLUSTERED ([tx_doc_Id] ASC)
);
after I dropped the table and update with Updated changed to datetime, adding column [VERSION] ROWVERSION NULL,
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(102.4, 47%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETC%3C/text%3E%3C/svg%3E)
Whenever you define a column as UNIQUE, you will get a UNIQUE INDEX on that column. SQL Server will give that index a name with a long list of hex digits to insure the name is unique. So UQ_Transact_396D246F48ECC986 is an index created because you declared filespec to be unique. And since you are on an older release (SQL 2014), you get the error. If you move to a newer release (SQL 2016 or later), then you will not get this error.
Tom
Please refer to:
I also found a similar problem. Someone gave a solution to index large columns:
Warning! The maximum key length is 900 bytes. The index has maximum length of 1000 bytes
If you have any question, please feel free to let me know.
Regards
Echo
If the answer is helpful, please click "Accept Answer" and upvote it.
Before SQL 16 the maximum combined length of the columns in an index was 900 bytes. With SQL 2016, that is still the maximum length for clustered indexes, but the maximum length for nonclustered indexes is 1700 bytes. Since the unique index you defined on filespec would be a nonclustered index, you should be fine with SQL 2016 or later.
Also, you don't want to make tx_doc_id the primary key and also declare it to be unique. Doing that will create two indexes on tx_doc_id (one clustered and one nonclustered). So you want to remove the UNIQUE attribute from tx_doc_id. Since it is the only key in your primary key constraint, it is already forced to be unique.
Tom