question

GGunnfs-5814 avatar image
0 Votes"
GGunnfs-5814 asked EchoLiu-msft commented

trouble with using bigint as primary key in creating table

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.)

sql-server-transact-sql
· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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?

0 Votes 0 ·

![134185-image.png
Click the upper right button.

0 Votes 0 ·
image.png (113.9 KiB)

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.



0 Votes 0 ·

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

0 Votes 0 ·

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://docs.microsoft.com/en-us/archive/blogs/sqlserverstorageengine/increased-nonclustered-index-key-size-with-sql-server-2016

Regards
Echo

0 Votes 0 ·

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!

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered TomCooper-6989 commented

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?

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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,




0 Votes 0 ·

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

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Please refer to:

https://www.ibm.com/support/pages/maximum-key-length-900-bytes-index-eidunq-has-maximum-length-2000-bytes

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.