February 26, 2013 at 2:30 pm
Hi
i have recently been running the following against one of my tables within a job and it has worked fine for many a month.
CREATE INDEX IX_tbl__PS_18W_OP_and_IP_PW_Times_clinic ON inf.tbl_PS_18W_OP_and_IP_PW_Times(appClinic)
recently though, i have experenced the following messgae and then the only way can get the job to complete is by commenting this particular index creation out via the old "--" method.
Warning! The maximum key length is 900 bytes. The index 'IX_tbl__PS_18W_OP_and_IP_PW_Times_clinic' has maximum length of 8000 bytes. For some combination of large values, the insert/update operation will fail.
It has serverly reduced performace by excluding this index and i was wonderingif anyone could advise.
Many thanks in advance for advice given
Ta
February 26, 2013 at 3:33 pm
SQL Server maximum key length is 900 bytes.
Your column is varchar max and now you have inserted a value in the column that exceeds 900 bytes.
February 26, 2013 at 3:53 pm
Thanks for your reply,
Being a bit of a novice, how would i go about identifying the value that exceeds the limits.
(if in fact this is the way to resolve the issue??)
I presume that a value as been entered in the (appClinc) field that exceeds the limit???
Sorry for my in-experience, still on the learning curve.
thanks in advance
February 26, 2013 at 4:01 pm
It's just a warning message, unless you actually have a value in that column that exceeds 900 bytes.
You could put that one index create in a separate step, and for that one step, change it to "On Failure: Continue to the next step".
That way, that warning wouldn't prevent the rest of the job from running, but you wouldn't skip any errors other than that single one, which you know is just a warning anyway.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 26, 2013 at 4:10 pm
Many Thanks, I'm learning all the time here. Good crack 🙂
If its just a warning then OK, i will let it run.
May I lastly ask (if not too much inconvience)...Why when running the following, is it only the appClinic that raises the error and not the others???
CREATE INDEX IX_tbl__PS_18W_OP_and_IP_PW_Times_adm_flag ON inf.tbl_PS_18W_OP_and_IP_PW_Times(Adm_Non_Admit_Flag)
CREATE INDEX IX_tbl__PS_18W_OP_and_IP_PW_Times_pathway_hosp ON inf.tbl_PS_18W_OP_and_IP_PW_Times(pathway_hosp)
CREATE INDEX IX_tbl__PS_18W_OP_and_IP_PW_Times_division ON inf.tbl_PS_18W_OP_and_IP_PW_Times(Division)
CREATE INDEX IX_tbl__PS_18W_OP_and_IP_PW_Times_spec ON inf.tbl_PS_18W_OP_and_IP_PW_Times(Spec_Desc)
CREATE INDEX IX_tbl__PS_18W_OP_and_IP_PW_Times_clinic ON inf.tbl_PS_18W_OP_and_IP_PW_Times(appClinic)CREATE INDEX IX_tbl__PS_18W_OP_and_IP_PW_Times_cons ON inf.tbl_PS_18W_OP_and_IP_PW_Times(PAS_ref_consultant)
CREATE INDEX IX_tbl__PS_18W_OP_and_IP_PW_Times_bookedflag ON inf.tbl_PS_18W_OP_and_IP_PW_Times(Booked_Unbooked_flag)
CREATE INDEX IX_tbl__PS_18W_OP_and_IP_PW_Times_weeks ON inf.tbl_PS_18W_OP_and_IP_PW_Times(weeks_wait)
I would rather understand the how's and why's of a question then it saves yoru time and mine if I dont have to ask it again.
Once again, thanks for putting my mind at rest, now i know it isnt a major issue.
Thanks
February 26, 2013 at 4:16 pm
I stand corrected. Thanks Scott.
February 26, 2013 at 4:18 pm
appClinic is the only column(s) that is(are) longer than 900 bytes.
If the total bytes in the index keys are 900 or less, that's not an issue for SQL, so you won't get the warning.
So, if you had:
appClinic varchar(8000)
appColumn1 varchar(899)
you will get an error on the first column's index creation but no on the second one.
SQL's warning you:
"
Hey, if a row you insert/update later puts more than 900 bytes into appClinic, you'll get an error at run time then, and I won't be able to complete that insert/update, because I can't build an index row with that big a key value on it. Don't say I didn't warn you!
"
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 26, 2013 at 4:32 pm
Here's a quick script to demonstrate the warning and then the actual error that occurs:
USE tempdb
CREATE TABLE test_index (
long_column_to_index varchar(8000)
)
-- will cause the Warning msg, since column is potentially longer than 900 bytes
CREATE INDEX test_index__ix_long_column_to_index ON test_index ( long_column_to_index )
INSERT INTO test_index VALUES(REPLICATE('*', 800)) --works OK, less than or equal to 900
INSERT INTO test_index VALUES(REPLICATE('*', 850)) --works OK, less than or equal to 900
INSERT INTO test_index VALUES(REPLICATE('*', 900)) --works OK, less than or equal to 900
-- verifies that all 3 rows were inserted
SELECT record_count, *
FROM sys.dm_db_index_physical_stats ( DB_ID(N'tempdb'), OBJECT_ID(N'test_index'), NULL, NULL, 'DETAILED')
INSERT INTO test_index VALUES(REPLICATE('*', 901)) --FAILS! ERROR, greater than 900 bytes
-- run after the error, to verify that the last row, the one with 901 bytes, was NOT inserted
SELECT record_count, *
FROM sys.dm_db_index_physical_stats ( DB_ID(N'tempdb'), OBJECT_ID(N'test_index'), NULL, NULL, 'DETAILED')
--DROP TABLE test_index
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 26, 2013 at 4:42 pm
Thanks for all your help.
I'd need never be puzzled by this again.
Thanks especially to Ten Cen. you make it sound so simple when you put it like that.(straight talking)
You never know, i may one day be able to pass on my new found knowledge to another SQL fledgling.
Cheers again chaps
February 26, 2013 at 4:43 pm
Ten Cen, what a douch I am.
I mean Scott,
Soz dude. :hehe::hehe:
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply