December 30, 2009 at 8:23 am
I'm trying to create an index on a temp table but I get a warning message. The index creation syntax is: CreateIndex ix_AssociateUserName on #tmpTrainingTasks (AssociateUserName)
Warning message is :
Warning! The maximum key length is 900 bytes. The index 'ix_AssociateUserName' has maximum length of 2000 bytes. For some combination of large values, the insert/update operation will fail.
I ran a query to check the length of field AssociateUserName but it returns 15 as the longest value. I'm not sure how to get rid of this warning.
Any ideas outthere?
thx,
John
December 30, 2009 at 8:30 am
It means the column you are creating the index on is defined to take 2000 bytes of data. The warning won't go away unless you redefine the column. The warning doesn't matter if the data will never exceed 900, but in that case, it's usually best to explicitly say so in the table schema.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 30, 2009 at 11:29 am
I explicitly set the length of the field.
Thx,
December 30, 2009 at 11:42 am
One trick I found if I want to improve query performance where I need to query a character field larger than 900 bytes is to have a column that holds the CHECKSUM or CHECKSUM_BINARY of that character field.
CHECKSUM produces a 4 byte integer hash of the field and so can an index can get you within the ball park of the records you want.
You where clause would look something like.
WHERE checksumfield = CHECKSUM(@Inputparameter)
AND original field = @InputParameter.
January 4, 2010 at 7:05 am
David.Poole (12/30/2009)
One trick I found if I want to improve query performance where I need to query a character field larger than 900 bytes is to have a column that holds the CHECKSUM or CHECKSUM_BINARY of that character field.CHECKSUM produces a 4 byte integer hash of the field and so can an index can get you within the ball park of the records you want.
You where clause would look something like.
WHERE checksumfield = CHECKSUM(@Inputparameter)
AND original field = @InputParameter.
I've used that same trick. Can be very effective.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply