Post reply

index creation issue/question ??

  • February 26, 2013 at 2:30 pm

    #274555

    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

    #1591330

    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

    #1591333

    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

    #1591335

    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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • February 26, 2013 at 4:10 pm

    #1591336

    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

    #1591337

    I stand corrected. Thanks Scott.

  • February 26, 2013 at 4:18 pm

    #1591338

    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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • February 26, 2013 at 4:32 pm

    #1591340

    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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • February 26, 2013 at 4:42 pm

    #1591343

    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

    #1591344

    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